I am trying to replace all the values in a column which starts with “barcode”. However for now, I am able to replace all the values based on the name of the values.
import gspread from oauth2client.service_account import ServiceAccountCredentials scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive'] credentials = ServiceAccountCredentials.from_json_keyfile_name('extended-signal-307001- 4e0bee527c0d.json', scope) gc = gspread.authorize(credentials) wks = gc.open('test').sheet1 column_o = wks.findall('barcode recognition error', in_column=15) print(column_o) for cell in column_o: cell.value = 'barcode error' wks.update_cells(column_o)
Currently, it only replaces if it finds the exact value inside the column which is “barcode recognition error”. I want to find the values that starts with “barcode” and replace all those values with “barcode error”.
Advertisement
Answer
I believe your goal and your current situation as follows.
- You want to replace the cell value including
barcode
at the top word withbarcode error
. - You want to achieve this using gspread with python.
- You have already been able to get and put values for Google Spreadsheet using Sheets API.
In this case, I would like to propose to use the batchUpdate method of Sheets API. I thought that when the batchUpdate method is used, your goal can be achieve by one API call. The sample script is as follows.
Sample script:
Please use your script for retrieving credentials
.
gc = gspread.authorize(credentials) spreadsheet = gc.open('test') wks = spreadsheet.sheet1 body = { "requests": [ { "findReplace": { "find": "^barcode[\s\w]+", "searchByRegex": True, "range": { "sheetId": wks.id, }, "replacement": "barcode error" } } ] } spreadsheet.batch_update(body)
- In this sample script, all cells in the 1st tab of the Spreadsheet are checked and replaced with
barcode error
. If you want to check the specific column and row, please set the gridRange forrange
.