CODEWITHSUNDEEP
pythongoogle-sheetsgoogle-sheets-apigspread
Arpit Ranasaria
Arpit Ranasaria

Reputation: 3

Gspread: Formatting specific cells at once (not just a row or column)

I am using gspread along with python to try and build a program that parses an image of a crossword into a google sheet format. Because of this, I need to make cells in an irregular shape colored black, for example perhaps cells A1, B2, and C3.

Now I've been trying to use gspreads worksheet.format function to do so, but it takes in an A1 format as an input that, to my knowledge, only accepts ranges (e.g A1:A3) rather than comma-separated (e.g. A1,B2,C3).

Alternatively, if I format each cell alone, I run into the google sheets API 'write limits' issue with bigger crosswords - because I can't write more than 60 times in a minute. Trying to do a batch_update also causes the same issue.

So, my question is - is there any way I can format irregularly organized cells to the same color at once without triggering the API's write limits? Or am I doomed to just update the sheet at one cell a second?

Upvotes: -1

Views: 2872

Answers (3)

Edmond Dantes
Edmond Dantes

Reputation: 149

It may be a little too late for this answer but anyway I want to share it. I also have difficult having coordinates range but not in the "A1" notation. I did this function to translate it. In my case I have only a universe of 12 columns (one per year month). Sure there should be more pythonic and efficient way to solve this issue, but this worked for me!

def trans_coord(coord_row0, coord_col0, coord_row1, coord_col1):
  """
  Translate coordinate grid of google sheets RANGE into 'A1:B2' format
  coord_row0:  Initial row,
  coord_col0:  Initial column,
  coord_row1:  Final row, 
  coord_col1:  Final column
  """
  
  col_name = {
      1: "A",
      2: "B",
      3: "C",
      4: "D",
      5: "E",
      6: "F",
      7: "G",
      8: "H",
      9: "I",
      10: "J",
      11: "K",
      12: "L"
      }

  a1_notation = f"{col_name[coord_col0]}{coord_row0}:{col_name[coord_col1]}{coord_row1}"
  
  return a1_notation

Upvotes: 1

clrife
clrife

Reputation: 46

Did you try the batch option with format_cell_ranges? That should allow you to pass many cells in one call.

From the docs:

fmt = cellFormat(
    backgroundColor=color(1, 0.9, 0.9),
    textFormat=textFormat(bold=True, foregroundColor=color(1, 0, 1)),
    horizontalAlignment='CENTER'
    )

fmt2 = cellFormat(
    backgroundColor=color(0.9, 0.9, 0.9),
    horizontalAlignment='RIGHT'
    )

format_cell_ranges(worksheet, [('A1:J1', fmt), ('K1:K200', fmt2)])

Upvotes: 1

Nikko J.
Nikko J.

Reputation: 5533

Unfortunately, formatting multiple cell with different position requires multiple request. Spreadsheet.batchUpdate UpdateCellsRequest can only update cells in single A1 notation. Example: A1:A1, A1:C3.

What you can do is to increase the Quota of Google Sheets API or create a loop, add counter to every request and use sleep() before it reaches the quota.

Google Sheets API Usage Limit

Upvotes: 1

Related Questions