Hello 👋

I'm Alejandro

A Web Engineer from Málaga, Spain based in London, UK

Using Google Spreadsheets as an API

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.

TablesAreCool
col 3 isright-aligned$1600
col 2 iscentered$12
zebra stripesare 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!

Share this post

Read more about