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.

Senseful · Accepted Answer · 2017-06-11T03:05:31.737

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

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

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:

Create a new app on Google Cloud Platform.
Create a new API key.
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.

This should be the accepted answer now, the other approaches don't appear to work with the V4 API. In case it wasn't clear for someone else, "RANGE" in this case can be simply the name of the tab within the spreadsheet. — atomic77, Feb 04 '18 at 21:58
Just to clarify, this approach does not require the "publish to web" option, instead, the actual sharing settings must make the spreadsheet public (or link only, etc), and the SPREADSHEET_ID is the one in the url of the spreadsheet itself, not the id in the web version — chrismarx, Jul 24 '18 at 18:44
Please update the answer with the above comment from @chrismarx. — Aftab Khan, Feb 09 '21 at 20:20
This is a great overview for retrieving JSON from the v4 API. One question: regarding "RANGE" in the URL. I know you can use "Sheet1" if the tab/sheet has that default name. But what if the user has changed that tab/sheet to another name. Is there a way to make use of the old "gid=0" that represents the first tab/sheet in the "RANGE" part of the url? I'm looking for some short-cut for "RANGE" that just says "grab all the data from the first tab/sheet" (i.e. gid=0). Anyone know how to do this using the v4 API json url format? — l_r, Aug 13 '21 at 10:29
What does "publicly accessible" mean? How about setting the permission to "restricted" and "anyone in GSuite"? Will that work? — newguy, Sep 29 '22 at 02:14

score 22 · Answer 2 · edited May 23 '17 at 12:17

I have finally (kind of) solved my problem. Just for future reference, and in case somebody else runs into the same troubles, here the solution I came up with:

To make the worksheet publicly accessible, one needs to make the worksheet publicly accessible. This is done in the Google Sheets web interface, using the menu entries File > Publish to the web ... > link > publish. It is possible to either publish the whole spreadsheet or individual worksheets.
An API to access data from Google Sheets programmatically is described on the Google Sheets API web pages. This API uses URLS of the form https://spreadsheets.google.com/feeds/.../key/worksheetId/.... Slightly oddly, the meaning of key and worksheetId seems not to be explained in the API documentation.

My experiments show that the key value can be found by taking part of the URLs used to access the sheet via the web interface (see also here). The key is everything after the /d/, until the next slash. For the spreadsheet in the question, the key is thus 1mhv1lpzufTruZhzrY-ms0ciDVKYiFJLWCMpi4OOuqvI. The worksheetId seems to be an integer, giving the position of the worksheet in the spreadsheet. For the example in the question one has to know that the sheet shown is the second worksheet, the worksheetId in this case is 2.

The API defined public and private requests. To access an exported resource without authentication, public requests must be used.
The API calls to get data from the spreadsheet are explained in the section "Retrieving a list-based feed" (click on the "Protocol" tab in the examples). The URL required extract the data from the spreadsheet in the question is

https://spreadsheets.google.com/feeds/list/1mhv1lpzufTruZhzrY-ms0ciDVKYiFJLWCMpi4OOuqvI/2/public/full

