How to use sheet ID in Google Sheets API?
Sheet name is the easiest way to access a specific sheet. As written here, range
parameter can include sheet names like,
Sheet1!A1
If you must use a sheet id instead of sheet name, You can use any of the alternate end points which uses dataFilter
, like spreadsheets.values.batchUpdateByDataFilter
instead of spreadsheets.values.batchUpdate
. You can then use sheetId in request body at data.dataFilter.gridRange.sheetId
. An example of using such a filter with sheetId is provided by another answer here by ztrat4dkyle.
However, developer metadata is the preferred method of permanently associating objects(sheets/ranges/columns) to variables, where user modifications are expected on such objects.
Daniel
I write blogs in English https://preciselab.io and Polish https://gustawdaniel.com
Updated on June 05, 2022Comments
-
Daniel almost 2 years
Google Sheets document can contain some sheets. First is default and '0'. Generally for any sheet there is address like this:
https://docs.google.com/spreadsheets/d/(spreadsheetId)/edit#gid=(sheetId)
with both
spreadsheetId
andsheetId
.But in API documentation there is no mention of how to use
sheetId
. I can only read and edit default sheet for givenspreadsheetId
.If in
request
from code presented in exemplary link I addedsheetId
property I got error:{ message: 'Invalid JSON payload received. Unknown name "sheetId": Cannot bind query parameter. Field \'sheetId\' could not be found in request message.', domain: 'global', reason: 'badRequest' }
How to get access to other sheets than default in Google Sheets API and read or update fields in them?
-
Călin Darie almost 5 yearsdownvoted because the question is about sheet id. By renaming a sheet, you can unknowingly break the name-based range.
-
Rahul over 4 yearsirrelevent answer.
-
TheMaster over 4 years@Călin There's no relevant documentation stating that sheetId is constant. Furthermore, The title isn't the only question: As the question describes and elaborates: How to get access to other sheets than default in Google Sheets API and read or update fields in them? So even if sheetId is constant, what's wrong with code based on sheetName? Perhaps, I want to make the code to always refer to "Master" sheet. I might make a copy of another sheet and name it "Master" for the code to refer to that sheet. There's no mention of any need for immutability of the sheet in the question.
-
TheMaster over 4 years@Rahul How so? Even though I thought Calin's downvote is unjustified, I did edit my answer to show other possibilities. What's your justification for the downvote?
-
TheMaster over 4 yearsIf any of you have additional requirements, ask a new question instead of downvoting my answer. The answer is perfect for the question. I can't improve the answer any more for a hypothetical problem by suggesting more unnecessary boilerplate(even though I did edit my suggestions into the answer because of the question "title").
-
Arundev about 4 yearsSheet id may not constant but it can be find from url easily. Google docs give regex to get that also. this is not the accepted answer.
-
jeffci almost 4 yearsGreat answer! Instead of using ID. I like the idea of names instead of ID, works with ID as well. I voted it up