How to replace all cells that starts with in google sheets using python

Question

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

Answer 1

I believe your goal and your current situation as follows.

• You want to replace the cell value including barcode at the top word with barcode 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 for range.

References:

• Method: spreadsheets.batchUpdate
• FindReplaceRequest
• batch_update(body)