I am posting the results of a webscrape to Google Sheets using the API. The results are stored using a Pandas DataFrame in Python. Keep in mind that I am posting to multiple sheets within one spreadsheet, each identified by sheetName. Here is the code I am using to post the DF:
def df_to_sheet(sheet, sheetName, df):
try:
df.replace(np.nan, '', inplace=True)
df = df.T.reset_index().T.values.tolist()
inputRange = sheetName + '!A1'
response = sheet.values().update(
spreadsheetId=sheets_conn.SPREADSHEET_ID,
valueInputOption='RAW',
range=inputRange,
body=dict(
majorDimension='ROWS',
values=df
)
).execute()
print(response)
return True
except:
return False
On all but two sheets are posting just fine. However, on those two sheets (with the try/except removed) return this error:
File "/usr/lib/python3/dist-packages/httplib2/__init__.py", line 1725, in request
(response, content) = self._request(
File "/usr/lib/python3/dist-packages/httplib2/__init__.py", line 1441, in _request
(response, content) = self._conn_request(conn, request_uri, method, body, headers)
File "/usr/lib/python3/dist-packages/httplib2/__init__.py", line 1364, in _conn_request
conn.request(method, request_uri, body, headers)
File "/usr/lib/python3.10/http/client.py", line 1282, in request
self._send_request(method, url, body, headers, encode_chunked)
File "/usr/lib/python3.10/http/client.py", line 1328, in _send_request
self.endheaders(body, encode_chunked=encode_chunked)
File "/usr/lib/python3.10/http/client.py", line 1277, in endheaders
self._send_output(message_body, encode_chunked=encode_chunked)
File "/usr/lib/python3.10/http/client.py", line 1076, in _send_output
self.send(chunk)
File "/usr/lib/python3.10/http/client.py", line 998, in send
self.sock.sendall(data)
File "/usr/lib/python3.10/ssl.py", line 1236, in sendall
v = self.send(byte_view[count:])
File "/usr/lib/python3.10/ssl.py", line 1205, in send
return self._sslobj.write(data)
ssl.SSLEOFError: EOF occurred in violation of protocol (_ssl.c:2396)
However, I have bypassed this error by converting the DF to a CSV, then reading it back as a DF:
i = 0
if (df_to_sheet(sheet, sheetName, df)):
print("Sheet Posted: ", sheetName)
else:
print("No Sheet Posted:", sheetName)
df.to_csv(csv_path + str(i), index=False)
df = pd.read_csv(csv_path + str(i))
if (df_to_sheet(sheet, sheetName, df)):
print("Sheet Posted using workaround: ", sheetName)
else:
print("Still no Sheet Posted", sheetName)
i += 1
In this case, the problematic sheets get posted using the workaround. I can see no reason that this workaround works, and, preferably, I would like to post everything without it. Does anyone understand the reason why I have to convert to a CSV first?
