如何用Python和Google API获取Google Sheets历史版本内容

问题背景 #

我有多份Google Sheets表格，需要获取它们的历史版本。这些历史表格记录产品状态，将被追加至Google BigQuery表中，因此必须能访问旧表格的实际内容，而非仅元数据。

当前进展与报错 #

已成功配置凭证服务，能获取到版本列表（示例字典格式）：

{'id': '15104',
 'mimeType': 'application/vnd.google-apps.spreadsheet',
 'kind': 'drive#revision',
 'modifiedTime': '2023-06-27T12:41:52.305Z'}

但无法下载历史版本内容，收到403错误：

HttpError: <HttpError 403 when requesting https://www.googleapis.com/drive/v3/files/1D1pkeTUDoGZnlHHQh0AiRvFAippyX4OYRWR4XNx3leU/revisions/15098?alt=media returned "Only files with binary content can be downloaded. Use Export with Docs Editors files.". Details: "[{'message': 'Only files with binary content can be downloaded. Use Export with Docs Editors files.', 'domain': 'global', 'reason': 'fileNotDownloadable', 'location': 'alt', 'locationType': 'parameter'}]">

尝试另一代码虽返回200状态码，但下载的XLSX文件无法读取，Google Sheets也无法打开。

尝试代码1 #

import os.path
from google.auth.transport.requests import Request
from google.oauth2.credentials import Credentials
from google_auth_oauthlib.flow import InstalledAppFlow
from googleapiclient.discovery import build

SCOPES = [
    'https://www.googleapis.com/auth/drive',
    'https://www.googleapis.com/auth/drive.file',
    'https://www.googleapis.com/auth/spreadsheets',
]

def login():
    creds = None
    # The file token.json stores the user's access and refresh tokens, and is
    # created automatically when the authorization flow completes for the first
    # time.
    if os.path.exists('token.json'):
        creds = Credentials.from_authorized_user_file('token.json', SCOPES)
    # If there are no (valid) credentials available, let the user log in.
    if not creds or not creds.valid:
        if creds and creds.expired and creds.refresh_token:
            creds.refresh(Request())
        else:
            flow = InstalledAppFlow.from_client_secrets_file(
                'bom_files.json', SCOPES)
            creds = flow.run_local_server(port=0)
        # Save the credentials for the next run
        with open('token.json', 'w') as token:
            token.write(creds.to_json())
    service = build('drive', 'v3', credentials=creds)
    # Call the Drive v3 API
    return service

def get_sheet_revisions(sheet_id,service):
    revisions = service.revisions().list(fileId=sheet_id).execute().get('revisions')
    revised_file_contents = []  # contents of revised files
    for revision in revisions:
        request = service.revisions().get_media(fileId=sheet_id,
                                                revisionId=revision['id'])
        file_contents = request.execute()
        # Do something with the file like save it.
        # For now, lets append it to a list
        revised_file_contents.append(file_contents)
    return revised_file_contents

if __name__ == '__main__':
    service = login()
    historic_sheets = get_sheet_revisions(sheet_id,service)

编辑：尝试代码2 #

import os.path
import gspread
from google.auth.transport.requests import Request
from google.oauth2.credentials import Credentials
import requests

SCOPES = ['https://www.googleapis.com/auth/drive', 'https://www.googleapis.com/auth/spreadsheets']

def login():
    creds = None
    if os.path.exists('token.json'):
        creds = Credentials.from_authorized_user_file('token.json', SCOPES)
    if not creds or not creds.valid:
        if creds and creds.expired and creds.refresh_token:
            creds.refresh(Request())
        else:
            creds = Credentials.from_service_account_file('bom_files.json', scopes=SCOPES)
        with open('token.json', 'w') as token:
            token.write(creds.to_json())
    return creds

