python - 使用 Python (API v4) 从 Google 工作表读取单元格格式

Question

我正在寻找一种从 Python 中的 Google 工作表中读取单元格格式的方法，特别是其背景颜色。

我发现能够读取表格的两个流行软件包是 gspread (fork)和 pygsheets 。我尝试了两者，它们在读取我的工作表数据方面效果很好，但是从我看来，两者都不支持读取单元格格式，只支持设置它们。这个open issue pygsheets 的 GitHub 页面上描述了我需要的功能类型。

本质上，每行都是一个包含时间戳、用户名、注释等的记录，我想查找特定用户名的所有行和仅查找那些没有红色背景的行，有点像这样:

if ("Username" in record.values()) and (matching_cells.background != red):
        # Do something

谢谢!

Answer 1

使用 google-api-python-client 获取授权的 sheets API 客户端，您可以使用 service.spreadsheets().get 请求电子表格数据。方法:

def get_sheet_colors(service, wbId: str, ranges: list):
    params = {'spreadsheetId': wbId,
              'ranges': ranges,
              'fields': 'sheets(data(rowData(values(effectiveFormat/backgroundColor,formattedValue)),startColumn,startRow),properties(sheetId,title))'}
    return service.spreadsheets().get(**params).execute()

desiredA1NotationRanges = ['\'Some Arbitrary Sheet 1\'!A1:K', '\'Some Other Arbitary Sheet\'!B2:D4']
all_data = get_sheet_colors(get_authed_service_somehow(), mySpreadsheetId, desiredA1NotationRanges))
# all_data is a dict with keys determined by the fields in the request
# (i.e. "sheets") and the output of the API method used (aka consult your API reference)

下面的代码使用上面的 API 响应并创建两个数组，一个具有背景颜色，一个具有单元格值，并通过为行和列添加前缀来确保行和列索引可移植，以确保数据从单元格 A1 开始，即使您请求的范围如“C3:J5”。这是作为将 REST 资源转换为更熟悉的类型的示例而提供的，并不具有一般意义。

dataset = []
default_bg = {'red': 1, 'green': 1, 'blue': 1}
# all_data['sheets'] is a list of sheet resources (per the API spec.)
for sheet in all_data['sheets']:
    # The sheet resource is a dict with keys determined by what we requested in fields
    # (i.e. properties (->sheetId, ->title), data)
    print('Sheet name is {title} with grid id {sheetId}'.format_map(sheet["properties"]))
    # each range in data will only contain startRow and/or startColumn if they are not 0
    # (i.e. if you grab A1:___, you won't have startRow or startColumn)
    for range in sheet['data']:
        rowData = range.get('rowData', [])
        if not rowData:
            continue
        offsets = {'row': range.get('startRow', 0),
                   'col': range.get('startColumn', 0)}
        rangeBGs = [default_bg] * offsets['row']
        rangeValues = [''] * offsets['row']
        for row in rowData:
            colData = row['values']
            newBGs = [default_bg] * offsets['col']
            newVals = [''] * offsets['col']
            for col in colData:
                try:
                    newBGs.append(col['effectiveFormat']['backgroundColor'])
                except KeyError:
                    newBGs.append(default_bg) # Shouldn't get called (all cells have a background)
                try:
                    newVals.append(col['formattedValue']) # Always a string if present.
                except KeyError:
                    newVals.append('') # Not all cells have a value.
            rangeBGs.append(newBGs)
            rangeValues.append(newVals)
        dataset.append({'sheetId': sheet['properties']['sheetId'],
                        'sheetName': sheet['properties']['title'],
                        'backgrounds': rangeBGs,
                        'values': rangeValues})
# dataset is now a list with elements that correspond to the requested ranges,
# and contain 0-base row and column indexed arrays of the backgrounds and values.
# One could add logic to pop elements from the ranges if the entire row has no values.
# Color in A1 of 1st range:
r1 = dataset[0]
print(f'Cell A1 color is {r1["backgrounds"][0][0]} and has value {r1["values"][0][0]}')
print(f'Cell D2 color is {r1["backgrounds"][3][1]} and has value {r1["values"][3][1]}')

引用文献:

• Python Sheets API documentation
• Google API Python Client
• Google's Python API Explorer
• The spreadsheet JSON resource
• "Fields" Reference将此语法与响应资源/实体的 API 描述结合使用。 (Google 在其 API Explorer 文档中也有一个交互式字段生成器。)