27

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?

roka
  • 1,677
  • 1
  • 15
  • 21

4 Answers4

59

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.

LeonardDrs
  • 625
  • 7
  • 8
  • 6
    I had been using the worksheet ID specified by the `gid=1816064743` docs.google.com URL parameter when editing the spreadsheet, but 1816064743 stopped working recently. I replaced it with the worksheet position number (1 for the first/default worksheet, 2 for the second worksheet). This fixed my application. :) – Chris Peterson Jul 03 '14 at 08:02
  • You can also use as a parameter an individual worksheet ID within the collection, which targets a specific sheet. – lincolnberryiii Aug 18 '14 at 01:51
  • 4
    As of this morning, 'od6' seems to have stopped working for newly created Google Sheets docs. Using '1' instead works, and works with old docs as far as I've been able to test. – Joe Germuska Jan 12 '15 at 18:25
  • ob6 works: https://spreadsheets.google.com/feeds/list/187iKB7ekhP96evCySKBWy5LfWErrJDono-8glzFPcCY/od6/public/basic?alt=json – Fergus Oct 13 '17 at 03:31
16

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.

enter image description here

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:

enter image description here

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

ThisClark
  • 14,352
  • 10
  • 69
  • 100
  • Just to add to this... I found that if you publish the entire document the request seems to take longer than if you publish on one sheet, and request only that one sheet. In my case it reduced the request from 10,000ms to 200ms, a massive difference. – Jason Allshorn May 30 '18 at 06:10
0

You can find the new ID at the following

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

storm is-in
  • 19
  • 2
0

Google turn down v3 API google sheets thus any of above url would not work now

use

Change Sheet1 == your sheet name Change {sheet_id} = your spreadsheet id Change {YOUR_API_KEY} = Go to " https://console.cloud.google.com/apis/library/sheets.googleapis.com " and enable it get than go "Credentials " tab right side click on " SHOW KEY " take this key and paste

https://sheets.googleapis.com/v4/spreadsheets/{sheet_id}/values/Sheet1!A1:D5?key={YOUR_API_KEY}

rpmathur 12
  • 11
  • 3