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")
CodePudding user response:
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.