def export_sheet_revision(sheet_id, revision_id, export_format):
    creds = login()
    client = gspread.authorize(creds)
    sheet = client.open_by_key(sheet_id)
    url = f"https://docs.google.com/spreadsheets/export?id={sheet_id}&revision={revision_id}&exportFormat={export_format}"
    return sheet, url

def download_file(url, output_path):
    response = requests.get(url)
    with open(output_path, 'wb') as file:
        file.write(response.content)

if __name__ == '__main__':
    sheet_id = '1D1pkeTUDoGZnlHHQh0AiRvFAippyX4OYRWR4XNx3leU'
    sheet_id = '1wl7kLGLAgCnFB0dn7JYubO-ZwnK5-s-4Rxq-mQtRRC8'  # simpler sheet
    revision_id = '15098'
    export_format = 'xlsx'
    
    sheet, download_url = export_sheet_revision(sheet_id, revision_id, export_format)
    worksheets = sheet.worksheets()

    for worksheet in worksheets:
        worksheet_title = worksheet.title
        worksheet_url = download_url + f'&gid={worksheet.id}'
        output_path = f'output_{worksheet_title}.xlsx'  # Specify the desired output file path for each worksheet
        
        download_file(worksheet_url, output_path)
        print(f"Worksheet '{worksheet_title}' downloaded to: {output_path}")

解决方案 #

核心问题 #

Google Sheets属于Docs Editors类文件，无法通过get_media直接下载，必须使用Drive API的导出接口，且历史版本导出需要带有效凭证的请求，不能用未授权的requests.get。

修正实现代码 #

import os.path
from google.auth.transport.requests import Request
from google.oauth2.credentials import Credentials
from google_auth_oauthlib.flow import InstalledAppFlow
from googleapiclient.discovery import build
from googleapiclient.http import MediaIoBaseDownload
import io

SCOPES = [
    'https://www.googleapis.com/auth/drive',
    'https://www.googleapis.com/auth/spreadsheets',
]

def get_drive_service():
    creds = None
    if os.path.exists('token.json'):
        creds = Credentials.from_authorized_user_file('token.json', SCOPES)
    if not creds or not creds.valid:
        if creds and creds.expired and creds.refresh_token:
            creds.refresh(Request())
        else:
            flow = InstalledAppFlow.from_client_secrets_file(
                'bom_files.json', SCOPES)
            creds = flow.run_local_server(port=0)
        with open('token.json', 'w') as token:
            token.write(creds.to_json())
    return build('drive', 'v3', credentials=creds)

def export_sheet_revision(sheet_id, revision_id, export_mime_type, output_path):
    service = get_drive_service()
    # 调用Drive API导出指定版本的表格
    request = service.files().export_media(
        fileId=sheet_id,
        mimeType=export_mime_type,
        revisionId=revision_id
    )
    # 处理二进制响应并保存文件
    fh = io.FileIO(output_path, 'wb')
    downloader = MediaIoBaseDownload(fh, request)
    done = False
    while done is False:
        status, done = downloader.next_chunk()
        print(f"下载进度: {int(status.progress() * 100)}%")

if __name__ == '__main__':
    sheet_id = '1wl7kLGLAgCnFB0dn7JYubO-ZwnK5-s-4Rxq-mQtRRC8'
    revision_id = '15098'
    # XLSX格式对应的MIME类型
    export_mime_type = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    output_path = f'sheet_revision_{revision_id}.xlsx'
    
    export_sheet_revision(sheet_id, revision_id, export_mime_type, output_path)
    print(f"历史版本已导出至: {output_path}")

关键注意事项 #

• 支持的导出格式及对应MIME类型：
  • XLSX: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
  • CSV: text/csv（仅单工作表，需在导出时添加&gid=工作表ID参数）
• 版本保留限制：Drive免费版默认保留最近30天版本，付费版可延长；若版本已被清理则无法获取
• 权限要求：授权用户或服务账号需拥有该Sheet的编辑权限（仅查看权限可能无法导出历史版本）

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