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 URL: https://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 ]