Using gspread, trying to color one string

Question

In google sheets, I am trying to find a string something like "find" and highlight the string. Please see below.

cell_list = worksheet.findall("find")

score 1 · Accepted Answer · answered Mar 04 '22 at 01:53

1

I believe your goal is as follows.

You want to search a text from a sheet on Google Spreadsheet using findall.
You want to change the font color of the searched cells.
You want to achieve this using gspread for python.

In this case, how about the following sample script?

Sample script:

client = gspread.authorize(credentials) # Please use your authorization script.
spreadsheetId = "###" # Please set the Spreadsheet ID.
sheetName = "Sheet1" # Please set the sheet name you want to search.
search_text = "find" # Please set the search text.
color = {"red": 1, "green": 0, "blue": 0} # Please set the color. In this sample, the red color is used.

spreadsheet = client.open_by_key(spreadsheetId)
worksheet = spreadsheet.worksheet(sheetName)
cell_list = worksheet.findall(search_text)
if cell_list != []:
    sheet_id = worksheet.id
    reqs = []
    for e in cell_list:
        reqs.append({
            "updateCells": {
                "range": {
                    "sheetId": sheet_id,
                    "startRowIndex": e._row - 1,
                    "endRowIndex": e._row,
                    "startColumnIndex": e._col - 1,
                    "endColumnIndex": e._col
                },
                "rows": [
                    {
                        "values": [
                            {
                                "userEnteredFormat": {
                                    "textFormat": {
                                        "foregroundColor": color
                                    }
                                }
                            }
                        ]
                    },
                ],
                "fields": "userEnteredFormat.textFormat.foregroundColor"
            }
        })
    res = spreadsheet.batch_update({"requests": reqs})

When this script is run, the value of search_text is searched from the sheet. And, the font color of the searched cells is changed.

Reference:

UpdateCellsRequest

answered Mar 04 '22 at 01:53

Tanaike

181,128
11
97
165

oh wow, that was awesome. I just had to modify the first line and it worked like magic. Thanks again, Tanaike! – himnaejah Mar 04 '22 at 03:45
As supplement information, In the case of the current [`format` method](https://docs.gspread.org/en/v5.2.0/api.html?highlight=format#gspread.worksheet.Worksheet.format), when I saw the script of gspread, I noticed that when the formats of several cells are changed, Sheets API is required to be used for each cell. In the OP's situation, there might be several cells in `cell_list = worksheet.findall("find")`. So I proposed to directly use the batchUpdate method because in this case, the formats of several cells can be changed by one API call. – Tanaike Mar 05 '22 at 08:44
Hey Tanaike, if I am trying to find more than string, can I create a list within the search text? – himnaejah Mar 06 '22 at 23:03
@himnaejah About your new question of `if I am trying to find more than string, can I create a list within the search text?`, I would like to support you. But unfortunately, I cannot correctly understand your new question. So can you post it as new question by including more information? By this, it will help users including me think of the solution. When you can cooperate to resolve your new question, I'm glad. Can you cooperate to do it? – Tanaike Mar 06 '22 at 23:06
In this case, there is one string that we are trying to find, "Find" but if I am trying to find more than many strings, say "Find", "Find2", "Find3", "Find4". Is there a way to do that? – himnaejah Mar 06 '22 at 23:12
@himnaejah Thank you for replying. In that case, I think that your new question is different from your this 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 Mar 06 '22 at 23:14
I just did. https://stackoverflow.com/questions/71374957/using-gspread-trying-to-color-many-strings – himnaejah Mar 06 '22 at 23:18
1

@himnaejah Thank you for your response. I will check it. – Tanaike Mar 06 '22 at 23:18

score 1 · Answer 2 · answered Mar 05 '22 at 08:22

lat st version of gspread has a dedicated method for formating cells. No need to write your own request. It also has a method to convert cell indices to A1 notation. You could simply do:

range = rowcol_to_a1(e.row, e.col):
worksheet.format(range, {"foregroundColor": color})

See documentation

Using gspread, trying to color one string

2 Answers2

Sample script:

Reference: