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

Question

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?

About I am trying to upload about 40k rows to via Google Sheets API, how many columns are in your data? I would like to confirm the number of cells in your data. — Tanaike
– Tanaike, Commented Oct 25, 2023 at 11:39
Same issue. I can't calculate total cells now but there is not 10M cells. The part of the code worked for several months. But recently the Sheet began to slow down a lot. And now I'm getting this error. I associate this with the problem of quick access to the specific Sheet. It needs destructuring and simplification of steam calculation formulas — oshliaer
– oshliaer, Commented Jan 28, 2024 at 8:13

HaPsantran · Accepted Answer · 2024-12-05 14:45:49Z

1

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.

answered Dec 5, 2024 at 14:45

HaPsantran

6,3077 gold badges26 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

dantebarba Jan 30 at 15:04

I refactored the Gdrive service creation to be created and injected once. Gdrive API was returning these weird timeouts when creating documents and I wasn't sure what it was. Can't believe the issue was related to refactoring the service instantiation.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related