Accessing a (new-style, public) Google sheet as JSON

Question

How can I access the contents of a (new-style) Google sheet a JSON? My aim is to access the values from JavaScript, so I need to be able to download the JSON via HTTP.

Example: how can I download the data from this sheet as JSON?

I tried to find the answer via a web search, but ultimately failed:

• Many tutorials on the web start with the instruction to find the key=... value in the url. The URL I got when I exported the sheet is https://docs.google.com/spreadsheets/d/1mhv1lpzufTruZhzrY-ms0ciDVKYiFJLWCMpi4OOuqvI/pubhtml?gid=1822753188&single=true and has no key=... in it.

• The answer to "Accessing A Public Google Sheet" seems to indicate that I should try https://docs.google.com/spreadsheets/d/1mhv1lpzufTruZhzrY-ms0ciDVKYiFJLWCMpi4OOuqvI/export?format=csv&id=1mhv1lpzufTruZhzrY-ms0ciDVKYiFJLWCMpi4OOuqvI&gid=1822753188 to get a CSV version, but this does not work for me: I get a sign-in page instead of the data.

• I found approaches using Google Apps Scripts, but these seem to require some user action in the browser instead of giving a download link.

Answer 1

If you want to use the latest API (v4), you'll need to do the following:

1. Generate a spreadsheets API key (see instructions below).
2. Make your sheet publicly accessible.

3. Use a request of the form:

   https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID/values/RANGE?key=API_KEY 

You'll then get a clean JSON response back:

{   "range": "Sheet1!A1:D5",   "majorDimension": "ROWS",   "values": [     ["Item", "Cost", "Stocked", "Ship Date"],     ["Wheel", "$20.50", "4", "3/1/2016"],     ["Door", "$15", "2", "3/15/2016"],     ["Engine", "$100", "1", "30/20/2016"],     ["Totals", "$135.5", "7", "3/20/2016"]   ], } 

Note that if you want to specify the entire contents of a page, an identifier such as Sheet1 is perfectly valid.

See Basic Reading for more information.

---

As of v4 API, all requests must be accompanied by an identifier (e.g. API key):

Requests to the Google Sheets API for public data must be accompanied by an identifier, which can be an API key or an access token.

Follow the steps in the linked document to create an API key on the credentials page.

Make sure to:

1. Create a new app on Google Cloud Platform.
2. Create a new API key.
3. Add the Google Sheets API. (API Manager > Dashboard > Enable API)

---

Note that you can still access public data without forcing the user to log in:

In the new Sheets API v4, there is no explicit declaration of visibility. API calls are made using spreadsheet IDs. If the application does not have permission to access specified spreadsheet, an error is returned. Otherwise the call proceeds.

Note that you do not need to publish the sheet to the web. All you need to do is make sure anyone with the link can access the sheet.

(I.e. when you click Create credentials on the Google Sheets API, choose Other non-UI, User data, and it says "User data cannot be accessed from a platform without a UI because it requires user interaction for sign-in." you can safely ignore that message. The API Key is all you really need, since this is public data.)

---

Common error messages:

The request is missing a valid API key.

You didn't include the key= param in your call.

API key not valid. Please pass a valid API key. Google developers console

You supplied an incorrect API key. Make sure that you typed in your key correctly. If you don't have a key yet, go to the Google developers console and create one.

API Key not found. Please pass a valid API key.
Google developer console API key

Your API Key is probably correct, but you most likely didn't add the Google Sheets permission. Go to the Google developer console API key page and add the sheets permission.

The caller does not have permission

Your sheet isn't set to be publicly accessible.