Continuing script from where an error occurred inside a for loop-CodePudding

I have a code that writes and formats data into google sheets like so:

sheet = client.open("google_sheet")
cats = ['A', 'B', 'C', 'D']

for cat in cats:
    ws = sheet.worksheet(cat)
    for cell in all_columns:

        ws.update_cell(50, cell.col, f"=('A5 / 1.2) * (1 - A14)")  # dummy formula
        # ...
        ws.update_cell(14, cell.col, f"=('C5 / 1.2) * (1 - G14)")  # dummy formula # 50

So in general I have a google sheet with multiple sheets in it to which I write dataframes and then using gspread module I change cell values / formatting and I am constantly getting API error for too many write requests per minute. I tried adding time.sleep(x) here and there but this does not help me. I thought of using try / except but in this structure I have, when I would get the error, continue statement would then run all ws.update_cell functions from the very top because all of those updates are happening in a single loop.

I would not want to do something like this:

for cat in cats:
    ws = sheet.worksheet(cat)
    for cell in all_columns:
        try:
            ws.update_cell(50, cell.col, f"=('A5 / 1.2) * (1 - A14)")  # dummy formula # 1
        except API error:
            time.sleep(60)
            ws.update_cell(50, cell.col, f"=('A5 / 1.2) * (1 - A14)")  # dummy formula # 1

        try:
            ws.update_cell(50, cell.col, f"=('A5 / 1.2) * (1 - A14)")  # dummy formula # 2
        except API error:
            time.sleep(60)
            ws.update_cell(50, cell.col, f"=('A5 / 1.2) * (1 - A14)")  # dummy formula # 2

        # ...
        ws.update_cell(14, cell.col, f"=('C5 / 1.2) * (1 - G14)")  # dummy formula # 50

Which I don't believe is the best practice. I thought of writing a function that would have the try / except in it but each of the ws.update_cell formulas are unique.

So I don't know what is the best practice for a problem when an error occurs in a for loop, wait 60 seconds then try the same thing again for a non unique update statements inside a for loop.

CodePudding user response：

I think your problem is twofold. Even if all the formulas were really different and there seems to be no apparent way to simplify them, you could always write a function that takes in the differences between your formulas from the parameters, that you must specify elsewhere, like in a big list. There is of course no better way to simplify this if the formulas are genuinely very different.

Once you have done that, do not repeat the code but put everything into a while True loop that will break (going to the next formula) only if the action succeeded. Of course there is a risk to be stuck in the loop by doing this without any max retries limit.

def function(num,formula):
    ws.update_cell(num, cell.col, formula)

your_functions=[
    [50,f"=('A5 / 1.2) * (1 - A14)"],
    #...
    [14,f"=('C5 / 1.2) * (1 - G14)"]
]

for num,formula in your_functions:
    while True:
        try:
            function(num,formula)
        except:
            #eventual sleep here
            continue
        else:
            break

CodePudding user response：

What you should do is create a super class of the gspread client.

Then you override the request method there you can handle your time sleep etc without touching a line of you application code.

Have a look at the client used in the test suite for gspread it does exactly that.