CODEWITHSUNDEEP
pythongoogle-cloud-platformgoogle-sheets-api
Manuel Perez Heredia
Manuel Perez Heredia

Reputation: 2357

Google Sheets API: socket.timeout: The read operation timed out

I am trying to upload about 40k rows (39345 rows x 60 columns) with Google Sheets API and I am getting the following error:

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

I am trying to circumvent the problem by uploading in batches of 1k rows (I lowered them to 50 rows!) but the problem still persists.

I have also tried:

import socket

######### then:

socket.setdefaulttimeout(3600)

Now the error is:

<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

Do you know if there is a limit to the number of rows one can append to a spreadsheet using this API? Any idea on how to solve this?

Upvotes: 0

Views: 1163

Answers (1)

HaPsantran
HaPsantran

Reputation: 6267

Batching is a good way to go, but sometimes the sockets just die. My solution to this problem is to create a new service object.

That is:

from apiclient.discovery import build as build_google_service
service = build_google_service(service_name, service_version, credentials=creds, cache_discovery=False)

I have retry logic that, depending on the endpoint, tries up to five times (with lags up to a minute between tries) with five different service objects. Occasionally (because I run lots of processes throughout the day), even that's not enough, so I do wonder if there's something else I might be missing. I will probably try six or seven first, though.

I admit not to knowin much about sockets, but this approach has addressed probably 99% of this problem's incidence.

Upvotes: 1

Related Questions