Creating a Basic Filter

Question

In pygsheets, is it possible to turn filtering on and off? I am able to get a dataframe into a sheet, but I cannot figure out how to turn on a filter without manually going to the sheet itself.

My dataframe will be changing size and the default filter is not smart enough to grow with the data, thus requiring me to turn the filter off and back on again.

Thanks for your help!

Is this a `pygsheets` question? Or is it a `gspread` question? Or is it a `Sheets REST API` question? — tehhowch, Jul 16 '18 at 15:14
It's a pysheets question. I'd seen another question tag other Google Sheet related topics, and in case pygsheets isn't a viable solution and REST is required. I can remove those tags though if it's confusing. — alpacafondue, Jul 16 '18 at 16:07

tehhowch · Answer 1 · 2018-07-18T03:50:49.097

In the Sheets REST API, removing and/or restoring the filter is fairly simple to do - there are two Requests that can be supplied in calls to spreadsheets#batchUpdate:

clearBasicFilter Include the sheet ID on which the filter is to be removed, e.g.
{clearBasicFilter: {sheetId: "someWorkSheetGridId"}}
setBasicFilter

A simple way to avoid introducing errors in your criteria specification is to first query for the existing filter, and use it in the following setBasicFilter request. If you don't, then you need to re-specify the filter criteria (if restoring an active filter). To simply restore the ability for the UI to filter, you do not need to supply the criteria property.

To query existing basic filter data, use spreadsheets#get, and the appropriate fields parameter to limit the response to only the needed information: fields: "sheets(properties(sheetId,title),basicFilter)".

Using the Google API Python Client (and ignoring any responses, error handling, etc):

def get_existing_basic_filters(wkbkId: str) -> dict:
    params = {'spreadsheetId': wkbkId,
              'fields': 'sheets(properties(sheetId,title),basicFilter)'}
    response = service.spreadsheets().get(**params).execute()
    # Create a sheetId-indexed dict from the result
    filters = {}
    for sheet in response['sheets']:
        if 'basicFilter' in sheet:
            filters[sheet['properties']['sheetId']] = sheet['basicFilter']
    return filters

def clear_filters(wkbkId: str, known_filters: dict):
    requests = []
    for sheetId, filter in known_filters.items():
        requests.append({'clearBasicFilter': {'sheetId': sheetId}})
    if not requests:
        return
    params = {'spreadsheetId': wkbkId,
              'body': {'requests': requests}}
    service.spreadsheets().batchUpdate(**params).execute()

def apply_filters(wkbkId: str, filters: dict):
    # All requests are validated before any are applied, so bundling the set and clear filter
    # operations in the same request would fail: only 1 basic filter can exist at a time.
    clear_filters(wkbkId, filters)

    requests = []
    for sheetId, filter in filters.items():
        # By removing the starting and ending indices from the 'range' property,
        # we ensure the basicFilter will apply to the entire sheet bounds. If one knows the 
        # desired values for startColumnIndex, startRowIndex, endRowIndex, endColumnIndex,
        # then they can be used to create a range-specific basic filter.
        # The 'range' property is a `GridRange`: 
        filter['range'] = {'sheetId': sheetId}
        requests.append({'setBasicFilter': {'filter': filter}})
    if not requests:
        return
    params = {'spreadsheetId': wkbkId,
              'body': {'requests': requests}}
    service.spreadsheets().batchUpdate(**params).execute()

service = get_authed_sheets_service_somehow()
fileId = "some valid Sheets file id"
my_filters = get_existing_basic_filters(fileId)
pprint(my_filters)
###
# do stuff to `my_filters`
###
apply_filters(fileId, my_filters)

If there were an updateBasicFilter request, you could avoid this procedure and simply modify the range of the existing BasicFilter with the new size of your dataframe, but there is no such request. You can likely determine the new range indices that are appropriate for the filter GridRange from the size of your dataframe.

You may consider trying out Filter Views to see if they offer anything useful for your situation.

Resources:

Creating a Basic Filter

1 Answers1