使用Python和Google Sheets API获取Google表格仅可见非隐藏数据

现有测试代码的表头错误修复 #

你使用的Google Visualization API（gviz/tq）端点默认会自动识别表头行数，当首行后存在隐藏行时，自动识别逻辑会错误将首行和首个可见数据行合并为表头。你之前添加的header=0是pandas的本地解析参数，无法解决接口返回的CSV本身表头错误的问题，只需在请求URL中新增tq_header=1参数指定表头仅为第1行即可修复。

该方案天然只会返回可见行列数据，不需要额外过滤隐藏内容，修复后代码如下：

import io
import requests
import pandas as pd
from googleapiclient.discovery import build
from oauth2client.service_account import ServiceAccountCredentials

def getSpreadsheetData(spreadsheet_id, sheet_id="0"):
    creds_file_path = ""        # 替换为你的服务账号密钥文件路径
    SCOPES = ['https://www.googleapis.com/auth/spreadsheets', 'https://www.googleapis.com/auth/drive']

    creds = ServiceAccountCredentials.from_json_keyfile_name(creds_file_path, SCOPES)
    access_token = creds.get_access_token().access_token
    # 新增tq_header=1参数，指定表头仅为第1行
    url = f'https://docs.google.com/spreadsheets/d/{spreadsheet_id}/gviz/tq?tqx=out:csv&tq_header=1&gid={sheet_id}'
    res = requests.get(url, headers={'Authorization': f'Bearer {access_token}'})
    df = pd.read_csv(io.StringIO(res.text))
    return df

更稳定的原生Google Sheets API实现方案（无需依赖CSV解析） #

如果不想依赖HTTP CSV导出接口，可以用原生API直接获取行列隐藏状态，过滤后生成DataFrame，实现逻辑如下：

1. 拉取工作表元数据，获取所有隐藏行、隐藏列的索引
2. 拉取全量数据后过滤掉隐藏的行列，再转换为DataFrame

完整代码：

import pandas as pd
from googleapiclient.discovery import build
from oauth2client.service_account import ServiceAccountCredentials

def getSpreadsheetData(spreadsheet_id, sheet_name):
    creds_file_path = "" # 替换为你的服务账号密钥文件路径
    SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
    creds = ServiceAccountCredentials.from_json_keyfile_name(creds_file_path, SCOPES)
    service = build('sheets', 'v4', credentials=creds)

    # 拉取工作表元数据，包含行列隐藏状态和单元格内容
    sheet_info = service.spreadsheets().get(
        spreadsheetId=spreadsheet_id,
        ranges=[sheet_name],
        includeGridData=True,
        fields="sheets.data.rowMetadata.hidden,sheets.data.columnMetadata.hidden,sheets.data.rowData.values.formattedValue"
    ).execute()
    grid_data = sheet_info['sheets'][0]['data'][0]

    # 筛选可见行、可见列的索引
    visible_row_idx = [i for i, row_meta in enumerate(grid_data.get('rowMetadata', [])) if not row_meta.get('hidden', False)]
    visible_col_idx = [i for i, col_meta in enumerate(grid_data.get('columnMetadata', [])) if not col_meta.get('hidden', False)]

    # 提取全量单元格值
    all_rows = []
    for row in grid_data.get('rowData', []):
        row_values = [cell.get('formattedValue', '') for cell in row.get('values', [])]
        all_rows.append(row_values)
    
    # 过滤可见行列，首行作为表头生成DataFrame
    visible_rows = [all_rows[i] for i in visible_row_idx]
    visible_data = [[row[j] for j in visible_col_idx] for row in visible_rows]
    df = pd.DataFrame(visible_data[1:], columns=visible_data[0])
    return df

如果你的工作表不需要将首行设为表头，把最后生成df的代码替换为df = pd.DataFrame(visible_data)即可。

内容的提问来源于stack exchange，提问作者lazarea