Using Google Spreadsheets as an API
March 26, 2020
Sometimes getting a small side project up and running is difficult. Building your own database, backend and frontend takes a lot of time.
I'm going to show you how you can make this journey easier and faster with Google Spreadsheets and its built in API, so you don't have to care about databases or backends at all.
Step by step
1. Sign in to your Google drive account
Go to https://drive.google.com/ and sign in with your account.
2. Create a new Spreadsheet
On the right hand corner of your screen there should be a "New" button, click on that, and select "Google Sheets".
A new Spreadsheet should open automatically.
3. Enter some test data
Create some columns and give them some test values, something like this.
Tables | Are | Cool |
---|---|---|
col 3 is | right-aligned | $1600 |
col 2 is | centered | $12 |
zebra stripes | are neat | $1 |
4. Publish to the web
Click on "File" > "Publish to the web".
A popup should appear prompting you to Publish. You should select what you would like to, but for this purpose we are going to publish the "Entire document" as a "Web page".
ā ļø This will make your data accessible to the public.
5. Accessing the API
Take the spreadsheet id, which is on the URL.
https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxx/edit#gid=0
Right in the middle of /spreadsheets/d/
and /edit#gid=0.
Now, Take this URL
https://spreadsheets.google.com/feeds/worksheets/[ID]/public/basic?alt=json
and replace [ID]
with your spreadsheet id.
Hit that URL from your browser, and you will see a big JSON object, where there is a feed
> entry
> link
property, which is an array.
Pick the second item, the one that says "cellsfeed" on it.
And click though the link of the href
property.
Once you are on it, add ?alt=json
at the end of the url to parse the content to JSON.
And there it is!
Every single cell of your spreadsheet should be exposed as an API for you to have the freedom and the time to build new projects.
Also the data is automatically updated once you edit.
So... have fun coding!