3

The Python API for Google sheets has a get method to get values from a spreadsheet, but it requires a range argument. I.e., your code must be something like this,

sheets_service = service.spreadsheets().values()
data = sheets_service.get(spreadsheetId = _GS_ID, range = _SHEET_NAME).execute()

and you cannot omit the range argument, nor will a value of '' work, or a value of 'Sheet1' or similar (unless there is a sheet named Sheet1).

What if I do not know the sheet name ahead of time? Can I reference the first or left-most sheet somehow? Failing that, is there a way to get a list of all the sheets? I have been looking at the API and have not found anything for that purpose, but this seems like such a basic need that I feel I'm missing something obvious.

mhucka
  • 2,143
  • 26
  • 41
  • Although I'm not sure whether this is what you want, for example, in order to know about the information, at first, how about retrieving values and metadata of the Spreadsheet, that you want, using the spreadsheets.get method like ``service.spreadsheets().get(spreadsheetId=_GS_ID).execute()``? The document is [here](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/get). In this case, it is required to know the Spreadsheet ID. – Tanaike Aug 15 '18 at 00:34
  • @Tanaike That will work! It works and gives a list of the sheets. And I understand my difficulty: I did not notice there is a separate `get` method on `spreadsheets()` too. That method returns different information. Thanks for your reply. BTW, if you'd like to write this up as an answer instead of a comment, I'd be happy to upvote & check it off as the answer. – mhucka Aug 15 '18 at 00:49
  • 2
    If you don't specify a name it defaults to the first sheet but you do have to specify the columns, e.g. `range="A:Z"` would bring back columns `A-Z` from the first sheet. Reference: https://developers.google.com/sheets/api/guides/concepts#a1_notation – AChampion Aug 15 '18 at 01:14
  • Thank you for replying. I posted an answer including sample scripts. Could you please confirm it? – Tanaike Aug 15 '18 at 02:48

1 Answers1

3

You can retrieve the values and metadata of Spreadsheet using spreadsheets.get of Sheets API. By using the parameter of fields, you can retrieve various information of the Spreadsheet.

Sample 1 :

This sample retrieves the index, sheet ID and sheet name in Spreadsheet. In this case, index: 0 means the first sheet.

service.spreadsheets().get(spreadsheetId=_GS_ID, fields='sheets(properties(index,sheetId,title))').execute()

Sample 2 :

This sample retrieves the sheet name, the number of last row and last column of data range using sheet index. When 0 is used for the sheet index, it means the first sheet.

res = service.spreadsheets().get(spreadsheetId=_GS_ID, fields='sheets(data/rowData/values/userEnteredValue,properties(index,sheetId,title))').execute()
sheetIndex = 0
sheetName = res['sheets'][sheetIndex]['properties']['title']
lastRow = len(res['sheets'][sheetIndex]['data'][0]['rowData'])
lastColumn = max([len(e['values']) for e in res['sheets'][sheetIndex]['data'][0]['rowData'] if e])

Reference :

Tanaike
  • 181,128
  • 11
  • 97
  • 165