Gspread batch update traceback

I am having problems trying to prepopulate google sheets filter and then do a batch_update. I get correct execution, the table is imported, filters are created. When the batch update occurs, I get a traceback that I don’t understand. I thought that the traceback may have come from the way I coded the sheetId. If anyone has some wisdom on how to read the traceback so that I can fix the underlying problem, I would appreciate it.

I am using python 3.10, and gspread 5.3.2, and my filter setup comes from my read of the underlying Google Sheets batchUpdate method.

Please advise, Tim

Gspread-batchUpdate Google Sheets batchUpdate

Traceback (most recent call last):
  File "/Users/peiffer/sre-cert-manager/scripts/get-certificates2.py", line 282, in <module>
    main()
  File "/Users/peiffer/sre-cert-manager/scripts/get-certificates2.py", line 268, in main
    import_csv_to_gsheet(output_file, google_api_credentials)
  File "/Users/peiffer/sre-cert-manager/scripts/get-certificates2.py", line 215, in import_csv_to_gsheet
    worksheet.batch_update(body)
  File "/Users/peiffer/venv/lib/python3.10/site-packages/gspread/utils.py", line 600, in wrapper
    return f(*args, **kwargs)
  File "/Users/peiffer/venv/lib/python3.10/site-packages/gspread/worksheet.py", line 848, in batch_update
    data = [
  File "/Users/peiffer/venv/lib/python3.10/site-packages/gspread/worksheet.py", line 849, in <listcomp>
    dict(vr, range=absolute_range_name(self.title, vr["range"])) for vr in data
TypeError: string indices must be integers

Code snippet follows:

#/usr/bin/env python3.10

name = 'MySheet'
gc = gspread.service_account(filename=path_to_credentials)

try:
    spreadsheet = gc.open(name)
except gspread.SpreadsheetNotFound:
    spreadsheet = gc.create(name)
    sharewith = '<email>'
    spreadsheet.share(sharewith, perm_type='user',
                      notify=True, role='writer')
results = gc.import_csv(spreadsheet.id, open(
    filepath, 'r').read().encode('utf-8'))
_ = results
worksheet = spreadsheet.sheet1
#
# Misc code here
#
worksheet.sort((1, 'asc'), range='A3:M16000')
worksheet.set_basic_filter(name='A2:M16000')
status_values_to_exclude = ["Expired", "Revoked"]
filter_specs = [{"columnIndex": 3, "filterCriteria": {"hiddenValues": status_values_to_exclude}}]
grid_range = gspread.utils.a1_range_to_grid_range('A2:M16000')
grid_range['sheetId'] = worksheet._properties['sheetId']
body = {"requests": [{"setBasicFilter": {"filter": {"range": grid_range, "filter_specs": filter_specs}}}]}
worksheet.batch_update(body)

Answer

Hi I believe the right method you are looking for is batch_update but in the spreadsheet object, not in the worksheet object.

It seems you are sending a complete request that you build manually, and the method batch_update in the worksheet object is only for updating values.

You should use:

...
body = {"requests": [{"setBasicFilter": {"filter": {"range": grid_range, "filter_specs": filter_specs}}}]}
spreadsheet.batch_update(body)

Advertisement

Answer