Looking up Google Sheets values using sheet id instead of sheet title (Google Drive Rest API V4)

Question

Using the Google Sheets REST API V4, and would prefer to use the sheet id instead of sheet title to look up values in a cell. It seems like you can specify the sheet within the spreadsheet using the range, e.g. Sheet4!A1:Z500, but not a sheet ID such as 1310487470!A1:Z500. This is so the query doesn't have to change if someone renames the sheet. Does the Google API support querying by ID?

Current query:

https://sheets.googleapis.com/v4/spreadsheets/1SR0DJ4nV5-05EWxjz1OYOWkKFObClmsC0rOowPnMwNE/values/Sheet4!A1:Z500

Ideal query:

https://sheets.googleapis.com/v4/spreadsheets/1SR0DJ4nV5-05EWxjz1OYOWkKFObClmsC0rOowPnMwNE/values/1310487470!A1:Z500

I have never found how to use GID to A1 notation yet. How about this way? For example, it retrieves both sheetId(GID) and its title using sheets.spreadsheets.get, and creates A1 notation using the title for the GID. The url query is https://sheets.googleapis.com/v4/spreadsheets/sheetID?fields=sheets(properties(sheetId%2Ctitle)). The scope of https://www.googleapis.com/auth/spreadsheets.readonly can be used for both APIs. If this will not be helpful for you, I'm sorry. — Tanaike
I just confirmed with GSuite support that there's no supported way. They can see the need for querying by ID and recommended I file a feature request. In the meantime, your workaround seems plausible, thanks @Tanaike! — yayitswei
If you need the sample script, feel free to tell me. I can prepare it. — Tanaike
I already implemented it- but if you have time to post your script perhaps it will help someone else here. — yayitswei

Tanaike Tanaike · Accepted Answer · 2017-05-07T22:53:17

How about following sample? I prepared it as a GAS sample. If you use this, please enable Sheet API v4 at API console.

This retrieves data of a sheet in spreadsheet using GID.

Retrieves information of sheets in spreadsheet. A JSON data which has keys of GID and values of sheet name is created.
Retrieves data from sheet using the JSON.

Sample script:

function fetch(url){
  return UrlFetchApp.fetch(url, {
    headers: {
      'Authorization': 'Bearer ' +  ScriptApp.getOAuthToken()
    }
  });
}

function main(){
  // Retrieves information of sheets in spreadsheet. 
  var spreadsheet = "#####";
  var url = "https://sheets.googleapis.com/v4/spreadsheets/" + spreadsheet + "?fields=sheets(properties(sheetId%2Ctitle))";
  var sheetdic = {};
  JSON.parse(fetch(url)).sheets.forEach(function(e){
    sheetdic[e.properties.sheetId] = e.properties.title;
  });

  // Retrieves data from sheet using GID.
  var sheet = sheetdic["#####"]; // Imports sheet name using GID.
  var url = "https://sheets.googleapis.com/v4/spreadsheets/" + spreadsheet + "/values/" + sheet + "!A1:Z500";
  var results = fetch(url);
}

Looking up Google Sheets values using sheet id instead of sheet title (Google Drive Rest API V4)

1 Answers