A HTTP GET request to this URL returns that data as XML. (I have not found a way to get the data as JSON.)
The usual protections agains cross-site requests make it difficult to access the data via JavaScript XML RPC calls in a web app. One way around this problem is to proxy the API calls through the web server (e.g. using nginx's proxy_pass directive).

The above steps are at least a partial solution to the problem in the question. The only difficulty is that the data is returned as XML rather than as JSON. Since the API documentation does not mention JSON, maybe it is not possible any more to extract the data in this format?

You can get the data as JSON by appending ?alt=json to the end of the Url https://spreadsheets.google.com/feeds/list/1mhv1lpzufTruZhzrY-ms0ciDVKYiFJLWCMpi4OOuqvI/2/public/full?alt=json — cygnim, May 07 '15 at 16:43
@SonicDynamite Interesting! Is this documented somewhere? Are there other magic things I can append to the URL to make interesting things happen? — jochen, May 07 '15 at 20:48
Do you know if it's possible to get the whole document (multiple sheets)with the request? I seem to only be able to get one sheet at a time. I could put all the data in just one page, but getting all the pages inside that json would be really useful. — fmtoffolo, May 29 '16 at 04:37
This doesn't seem to work anymore. I had to form my URL like this: https://spreadsheets.google.com/feeds/cells/spreadsheetId/sheetId/public/full?alt=json. See more here: https://developers.google.com/sheets/api/guides/migration — tom, Dec 30 '16 at 15:45
"To make the worksheet publicly accessible, one needs to make the worksheet publicly accessible." - Who'd have thunk it? ;) — Sean, Apr 30 '18 at 09:38
@Sean yes, that's a silly typo :) Not sure what (if anything) I was thinking ... — jochen, Apr 30 '18 at 16:14

bomberjackets · Answer 3 · 2021-08-19T21:36:59.677

Edit: (Aug 17, 2021) With the rollout of Sheets v4, the endpoint in the original answer has been deprecated. The updated endpoint and sample script included below:

Updated solution

Credits to the original answer here.

"https://docs.google.com/spreadsheets/d/" + spreadsheetId + "/gviz/tq?tqx=out:json&gid=0";

You don't technically have to include the gid if you just want the first sheet, but you can specify another sheet if you'd like using that parameter.

Here's a sample script to retrieve values of Spreadsheet as JSON, and then parsed as header row and values.

var sf = "https://docs.google.com/spreadsheets/d/1l7VfPOI3TYtPuBZlZ-JMMiZW1OK6rzIBt8RFd6KmwbA/gviz/tq?tqx=out:json";
$.ajax({url: sf, type: 'GET', dataType: 'text'})
.done(function(data) {
  const r = data.match(/google\.visualization\.Query\.setResponse\(([\s\S\w]+)\)/);
  if (r && r.length == 2) {
    const obj = JSON.parse(r[1]);
    const table = obj.table;
    const header = table.cols.map(({label}) => label);
    const rows = table.rows.map(({c}) => c.map(({v}) => v));

    console.log(header);
    console.log(rows);
  }
})
.fail((e) => console.log(e.status));

Original solution

Note: This no longer works as Sheets v3 was deprecated in August 2021.

Here's how to get the JSON using those same URL parameters:

"https://spreadsheets.google.com/feeds/list/" + spreadsheetID + "/od6/public/values?alt=json";

Creds to @jochen on the answer with the path all the way up to XML "https://spreadsheets.google.com/feeds/list/" + spreadsheetID + "/od6/public/" + sheetID;

As @jochen's answer explains, this sheetID is based on the order of the sheets in the spreadsheet.

Note: replace `od6` by the sheet: `default` or `1` (first), `2` (second), etc. — mems, May 14 '18 at 09:27
It seems this approach has been/is being deprecated, as I'm getting intermittent 404 errors trying to access sheets this way. ( https://cloud.google.com/blog/products/g-suite/migrate-your-apps-use-latest-sheets-api ) — l_r, Aug 13 '21 at 10:37
@l_r is right: the endpoint from the original answer has been deprecated along with Sheets v3 as of August 2021, answer has been updated with the new method for Sheets v4 — still no API key necessary, just need to use the "Publish to Web" option instead :) — bomberjackets, Aug 17 '21 at 22:47
@bomberjackets just to clarify, "publish to web" option is no longer needed. The sheet just needs to be Shared with "Anyone with the link" can view sharing option. — l_r, Aug 18 '21 at 13:48

score 1 · Answer 4 · answered Apr 12 '17 at 09:32

A faster solution here is to use this https://gist.github.com/ronaldsmartin/47f5239ab1834c47088e to wrap around your existing spreadsheet.

You first need to change your sheet access to Anyone with link can View

Add the id and sheet html param to the URL below.

https://script.google.com/macros/s/AKfycbzGvKKUIaqsMuCj7-A2YRhR-f7GZjl4kSxSN1YyLkS01_CfiyE/exec

Eg: your id is your sheet id which is

1mhv1lpzufTruZhzrY-ms0ciDVKYiFJLWCMpi4OOuqvI

and your sheet which is

Sheet2

In your case you can actually see your data here as json at

https://script.google.com/macros/s/AKfycbzGvKKUIaqsMuCj7-A2YRhR-f7GZjl4kSxSN1YyLkS01_CfiyE/exec?id=1mhv1lpzufTruZhzrY-ms0ciDVKYiFJLWCMpi4OOuqvI&sheet=Sheet2

To be safe, you should deploy the code sheetAsJson.gs in the github gist above as your own in your Google Drive.

The OP of the question has turned off his/her sheet from the public, but you can use like this link https://script.google.com/macros/s/AKfycbzGvKKUIaqsMuCj7-A2YRhR-f7GZjl4kSxSN1YyLkS01_CfiyE/exec?id=0AgviZ9NWh5fvdDdNMlI2aXRCR2lCX1B1alZ6ZjZxSkE&sheet=Summary&header=2&startRow=3&First_Name=Greatest&Last_Name=Ever from the gist that I cited in the beginning of my answer. If you read the gist cited from my answer you should be able to understand how it works and use the author's script to wrap around your sheet. — owyongsk, Apr 23 '18 at 08:05

score 1 · Answer 5 · answered Aug 25 '20 at 12:32

1

Here is the solution

Note your sheet id in the document url (don't use the published url to find the id!)
Publish your sheet, just as html page
Use the id from step 1,
and put it in this url https://spreadsheets.google.com/feeds/cells/{id}/1/public/full?alt=json

The /1 indicates the first sheet in your document

answered Aug 25 '20 at 12:32

bartburkhardt

7,498
1
18
14

I am using this "?alt=json" technique for showing my data to my website. Is this technique good for the long term?. – Manish S Mar 08 '21 at 11:45
That's more question for Google I think – bartburkhardt Mar 08 '21 at 15:22

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

5 Answers5

Common error messages:

Updated solution

Original solution

Linked