Search code examples
pythongoogle-sheetsgoogle-drive-apigoogle-sheets-apigspread

Gspread - How can I format many cells at once without exceeding my quota limit?

Essentially, I am attempting to print an image onto a worksheet by changing the color of every cell. When it comes to small images, it can do it just fine. However most images I will be importing are more than 100x100. Not only is this incredibly slow, but I cant do it without exceeding my quota limit.

I don't think using format() 1000+ times is very optimal, is there a way to set up my formats and then "push" it onto a worksheet without calling the API too many times?

for row in rows:
    for column in columns:
        color = (image.get_at((column, row)))
        address = utils.rowcol_to_a1(row+1, column+1)
        #the part where it formats the cell
        sheet2.format(str(address), {"backgroundColor": {'red': color[0]/255, 'green': color[1]/255, 'blue': color[2]/255}})

image.get_at() is from another module, it just gets the color of a pixel.

sheet2.format() is called for every single pixel in the image.

I understand I can use time.sleep() to wait for the quota to reset, but at that rate it will take forever to import any decent sized image.

Solution

  • I believe your goal is as follows.

    • You want to reduce the process cost of your script.
    • You want to achieve this using gspread for python.
      • From your showing script, I thought that you might use gspread.

    In your situation, how about using batch_update? When batch_update is used, the request can be achieved by one API call. When your script is modified, how about the following modification?

    Modified script:

    image = ### # Please declare image.

spreadsheetId = "###" # Please set your Spreadsheet ID.
spreadsheet = client.open_by_key(spreadsheetId)
sheet2 = spreadsheet.worksheet("Sheet1") # Please set your sheet name.
rows = [0, 1, 2, 3,,,] # Please set values of rows.
columns = [0, 1, 2, 3,,,] # Please set values of columns.


rowValues = []
for row in rows:
    colValues = []
    for column in columns:
        color = (image.get_at((column, row))) # I used your script here.
        colValues.append({"userEnteredFormat": {"backgroundColorStyle": {"rgbColor": {"red": color[0] / 255,"green": color[1] / 255,"blue": color[2] / 255}}}})
    rowValues.append({"values": colValues})

requests = {"requests": [{"updateCells": {"rows": rowValues,"range": {"sheetId": sheet2.id,"startRowIndex": rows[0],"endRowIndex": rows[-1] + 1,"startColumnIndex": columns[0],"endColumnIndex": columns[-1] + 1},"fields": "userEnteredFormat.backgroundColorStyle"}}]}
spreadsheet.batch_update(requests)
    • When this script is run, one request of UpdateCellsRequest is created, and request it using batchUpdate method of Sheets API. I thought that by this, your issue might be able to be resolved.

    Note:

    • In this sample script, it supposes that the values of rows and columns are an array like [0, 1, 2, 3,,,] created by the continuous numbers.

    • Unfortunately, I cannot know your actual situation. So, I cannot know the values of rows and columns. So, when the above script didn't work, can you provide more information? For example, can you provide the sample values? By this, I would like to modify the script.

    References: