How to convert Google Sheets data to JSON … in just 3 steps

I was working on updating my personal website to show various projects I’ve built over the years and needed a simple way to manage this one section. I knew I could use plain HTML but when I realized I’ve launched 50+ projects, that seemed like it would be a nightmare to maintain.

My next thought was setting up a MySQL database and updating the data via phpMyAdmin. I started building the database but realized this was a huge overkill especially since this data would rarely change.

Luckily for me, fellow maker Pat Walls was live-coding a similar idea on Twitch. Pat was looking to develop a way to power a blog using Google Docs instead of WordPress and that made me think “if Pat can do it for blogging, can I do something similar using Google Sheets?”

After an hour or so of researching and hacking away, the anwer was a definite YES.

Below is a basic rundown on how I managed to get a Google Sheets to JSON solution in place to power part of my website without having to use an sort of CMS.

First Step: Setup your Google Sheet

Start a new spreadsheet like you normally do in Google Sheets

Head over to sheets.google.com and start a new Google Sheet.

While in your new spreadsheet you’ll need to do two quick things that will make your spreadsheet PUBLIC. I know this sounds scary but to be able to convert your spreadsheet’s data to JSON it needs to publicly available.

First make the spreadsheet PUBLIC by clicking on the SHARE button and then selecting “On — Public on the Web”

Then you just need to click on the FILE menubar option and select “Publish to the web.”

Second Step: Find the Spreadsheet’s ID

In this example the Spreadsheet ID is 1K5s_56IsN5icMTehyiOxDE7fCStGHbSh8Jq_AzaPcP0

This one is pretty straightforward. Look at the URL in your browser’s address bar and find the string of characters between /d/ and /edit#. That is your Spreadsheet ID.

Final Step: Find the Worksheet ID

Using the Spreadsheet ID from above, update the code below, replacing “spreadsheetID” with your Spreadsheet ID.

https://spreadsheets.google.com/feeds/worksheets/spreadsheetID/public/basic?alt=json

Paste the above updated URL with your Spreadsheet ID into a new browser tab. You should then see something siimilar to this:

In this example the Worksheet ID is od6

Since this demo spreadsheet only has one worksheet, you just need to locate the single worksheet idea. 99.99999% of the time when you are using a single worksheet the ID will be od6.

Now all you need to do is take the Spreadsheet ID and Worksheet ID and update the code below.

https://spreadsheets.google.com/feeds/list/spreadsheetID/worksheetID/public/values?alt=json

Paste the updated URL in a new browser tab and you should now see the JSON version of your Google Spreadsheet.

JSON version of the Google Spreadsheet

So … how did I do?

Let me know in the comments below if this tutorial was helpful. As I mentioned at the beginning, I’m using a Google Sheet to JSON data management system to power part of my personal website.