I have a google sheet that contains multiple filter views with a range that spans the entirety of the spreadseet (Range: A1:D) As there are currently 9 rows of data, the filter view's range is set to A1:D9. I regularly add to this spreadsheet using a python script with the gspread library and as a result, I have to manually update the filter view's range to encompass the spreadsheet's new size.
Is there a way to do this through code? I have found various posts about it alluding to such a feature but no code snippets for me to learn from. My attempt to investigate the issue myself involved running my script through debug on my IDE to try and find any properties of my spreadsheet object that contained any kind of variable storing the filter views but I was unable to find anything.
Is what I am asking for possible?
I was eventually able to find out an answer to this by looking outside of the gspread library. Google sheets API v4 had the answer in the end:
from oauth2client.service_account import ServiceAccountCredentials
from googleapiclient import discovery
scope = ['https://spreadsheets.google.com/feeds']
credentials = ServiceAccountCredentials.from_json_keyfile_name('creds.json', scope)
service = discovery.build('sheets', 'v4', credentials=credentials)
update_filter_view_request = {
'updateFilterView': {
'filter': {
'filterViewId': filter_view_id,
'range': {"sheetId": '''tab id goes here''',
"startRowIndex": 0, # 0 is A
"endRowIndex": 3, # 3 is D
"startColumnIndex": 0,
"endColumnIndex": '''updated range value'''}
},
'fields': {
'paths': ['*']
}
}
}
service.spreadsheets().batchUpdate(spreadsheetId='''spreadsheet ID goes here''', body={'requests': [update_filter_view_request]}).execute()
I ended up putting parts of the above code into a method information from
resp = service.spreadsheets().get(spreadsheetId=sheet_id).execute()
to plug into a loop and get the ids of all the tabs and filterviews I was interested in updating. Hopefully this will be of use to someone because it stumped me for ages!