0

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.

Kelo
  • 1,783
  • 2
  • 9
  • 21
Alfredo Lozano Jr
  • 25
  • 7

1 Answers1

0

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:

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • Using batch_update sounds like a great idea. But I dont understand how you incorporate multiple formats into requests. How does that work? Whats the syntax? – Alfredo Lozano Jr Sep 21 '22 at 17:26
  • @Alfredo Lozano Jr Thank you for replying. My proposed script is for your question for changing `backgroundColor`. About your additional question of `But I dont understand how you incorporate multiple formats into requests. How does that work?`, unfortunately, I cannot understand the detail of `incorporate multiple formats`. But, in this case, please add them to `userEnteredFormat`. You can see this at [the official document](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/cells#CellFormat). If this was not useful, I apologize. – Tanaike Sep 21 '22 at 23:37
  • My question was on how you were able to put in multiple formats into a single variable. However I've looked at the documentation a bit I understand now. Thank you so much for your help, your modification worked perfectly. To be able to color thousands of cells specific colors with just one API call is amazing. Thank you again. – Alfredo Lozano Jr Sep 22 '22 at 03:28
  • Hello again. I have another question regarding batch_update. Can it also work for putting a custom formula on each one of the cells? When this formula is true, it colors the cell another color. I want to use the formula =indirect("Sheet1!B2")<>"hello" . meaning when Sheet1 cell B2 has the value "hello", the cell with the formula is colored white, otherwise the cell follows its original formatting. How can I put this formula onto the cells using gspread? – Alfredo Lozano Jr Sep 22 '22 at 05:58
  • @Alfredo Lozano Jr About your new question, I would like to support you. But the issue of replying is new issue, and that is different from your question. So can you post it as new question? Because when your initial question is changed by comment, other users who see your question are confused. By posting it as new question, users including me can think of it. If you can cooperate to resolve your new issue, I'm glad. Can you cooperate to resolve your new question? – Tanaike Sep 22 '22 at 07:06