Google Sheet Python API: how get values in first sheet if don't know range?

3
votes

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.

1
pythongoogle-sheetsgoogle-sheets-api
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. In this case, it is required to know the Spreadsheet ID. – Tanaike
@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
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: developers.google.com/sheets/api/guides/concepts#a1_notation – AChampion
Thank you for replying. I posted an answer including sample scripts. Could you please confirm it? – Tanaike

1 Answers

3
votes

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 :