How to speed up updating cell colors with pygsheets?

Question

I'm using this code to update the background color of a range of cells in a google sheet:

from pygsheets import Workbook, Color

gc = pygsheets.authorize(service_file='path/to/credentials.json')
workbook = gc.open('spreadsheet_name')
worksheet = workbook.worksheet_by_title('Sheet1')

cell_range = worksheet.range('E2:J37')
for row in cell_range:
    for cell in row:
        cell.color = (0.8, 0.8, 0.8)

But the program is extremely slow. After it does a chunk of cells, it will hang for several minutes before continuing, and as a result for a range this size it takes like 20 minutes, somewhat undermining the point of automating this. Is there a way to speed this up? From what I can tell there isn't a way to set the formatting for a range of cells directly, necessitating this iterative approach.

Answer 1

I believe your goal is as follows.

• You want to reduce the process cost of your script.

In your script, the background color is changed every cell in a loop. I think that this is the reason for your current issue. And, in this case, the APIs of the number of loops are used. In your situation, I thought that the batchUpdate method can be used. When this is reflected in your script, it becomes as follows.

From:

cell_range = worksheet.range('E2:J37')
for row in cell_range:
    for cell in row:
        cell.color = (0.8, 0.8, 0.8)

To:

requests = [
    {
        "repeatCell": {
            "range": worksheet.get_gridrange("E2", "J37"),
            "cell": {
                "userEnteredFormat": {
                    "backgroundColor": {"red": 0.8, "green": 0.8, "blue": 0.8}
                }
            },
            "fields": "userEnteredFormat.backgroundColor",
        }
    }
]
gc.sheet.batch_update(workbook.id, requests)

• By this modification, the background color of cells "E2:J37" is changed to {"red": 0.8, "green": 0.8, "blue": 0.8} by one API call.

References:

• Method: spreadsheets.batchUpdate
• batch_update