How to get a Google Sheet as JSON

October 14, 2021

Google Sheets can be a great place to store content for a website, since it's structured and easy to update (especially for non-coders).

Google used to have an obscure way of getting an API for reading a Google Sheet, but it stopped working in August 2021 when they deprecated the Sheets API v3.

However, I’ve built a free API that you can use for this, which doesn’t require authentication or complicated permissions.

opensheet is an open source project I built for fetching a Google Sheet as live JSON data.

Preparing Your Google Sheet

  1. The first row of your spreadsheet should be headers, and the rest is data under those headers (see example).
  2. Share the spreadsheet so anyone can see it (“Share” button in top right corner > “Anyone on the internet with this link can view”).

Using the API

The URL format for using the API is:

https://opensheet.vercel.app/spreadsheet_id/sheet_name

Replace spreadsheet_id with the ID in the URL of the Google Sheet. Here’s the part you should copy and paste:

A URL bar with the spreadsheet ID highlighted in yellow

Next, replace sheet_name with the name of the sheet (basically “tab”) that you to want to get the values from. You can find and rename the sheets at the bottom of Google Sheets:

The sheet tabs at the bottom of Google Sheets

And that’s it! You can click here to see an example API response.

The data gets returned as an array of objects, where the key is the name of the column and the value is a cell’s value.

Example using JavaScript

This example uses fetch, a modern built-in way to make HTTP requests with JavaScript.

fetch('https://opensheet.vercel.app/1o5t26He2DzTweYeleXOGiDjlU4Jkx896f95VUHVgS8U/Sheet+One')
  .then(res => res.json())
  .then(data => {
    data.forEach(row => {
      // Do something with each row here.
    })
  })

Wrap-up

You can check out the code for opensheet here, but you’re welcome to use my hosted instance at opensheet.vercel.app.

Responses are cached for 30 seconds to improve performance, so it might take up to 30 seconds for changes to show up.

If you have any questions about how to use this little utility API, please reach out! I’d love to help.

A quick favor: was anything I wrote incorrect or misspelled, or do you still have questions? Please use this form to let me know or ask for help!