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!

I have been a massive fan of the entire Google Doc suite, but I only found out recently that there are publics APIs for querying data from any published Google Sheet documents. Sounds amazing right? If I need an API to return some data, I can just create a Google Sheet document then populate it with whatever data I need. Compared to my previous method, which involved storing data as a JavaScript object in the application, using Google Sheets means data and source code can be decoupled. Another thing to point out is using Google APIs, all the request balancing and performance are passed to Google there is no additional work on my end.

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.

How to request data from Google Sheet document

1. Publish the document
On the Google Sheet document, click on File then 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. I personally recommend JSON, which is not on the list. Don't worry this is completely fine, just leave the data type as Web page.

3. Return data as JSON
Use the following API to request data back as JSON, just replace DOCUMENT_ID and SHEET_ID respectively with your own.

https://spreadsheets.google.com/feeds/cells/DOCUMENT_ID/SHEET_ID/public/basic?alt=json

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.

https://spreadsheets.google.com/feeds/worksheets/DOCUMENT_ID/private/full

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.