在Python中获取Google电子表格api v4中的工作表列表和最新工作表 [英] Get list of sheets and latest sheet in google spreadsheet api v4 in Python

问题描述

我正在按照 google官方文档.尽管我可以使用下面提到的代码块中的rangeName = 'Class Data!A2:E'中的range属性从某些工作表中读取值:

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

我面临的问题是我无法从官方文档中保留最新的工作表名称或ID，并且由于最新的api版本正在使随机gid(我们可能不知道工作表的gid是什么).有什么方法可以使用Google Sheet API v4来引用工作表列表或电子表格最新修订的工作表名称或ID?

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?

推荐答案

您可以使用电子表格上的获取"方法来获取工作表列表:

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)

这篇关于在Python中获取Google电子表格api v4中的工作表列表和最新工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！