How can I get only filled rows with spreadsheets.get, given a sheet ID but not a sheet title?

user_10213561 0
Viewed 1293

Google Sheets API v4 has two methods for getting spreadsheet data:

  • spreadsheets.get, i.e. GET https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}
  • spreadsheets.get.values, i.e. GET https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/{range}

The range path param of the latter lets you specify a sheet title (without any cell range in A1 notation), which automagically returns only filled rows of that sheet. The former has a range query param which does the same; however...

I don't want to use the title of a sheet, since titles are mutable. I want to use only the ID of a sheet, since IDs are immutable.

With spreadsheets.get, I can indeed find the sheet I want, using only ID, in the retrieved object's sheets array. However, by not specifying a range query param, I've retrieved every single row of the spreadsheet.

So, how can I get all filled rows and no empty rows of a sheet in a spreadsheet, given only the spreadsheet ID and sheet ID?

google-sheets-api
1 Answers

By following the example from Google Sheets API, you can use the spreadsheetid to indicate where you would provide the spreadsheet ID which can be discovered from the spreadsheet URL. You can read a single range by following the spreadsheet.values.get request that reads the value stored in the range. For example:

GET https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId/values/Sheet1!A1:D5

Note: Empty trailing rows and columns are omitted.

user_101053210
Related