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")

Answer 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