How to update values in loop with gspread, without "Range exceeds grid limits"

Question

Python 3.9.7 with gspread 4.0.1 My script is run every 8 seconds and checks for new emails in this spreadsheet with empty status field (row1). And if empty - it subscribes the person. Also uses api to check email quality, and later updates fields with email quality scores. Python Gspread is used to interact with google sheets api.

Once a day or two i get this error.

{'code': 400, 'message': 'Range (LeadsFromTilda!F1632) exceeds grid limits. Max rows: 1631, max columns: 26', 'status': 'INVALID_ARGUMENT'}

But in breadcrumbs i see that request just before this erroneous one, was successful. and it was on 1632 !

Here is a relevant part of my code. Full version is available at codereview

    gc = gspread.service_account_from_dict(credentials)
sh = gc.open("Tilda_Form_v4 emails")
ws = sh.get_worksheet(0)


def dotable():
    all_values = ws.get_all_values()
    for index, row in enumerate(all_values):
        if row[1] == "subscribed" or row[1] == "error":
            pass
        elif row[1] == "":
            rownum = index + 1
            name = row[0]
            email = row[5]
            print(fr"Empty status {name},{email} on {rownum} ")
            success, response = subscribe(email, name)
            if success:
                ws.update("C" + str(index + 1), 'moded_ok')
                check_result_json = checkemail(email)
                check_result = "Overall Score (bigger better) "+str(check_result_json["overall_score"])
                ws.update("F" + str(rownum), check_result) # Result email check. Overall score
                ws.update("H" + str(rownum), str(check_result_json)) # Result email check full data
                ws.update("G" + str(rownum), parse_bmresponse(response))
                break
            else:
                ws.update("C" + str(rownum), 'moded_noluck')
                check_result_json = checkemail(email)
                ws.update("H" + str(rownum), str(check_result_json))  # Result email check full
                ws.update("G" + str(rownum), parse_bmresponse(response))
                break # important notice here. Only 1 email per iteration. (google api limits)
        else:
            pass


while 1 > 0:
    try:
        dotable()
        time.sleep(8)

What i tried to fix it:

1. Changing sheet name. To "Tilda", "WokLeadsV1d", finally now it is "Tilda_Form_v4 emails" Advice from here
2. Adding more rows or removing all empty ones. Still get this error. Now the end of the spreadsheet looks like that

Please help me fix this bug.

Answer 1

From your script and your sample Spreadsheet, I thought that ws.update uses one API call. So, I thought that when the multiple ws.update methods are populated by one API call by the method of "spreadsheets.values.batchUpdate", your issue might be able to be removed. So in this modification, I would like to propose this. When your script is modified, it becomes as follows.

Modified script:

In this modification, your dotable() is modified.

def dotable():
    all_values = ws.get_all_values()
    values = []
    for index, row in enumerate(all_values):
        if row[1] == "subscribed" or row[1] == "error":
            pass
        elif row[1] == "":
            rownum = index + 1
            name = row[0]
            email = row[5]
            print(fr"Empty status {name},{email} on {rownum} ")
            success, response = subscribe(email, name)
            if success:
                check_result_json = checkemail(email)
                check_result = "Overall Score (bigger better) " + str(check_result_json["overall_score"])
                values.append({"range": "'" + ws.title + "'!C" + str(index + 1), "values": [['moded_ok']]})
                values.append({"range": "'" + ws.title + "'!F" + str(rownum), "values": [[check_result]]})
                values.append({"range": "'" + ws.title + "'!H" + str(rownum), "values": [[str(check_result_json)]]})
                values.append({"range": "'" + ws.title + "'!G" + str(rownum), "values": [[parse_bmresponse(response)]]})
                break
            else:
                check_result_json = checkemail(email)
                values.append({"range": "'" + ws.title + "'!C" + str(rownum), "values": [['moded_noluck']]})
                values.append({"range": "'" + ws.title + "'!H" + str(rownum), "values": [[str(check_result_json)]]})
                values.append({"range": "'" + ws.title + "'!G" + str(rownum), "values": [[parse_bmresponse(response)]]})
                break
        else:
            pass

    access_token = credentials.get_access_token().access_token # or credentials.access_token
    res = requests.post(
        'https://sheets.googleapis.com/v4/spreadsheets/' + sh.id + '/values:batchUpdate',
        headers={"Authorization": "Bearer " + access_token, "Content-Type": "application/json"},
        data=json.dumps({"data": values, "valueInputOption": "USER_ENTERED"}),
    )

Note:

• In this modification, it supposes that your functions of subscribe, parse_bmresponse, and checkemail work fine. Please be careful about this.

• I'm not sure about the scopes you are using. So, when an error is related to the scopes, please add https://www.googleapis.com/auth/spreadsheets to the scopes.

• By the above modification, Sheets API is not used in the loop. The values are put to the Spreadsheet by one API call. But, if the same error occurs by the following script,

    while 1 > 0:
        try:
            dotable()
            time.sleep(8)
  

  • Please modify 8 of time.sleep(8) and test it again.

• In the current stage, the method of "spreadsheets.values.batchUpdate" is included in the methods of gspread. So I directly requested to the endpoint of Sheets API. Ref So, please add import requests and import json.

Reference:

• Method: spreadsheets.values.batchUpdate