使用Google Sheets API上传大量数据遇超时及503错误求解决方案

问题背景 #

尝试通过Google Sheets API上传39345行×60列的数据，先后遇到两类错误，且分批上传、修改超时设置均未解决。

初始错误（socket超时） #

Error, sleep for 101 seconds
Traceback (most recent call last):
  File "C:\***lib\site-packages\pysuite\gsheets.py", line 38, in execute
    return func.execute()
  File "C:\***lib\site-packages\googleapiclient\_helpers.py", line 130, in positional_wrapper
    return wrapped(*args, **kwargs)
  File "C:\***lib\site-packages\googleapiclient\http.py", line 923, in execute
    resp, content = _retry_request(
  File "C:\***lib\site-packages\googleapiclient\http.py", line 222, in _retry_request
    raise exception
  File "C:\***lib\site-packages\googleapiclient\http.py", line 191, in _retry_request
    resp, content = http.request(uri, method, *args, **kwargs)
  File "C:\***lib\site-packages\google_auth_httplib2.py", line 218, in request
    response, content = self.http.request(
  File "C:\***lib\site-packages\httplib2\__init__.py", line 1724, in request
    (response, content) = self._request(
  File "C:\***lib\site-packages\httplib2\__init__.py", line 1444, in _request
    (response, content) = self._conn_request(conn, request_uri, method, body, headers)
  File "C:\***lib\site-packages\httplib2\__init__.py", line 1396, in _conn_request
    response = conn.getresponse()
  File "C:\python\Python39\lib\http\client.py", line 1377, in getresponse
    response.begin()
  File "C:\python\Python39\lib\http\client.py", line 320, in begin
    version, status, reason = self._read_status()
  File "C:\python\Python39\lib\http\client.py", line 281, in _read_status
    line = str(self.fp.readline(_MAXLINE + 1), "iso-8859-1")
  File "C:\python\Python39\lib\socket.py", line 704, in readinto
    return self._sock.recv_into(b)
  File "C:\python\Python39\lib\ssl.py", line 1241, in recv_into
    return self.read(nbytes, buffer)
  File "C:\python\Python39\lib\ssl.py", line 1099, in read
    return self._sslobj.read(len, buffer)
socket.timeout: The read operation timed out

修改超时设置后的错误（503服务不可用） #

先执行以下代码延长超时时间：

import socket

socket.setdefaulttimeout(3600)

随后出现错误：

<HttpError 503 when requesting https://sheets.googleapis.com/v4/spreadsheets/*******/values/%(SheetName)%27%21A1:append?valueInputOption=USER_ENTERED&insertDataOption=INSERT_ROWS&alt=json returned "The service is currently unavailable.". Details: "The service is currently unavailable.">

Process finished with exit code 1

已尝试的解决方法 #

• 分批上传：从1000行逐步降至50行，问题依旧
• 延长默认socket超时时间至3600秒，出现503错误

疑问 #

Google Sheets API是否存在追加行数限制？有哪些可行的解决办法？

解答 #

关于API追加行数限制 #

Google Sheets API确实有相关限制：

• 单请求最多可写入50000行或1000000单元格（以先达到的限制为准），你的单批次50行×60列=3000单元格，远低于单请求限制，所以不是单批次行数的问题。
• 此外还有配额限制：普通用户每分钟最多60次请求，每天最多100000次请求；G Suite/Workspace用户配额更高。如果请求频率过高，会触发限流，可能表现为超时或503错误。

可行解决办法 #

1. 实现指数退避重试机制
   遇到503、超时这类临时错误时，按指数间隔（如1s、2s、4s、8s…，上限设为30s）等待后重试，避免持续给服务器施压。可以自己封装重试逻辑：

   from googleapiclient.errors import HttpError
   import time
   import socket
   
   def retry_upload(func, max_retries=5):
       retries = 0
       while retries < max_retries:
           try:
               return func()
           except (socket.timeout, HttpError) as e:
               if isinstance(e, HttpError) and e.resp.status != 503:
                   raise
               wait_time = min(2 ** retries, 30)
               time.sleep(wait_time)
               retries += 1
       raise Exception("Max retries exceeded")
   
   # 使用示例：把append请求传入lambda
   retry_upload(lambda: service.spreadsheets().values().append(
       spreadsheetId="your_spreadsheet_id",
       range="Sheet1!A1",
       valueInputOption="USER_ENTERED",
       insertDataOption="INSERT_ROWS",
       body={"values": your_batch_data}
   ).execute())
   

2. 控制请求频率
   每批次上传后等待1-2秒，避免触发每分钟请求配额限制。可以在Google Cloud控制台查看当前配额使用情况，确认是否达到上限。

3. 改用batchUpdate接口
   相比values.append，batchUpdate可在一个请求中执行多个写入操作，减少请求次数，效率更高。示例代码：

   def get_sheet_id(service, spreadsheet_id, sheet_name):
       sheet_metadata = service.spreadsheets().get(spreadsheetId=spreadsheet_id).execute()
       for sheet in sheet_metadata['sheets']:
           if sheet['properties']['title'] == sheet_name:
               return sheet['properties']['sheetId']
       return None
   
   def batch_update_sheet(service, spreadsheet_id, sheet_name, data):
       sheet_id = get_sheet_id(service, spreadsheet_id, sheet_name)
       requests = []
       # 先获取当前已用行数，确定追加起始位置
       current_rows = service.spreadsheets().values().get(
           spreadsheetId=spreadsheet_id, range=f"{sheet_name}!A:A"
       ).execute().get('values', [])
       start_row = len(current_rows) + 1
   
       for row_idx, row in enumerate(data):
           requests.append({
               "updateCells": {
                   "range": {
                       "sheetId": sheet_id,
                       "startRowIndex": start_row + row_idx - 1,
                       "endRowIndex": start_row + row_idx,
                       "startColumnIndex": 0,
                       "endColumnIndex": len(row)
                   },
                   "rows": [{"values": [{"userEnteredValue": {"stringValue": str(cell)}} for cell in row]}],
                   "fields": "userEnteredValue"
               }
           })
       return service.spreadsheets().batchUpdate(
           spreadsheetId=spreadsheet_id, body={"requests": requests}
       ).execute()
   

4. 检查网络环境
   超时错误可能和网络稳定性有关，尝试切换网络环境，减少请求传输过程中的中断。

5. 确认服务状态
   偶尔Google Sheets API会出现临时故障，可查看官方状态页面确认服务是否正常。

内容的提问来源于stack exchange，提问作者Manuel Perez Heredia