Google Sheets as a Service
The most difficult choice to make when creating a simple web application is not choosing the right framework or colour scheme (for any backend devs out there). Instead, the struggle comes from deciding if a backend is required or necessary. A lot of the time, it is hard to justify setting up a backend service for a simple web application. More times than not, the only thing I really need is one or two simple APIs to bounce some data around. For that, I don’t really want to spin up a new EC2 instance on AWS, create a backend, setting up a database and etc.
There are plenty of services out there which already do this kind of stuff I know, AWS has Lambda for serverless services. Even with Lambda functions, I still need a database to store any data. The closest service I have found is called Orchestrate. It essentially lets me run a database then using different API calls I can query different data from it. The downside of both of these solutions is that they are paid services, sure it might not cost a whole lot but no doubt it is a bad way to implement open source projects. With things tied to my bank account, it then can’t be shared publicly and the open source community won’t be able to contribute to it. So this is where Google Sheets comes into play!
Note: For the purpose of this blog, I’m going to use the non-authenticated API which required the document to be publicly viewable in order to request data from it (bypass authentication). So if the project you are working on has sensitive data, I would recommend that you use the authenticated implementation as suggested here by google.
1. Publish the document
On the Google Sheet document, click on
Publish to the web.... Once the popup opens, just click on the blue
Publish button. Here is my example Google Sheet document.
2. Find out the document ID and worksheet ID
Once published, copy the shareable link from the same popup. Now open it in a new tab, you can see it is an HTML version of the same document. There are a bunch of formats you can get it in (see image below). I personally recommend JSON, which is not on the list. Don’t worry this is completely fine, just leave the data type as
3. Return data as JSON
Use the following API to request data back as JSON, just replace
SHEET_ID respectively with your own.
DOCUMENT_ID is the key of the spreadsheet you want to retrieve, it can be found in the URL. In my case, it is docs.google.com/spreadsheets/d/==1MYzZjtsujLXH43F_iQl242OV045UahkENWkv0nF83Qo==/edit#gid=327263911.
SHEET_ID is the positional or unique identifier of the worksheet, the default ID is
od6 for default sheet name
Sheet1. If you don’t know what your
SHEET_ID is, then it is worth using the following API to dig through and find it.
The returned data is a massive JSON object, unfortunately, I had to write my own logic to extract the relevant data from it. Depending on how the sheet is laid out the code implementation to extract it will obviously differ. For this reason, possibly it is best to only consider Google Sheet as short term or temporary backend service.
4. Additional APIs to insert, update, delete data from the document
For anything else more advanced, please refer to the official Google API documentation.