Retrieve Google Spreadsheet Worksheet JSON

Question

I try to receive the JSON of a Google Spreadsheet Worksheet. It worked till some days ago. For the default worksheet it still works, but not for all other worksheets.

This is the working URL for the default worksheet: https://spreadsheets.google.com/feeds/list/1caRqAA1TyBoZ0eVZvvKheEBh9SGRmQII4qih9urY70k/od6/public/full?alt=json

And this is the URL for the worksheet that stopped working: https://spreadsheets.google.com/feeds/list/1caRqAA1TyBoZ0eVZvvKheEBh9SGRmQII4qih9urY70k/1416241220/public/full?alt=json

The error message is Invalid query parameter value for grid_id.

Only difference is the worksheet parameter (od6 vs 1416241220).

Any ideas on why that error suddenly occurs?

Answer 1

ChrisPeterson's note:

You can use worksheet position number (1 for the first/default worksheet, 2 for the second worksheet).

Original answer

I came across the same issue and I managed to find my way out. It seems that they recently changed the id for each worksheet.

You can find the new ID at the following

https://spreadsheets.google.com/feeds/worksheets/YOUR_SPREADSHEET_ID/private/full

I got something like o3laxt8 between <id> tags

Ps: od6 anddefault values will always work and redirect to the first worksheet of your document.

Joe Germuska' note:

od6 doesn't work anymore

Seems to work again.

Answer 2

I'd like to share a concrete example because I find there are enough confusing instructions out there including the accepted answer and worksheet IDs and where to put them not being obvious.

Here's a document I published and anyone with the link can view:
https://docs.google.com/spreadsheets/d/1QDWpycJJFA-UAiSPIv-icJ4UZhbEmuN8wxxag83SE1c/edit?usp=sharing

The document has to be published correctly. There are two Publish buttons and the first one doesn't work for this task. Use the second.

The document KEY is important. Obtain the KEY from between the /d/ and the /edit in the url. In my example, the key is 1QDWpycJJFA-UAiSPIv-icJ4UZhbEmuN8wxxag83SE1c.

Second, use the following URL style, replacing KEY with your own:

https://spreadsheets.google.com/feeds/list/KEY/od6/public/values?alt=json 

My example url links directly to published json:
https://spreadsheets.google.com/feeds/list/1QDWpycJJFA-UAiSPIv-icJ4UZhbEmuN8wxxag83SE1c/od6/public/values?alt=json

Finally, if the worksheet has multiple sheets (or tabs), replace od6 in the url with a number. My example has two tabs, so there are two urls corresponding to either tab. I simply replace od6 with 1 and 2 depending on the order of the sheets:

Tab 1:
https://spreadsheets.google.com/feeds/list/1QDWpycJJFA-UAiSPIv-icJ4UZhbEmuN8wxxag83SE1c/1/public/values?alt=json

Tab 2:
https://spreadsheets.google.com/feeds/list/1QDWpycJJFA-UAiSPIv-icJ4UZhbEmuN8wxxag83SE1c/2/public/values?alt=json

---

In the event of a worksheet where the tabs are reordered frequently, it is possible to get the ID of a given sheet and use that instead of ordered numbers. I first learned of this approach from this post or this post:

In brief, you would reform a private URL with your KEY:

https://spreadsheets.google.com/feeds/worksheets/KEY/private/full 

This only works on a browser where you are logged into Google Drive on an account with permissions.

Next, you have to sift through XML to find your sheet IDs:

Replace the previous 1 and 2 with the IDs, for example:

Tab 1 (first worksheet id in a new google sheet is always od6 by default, no matter order of tabs): https://spreadsheets.google.com/feeds/list/1QDWpycJJFA-UAiSPIv-icJ4UZhbEmuN8wxxag83SE1c/od6/public/values?alt=json

Tab 2:
https://spreadsheets.google.com/feeds/list/1QDWpycJJFA-UAiSPIv-icJ4UZhbEmuN8wxxag83SE1c/ope57yg/public/values?alt=json