Get list of sheets and latest sheet in google spreadsheet api v4 in Python

Question

I am trying to read and write values of different sheets in python 3 following the google official documentation. Though I am able to read values from certain sheets using range property in rangeName = 'Class Data!A2:E' in the code block mentioned below:

discoveryUrl = ('https://sheets.googleapis.com/$discovery/rest?'
                    'version=v4')
    service = discovery.build('sheets', 'v4', http=http,
                              discoveryServiceUrl=discoveryUrl)

    spreadsheetId = '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms'
    rangeName = 'Class Data!A2:E'
    result = service.spreadsheets().values().get(
        spreadsheetId=spreadsheetId, range=rangeName).execute()
    values = result.get('values', [])

And I am trying to write values using the sample code from here:

requests.append({
    'updateCells': {
        'start': {'sheetId': 0, 'rowIndex': 0, 'columnIndex': 0},
        'rows': [
            {
                'values': [
                    {
                        'userEnteredValue': {'numberValue': 1},
                        'userEnteredFormat': {'backgroundColor': {'red': 1}}
                    }, {
                        'userEnteredValue': {'numberValue': 2},
                        'userEnteredFormat': {'backgroundColor': {'blue': 1}}
                    }, {
                        'userEnteredValue': {'numberValue': 3},
                        'userEnteredFormat': {'backgroundColor': {'green': 1}}
                    }
                ]
            }
        ],
        'fields': 'userEnteredValue,userEnteredFormat.backgroundColor'
    }
})
batchUpdateRequest = {'requests': requests}

service.spreadsheets().batchUpdate(spreadsheetId=spreadsheet_id,
                                    body=batchUpdateRequest).execute()

The problem I am facing is that I am not able to retain latest sheet name or id from official documentation and as latest api revision is making random gid(we may not know what would be the sheet gid would be). Is there any way to refer list of sheets or spreadsheet latest revised sheet name or id using google sheet api v4?

Answer 1

You can get a list of sheets by using the "get" method on spreadsheets:

sheet_metadata = service.spreadsheets().get(spreadsheetId=spreadsheet_id).execute()
sheets = sheet_metadata.get('sheets', '')
title = sheets[0].get("properties", {}).get("title", "Sheet1")
sheet_id = sheets[0].get("properties", {}).get("sheetId", 0)