频繁调用Google Sheets API致Python报错，求解决方案

问题背景 #

我正在为Slack工作区开发机器人，将频道消息归档到Google Sheets（Slack消息数月后会被删除）。但短时间内发送大量消息时，会触发多种SSL相关错误，最常见的是ssl.SSLError: [SSL: WRONG_VERSION_NUMBER]，还有OSError: [Errno 0] Error和ssl.SSLError: [SSL: DECRYPTION_FAILED_OR_BAD_RECORD_MAC]。

当前读写Google Sheets的代码：

from __future__ import print_function
from googleapiclient.discovery import build
from google.oauth2 import service_account

path_to_credentials = ...

credentials = service_account.Credentials.from_service_account_file(path_to_credentials, scopes=SCOPES)
spreadsheet_service = build('sheets', 'v4', credentials=credentials)
drive_service = build('drive', 'v3', credentials=credentials)
SPREADSHEET_ID = ...

def read_range(range):
    range_name = 'Sheet1!' + range
    spreadsheet_id = SPREADSHEET_ID
    result = spreadsheet_service.spreadsheets().values().get(
    spreadsheetId=spreadsheet_id, range=range_name).execute()
    rows = result.get('values', [])
    return rows

def write_range(range, write_rows):
    spreadsheet_id = SPREADSHEET_ID
    range_name = 'Sheet1!' + range
    value_input_option = 'USER_ENTERED'
    body = {
        'values': write_rows
    }
    result = spreadsheet_service.spreadsheets().values().update(
        spreadsheetId=spreadsheet_id, range=range_name,
        valueInputOption=value_input_option, body=body).execute()

错误栈示例：

Traceback (most recent call last):
  File "../srs/ApiMains/message_main.py", line 39, in message_main
    recordQuestionAndAnswerMessages(thread_ts=out_ts, name=name, message=text, ts=ts)
  File "../srs/ApiMains/message_main.py", line 131, in recordQuestionAndAnswerMessages
    write_range("A2:A2", [[next_col_num + 1]])
  File "../srs/util/sheets_util.py", line 32, in write_range
    result = spreadsheet_service.spreadsheets().values().update(
  File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/googleapiclient/_helpers.py", line 130, in positional_wrapper
    return wrapped(*args, **kwargs)
  File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/googleapiclient/http.py", line 844, in execute
    resp, content = _retry_request(
  File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/googleapiclient/http.py", line 183, in _retry_request
    raise exception
  File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/googleapiclient/http.py", line 164, in _retry_request
    resp, content = http.request(uri, method, *args, **kwargs)
  File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/google_auth_httplib2.py", line 197, in request
    response, content = self.http.request(
  File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/httplib2/__init__.py", line 1701, in request
    (response, content) = self._request(
  File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/httplib2/__init__.py", line 1421, in _request
    (response, content) = self._conn_request(conn, request_uri, method, body, headers)
  File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/site-packages/httplib2/__init__.py", line 1373, in _conn_request
    response = conn.getresponse()
  File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/http/client.py", line 1322, in getresponse
    response.begin()
  File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/http/client.py", line 303, in begin
    version, status, reason = self._read_status()
  File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/http/client.py", line 264, in _read_status
    line = str(self.fp.readline(_MAXLINE + 1), "iso-8859-1")
  File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/socket.py", line 669, in readinto
    return self._sock.recv_into(b)
  File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/ssl.py", line 1241, in recv_into
    return self.read(nbytes, buffer)
  File "/Library/Frameworks/Python.framework/Versions/3.8/lib/python3.8/ssl.py", line 1099, in read
    return self._sslobj.read(len, buffer)
ssl.SSLError: [SSL: WRONG_VERSION_NUMBER] wrong version number (_ssl.c:2607)

原因分析 #

这些SSL错误本质是Google Sheets API速率限制触发的底层网络异常：

• Google Sheets API有严格的配额限制（默认每100秒最多100个写入请求），短时间内频繁发起单个单元格的update请求，会触发速率限制。
• 当请求被限制时，Google服务器不会总是返回明确的429状态码，可能直接中断SSL连接，导致客户端出现版本不匹配、解密失败这类底层错误。

解决方法 #

1. 批量写入，减少请求次数 #

不要每次只写单个单元格，将多条消息的数据收集后一次性写入，大幅降低请求频率。比如修改为批量追加数据：

def append_rows(write_rows):
    spreadsheet_id = SPREADSHEET_ID
    range_name = 'Sheet1!A:Z'  # 覆盖需要写入的列范围
    value_input_option = 'USER_ENTERED'
    body = {
        'values': write_rows
    }
    result = spreadsheet_service.spreadsheets().values().append(
        spreadsheetId=spreadsheet_id, range=range_name,
        valueInputOption=value_input_option, body=body,
        insertDataOption='INSERT_ROWS').execute()

使用时，先将多条消息的信息（比如用户名、消息内容、时间戳）收集到列表中，再调用append_rows一次性写入。

2. 配置指数退避重试 #

针对SSL错误和速率限制错误，实现指数退避的重试逻辑，避免频繁失败：

from googleapiclient.errors import HttpError
import time
import ssl

def safe_write_range(range, write_rows):
    max_retries = 5
    retry_delay = 1  # 初始延迟1秒
    for attempt in range(max_retries):
        try:
            spreadsheet_id = SPREADSHEET_ID
            range_name = 'Sheet1!' + range
            value_input_option = 'USER_ENTERED'
            body = {'values': write_rows}
            result = spreadsheet_service.spreadsheets().values().update(
                spreadsheetId=spreadsheet_id, range=range_name,
                valueInputOption=value_input_option, body=body).execute()
            return result
        except (HttpError, ssl.SSLError, OSError) as e:
            # 检查是否是速率限制或SSL相关错误
            if isinstance(e, HttpError) and e.resp.status == 429:
                print(f"速率限制触发，{retry_delay}秒后重试...")
            else:
                print(f"错误发生：{str(e)}，{retry_delay}秒后重试...")
            time.sleep(retry_delay)
            retry_delay *= 2  # 指数退避，每次延迟翻倍
    raise Exception("已达到最大重试次数，请求失败")

3. 控制请求速率 #

如果必须保持单个请求模式，可以添加固定延迟降低请求频率：

import time

def write_range(range, write_rows):
    spreadsheet_id = SPREADSHEET_ID
    range_name = 'Sheet1!' + range
    value_input_option = 'USER_ENTERED'
    body = {'values': write_rows}
    result = spreadsheet_service.spreadsheets().values().update(
        spreadsheetId=spreadsheet_id, range=range_name,
        valueInputOption=value_input_option, body=body).execute()
    time.sleep(0.5)  # 添加0.5秒延迟，控制请求频率

4. 升级依赖库 #

老旧的google-api-python-client、httplib2或google-auth-httplib2可能存在SSL相关bug，升级到最新版本：

pip install --upgrade google-api-python-client google-auth-httplib2 httplib2

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