How to use Google Sheets as a JSON Endpoint

By Clark Jason Ngo

UPDATE: 5/13/2020 - New Share Dialog Box steps available below.

Thanks Erica H!

Are you building a prototype dynamic web application and need to collaborate with non-developers?

I have been to a hackathon before and experienced participating with the knowledge on how to develop an application but lacked the skill or time to implement a full-stack web application in the 3-day sprint. That time may skill was way too low to even help out and was sidelined to watch tutorials and study HTML and CSS.

The result? I have learned a lot but I wished I could've contributed more.

In one of the hackathons I have participated in recently, I encountered a similar problem. This time around I was not the newbie. I had non-engineers who wanted to help in building our prototype web application. Luckily, we stumbled upon a Google Spreadsheets as a way for our non-engineers to mock up our database and have the back-end developers connect to Google Sheets JSON Endpoint and parse it.

With this guide, you'll be able to:

1. Create a spreadsheet in Google Spreadsheets.
2. Publish the spreadsheet to the web.
3. Generate a JSON endpoint.
4. Open the spreadsheet for public collaboration.
5. Pass the JSON endpoint to your back-end developer.

After this tutorial, you'll be able to join teams and say, "I can help with the back-end!".

Section 1: Creating a Google Sheet

Step 1:

Go to Google Sheets

Step 2:

Create a new spreadsheet

Section 2: Publishing your Google Sheets to the web

Note: New Share Dialog Box update as of 5/13/2020, located after Step 2.

Step 1:

Click File > Publish to the web…

Step 2:

Click Publish, then OK

Step 3:

No need to do anything here

UPDATE: 5/13/2020 - New Share Dialog Box

Step 1:
Click Share

Step 2:

Click "Change to anyone with the link"

Step 3:

Click "Done"

Section 4: Using your Google Sheets as JSON endpoint

Step 1:

Copy the template URL and paste in the address bar:

https://spreadsheets.google.com/feeds/cells/YOURGOOGLESHEETCODE/SHEETPAGENUMBER/public/full?alt=json

Step 2:

Go to your opened Google Sheets and check the address bar

Google Sheets url

Google Sheets code

Step 3:

Go to the template URL and replace

• YOURGOOGLESHEETCODE with _1ifbWzueslEP5-_ysP6gg7oNaHQmqF8LlXBfStCwFMs
• SHEETPAGENUMBER to 1

Step 4:

Retrieve JSON URL

https://spreadsheets.google.com/feeds/cells/1ifbWzueslEP5-_ysP6gg7o_NaHQmqF8LlXBfStCwFMs/1/public/full?alt=json

Result of JSON url

Section 5: Making your Google Sheets public (for collaboration and data entry)

Step 1:

On the top right, click Share

Step 2:

Add a name, click Save

Step 3:

Click Advanced

Step 4:

Click Change…

Step 5:

Click On — Public on the web, then Save

Common Gotchas:

If you receive the response below, please check your URL and make sure you use the Google Sheets code in the address bar.

If you receive the response below, please go back to Section 2: Publishing your Google Sheets to the web.

Clark Jason Ngo - Graduate Teaching Assistant - Technology Institute - City University of Seattle |…
_Join LinkedIn * Passionate to nurture new software developers. Technical Skills: Git, MVC, JavaScript, NodeJS, ReactJS…_www.linkedin.com