Did you know that you can use Google Sheets as a JSON API?

You don’t necessarily need to write server-side code for everything.  Sometime there is a much easier solution.  Did you know that you can use Google Sheets as an API endpoint?  Let’s take a look at a quick example.

For a few years now, I have been archiving my tweets as Google Sheets documents.  There is a maximum size to a single document, so there are more than one.  Today, we will be using the eighth one in the series as an example.

Step 1:  Click the “Share” button

Step 2:  Change the permissions to “Anyone on the internet with this link can view”

Step 3: Click “Publish to the web” under “File”

Step 4: Publish the document to the web

Step 5: Get the sharing URL

Step 6: Craft the API endpoint URL using the document ID and sheet number from step 5

Once you get this far, you can build the API endpoint URL using the document ID and sheet number (for a one sheet document, the value is “1”).

Sharing URLhttps://docs.google.com/spreadsheets/d/1r4hO_Jum3YpilAXNZM7TaCSnYz9Og6ViqB_Ip-4w0_g/edit?usp=sharing

API endpoint URL: https://spreadsheets.google.com/feeds/cells/1r4hO_Jum3YpilAXNZM7TaCSnYz9Og6ViqB_Ip-4w0_g/1/public/full?alt=json

Step 7: Use the API endpoint like you normally would

If you set this up correctly, you should be able to read from it like any JSON API out there.

Below is a quick look at the raw data.

See the Pen
Google Sheets API Example – Part 1
by Joe Steinbring (@steinbring)
on CodePen.

If we take a closer look, you’ll see that we still need to treat it like tabular data.

See the Pen
Google Sheets API Example – Part 2
by Joe Steinbring (@steinbring)
on CodePen.

We can always loop over the data and transform it, though.

See the Pen
Google Sheets API Example – Part 4
by Joe Steinbring (@steinbring)
on CodePen.

In this final example, we loop over the array in {{this.data.entry}} and create {{this.rejiggeredData}}.  This new array of objects should be a little easier to work with.

Have any questions, comments, etc?  Feel free to drop a comment, below.

 

[ Cover photo by Rajeshwar Bachu on Unsplash ]

Leave a Reply

Your email address will not be published. Required fields are marked *