python gspread connection time out when writing to google sheets

Question

here's my existing connection code, which works fine

json_key = json.load(open('path/to/json.js')) 
scope = ['https://spreadsheets.google.com/feeds']
credentials = SignedJwtAssertionCredentials(json_key['client_email'], json_key['private_key'], scope)
gc = gspread.authorize(credentials)

but after 10 mintutes or so my connection will time out.

after reading around a bit i came across the use of this in the connection code,

headers = gspread.httpsession.HTTPSession(headers={'Connection':'Keep-Alive'})

but i'm not sure how include into my existing connection code. what is the correct way to include it? the examples i've seen are not the same as my connection code above, and the examples specify a password that's not in the json connection file that i'm using

here's an an output of the error. sometimes they differ slightly. but it's all down to the same connection time out

File "/usr/local/lib/python2.7/dist-packages/gspread/models.py", line 429, in update_acell
return self.update_cell(*(self.get_int_addr(label)), val=val)
File "/usr/local/lib/python2.7/dist-packages/gspread/models.py", line 440, in update_cell
self._cell_addr(row, col))
File "/usr/local/lib/python2.7/dist-packages/gspread/client.py", line 271, in get_cells_cell_id_feed
r = self.session.get(url)
File "/usr/local/lib/python2.7/dist-packages/gspread/httpsession.py", line 79, in get
return self.request('GET', url, **kwargs)
File "/usr/local/lib/python2.7/dist-packages/gspread/httpsession.py", line 75, in request
raise HTTPError("%s: %s" % (response.status, response.read()))

Coco

it's pretty tricky to get this work. I've tried serveral methods like restart the script after it gives after an hour an error 403. The solution to give the argument 'keep-alive' dont work anymore. – Rhine

hi user3768071, i was able to workaround the time out issue by writing data as a batch, as opposed to having to keep the connection open to write line by line. if you need me to elaborate more on the solution then let me know – Coco

Answer 1

If the time is exceeded, you can call a Function to Reconnect to the Database. I have provided a sample code snippet that you can incorporate within your program.

import gspread
from oauth2client.service_account import ServiceAccountCredentials

from datetime import datetime
from datetime import timedelta

def connect_database():

    scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
    creds = ServiceAccountCredentials.from_json_keyfile_name('client_secret.json', scope)
    client = gspread.authorize(creds)

    workbook1 = client.open("File_Name")
    w1 = workbook1.worksheet("Sheet1")
    w2 = workbook1.worksheet("Sheet2")

    return w1,w2

program_start_time=datetime.now()

if datetime.now() > program_start_time + timedelta(minutes=10):
    worksheet1, worksheet2 = connect_database()
    program_start_time = datetime.now()   #Resets the Program Start Time

Undercoating