Python – Google Sheets Python API: how get values in first sheet if don’t know range?

Python API for Google sheets There is a get method to get values from a spreadsheet, but it takes a range parameter. That is, your code must look like this

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

And you can’t omit the range parameter, the value of ” or the value of ‘Sheet1' will not work or similar (unless there is a worksheet named Sheet1).

What if I don’t know the worksheet name beforehand? Can I somehow reference the first sheet or the leftmost sheet? If this is not possible, is there a way to get a list of all worksheets? I’ve been looking at the API and haven’t found anything for that purpose, but this seems like a basic need and I feel like I’m missing something obvious.

You can use spreadsheets.get of the Sheets API to retrieve the values and metadata of a spreadsheet. Through the fields parameter, you can get various information about the spreadsheet.

Example 1:

This example retrieves the index, worksheet ID, and worksheet name in a spreadsheet. In this case, index: 0 represents the first worksheet.

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

Example 2:

This example uses the worksheet index to retrieve the worksheet name, the last row of the data range, and the number of the last column. When the worksheet index is 0, it indicates the first worksheet.

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])

Quote:

• spreadsheets.get
• Convert column index into corresponding column letter
  • For column, you can see in the thread above about the method to convert index to letter.