Filters

Filters allow you to sort and filter the data that you see when you view a spreadsheet. Data that matches the filter criteria you specify doesn't appear while the filter is on. With filter views, you can also save different filters and switch between them whenever you like. Filters do not change the data in your spreadsheet, so they are useful when you want to temporarily hide or sort some information.

Some example use cases for filters are listed below:

• Sort data by a particular column. For example, sort user records by last name.
• Hide data that meets a specific condition. For example, hide all records older than two years.
• Hide data that matches a certain value. For example, hide all issues with status "closed."

Basic Filter

The basic filter for a spreadsheet is a filter that is applied whenever you view the spreadsheet. You can turn the basic filter off by clearing it. This removes the filter and all its settings from the spreadsheet. If you want to turn the same filter back on, you need to set the criteria again.

Managing the basic filter

To set or clear the basic filter, use the BatchUpdate method with the appropriate request type:

• To clear the basic filter, use the ClearBasicFilterRequest.
• To set the basic filter, use the SetBasicFilterRequest.

Filter Views

Filter views are named filters that you can toggle off and on whenever you like. A spreadsheet can have any number of filter views, but you can only apply one at a time.

Some example use cases for filter views are listed below:

• You have several different filters you want to switch between when viewing the data.
• You want each person you share your spreadsheet with to view the data differently. In this case, you can provide the filter view ID in the sharing link as explained below.
• You don't have edit access to a spreadsheet but still want to apply a filter. In this case, you can create a temporary filter view which is only visible to you.

You can use the spreadsheet URL to specify the filter view you want to apply. To do so, use the filter view ID returned in the response when you create the filter view. For example, if your sheet ID is 1qpyC0XzvTcKT6EISywvqESX3A0MwQoFDE8p-Bll4hps and your filter view ID is 1961323144, the URL below links to the spreadsheet with that filter view applied.

https://docs.google.com/spreadsheets/d/1qpyC0XzvTcKT6EISywvqESX3A0MwQoFDE8p-Bll4hps/edit#gid=0&fvid=1961323144

Managing filter views

To create, modify, or delete filter views, use the BatchUpdate method with the appropriate request type:

• To create a new filter view, use the AddFilterViewRequest.
• To make a copy of an existing filter view, use the DuplicateFilterViewRequest.
• To modify an existing filter view, use the UpdateFilterViewRequest.
• To delete a filter view, use the DeleteFilterViewRequest.

Filter Representation

The JSON representation for a FilterView object is shown below. The BasicFilter is the same except that it lacks a title and ID, and it can't use a named range.

{
  "filterViewId": number,
  "title": string,
  "range": {
    object(GridRange)
  },
  "namedRangeId": string,
  "sortSpecs": [
    {
      object(SortSpec)
    }
  ],
  "criteria": {
    string: {
      object(FilterCriteria)
    },
    ...
  }
}

The rest of this document references the example sales data below:

Sort specifications

A filter can have any number of sort specifications. These specify how to sort the data and are applied in the specified order. The dimensionIndex attribute specifies the column index. Consider an example sort specification:

[
  {
    "dimensionIndex": 3,
    "sortOrder": "ASCENDING"
  },
  {
    "dimensionIndex": 6,
    "sortOrder": "ASCENDING"
  }
]

When applied to the example data, this specification sorts by quantity first and then by ship date if two rows have the same quantity.

Filter criteria

Filter criteria determine what data in the spreadsheet is hidden. Each criterion depends on the values in a specific column. Therefore, you supply the filter criteria as a map, where the keys are the column indices, and the values are the criteria.

For criteria specified using a condition, the condition must be true for a value to be shown. However, for hiddenValues, all matches for the value will be hidden.

For example, consider the filter criteria map below:

{
  0: {
    'hiddenValues': ['Panel']
  },
  6: {
    'condition': {
      'type': 'DATE_BEFORE',
      'values': {
        'userEnteredValue': '4/30/2016'
      }
    }
  }
}

When applied to the data set above, this criteria only shows rows where the item category is not "Panel" and where the ship date is before April 30, 2016.

Example

This example creates two different filter views on the sample data set above. The second is created as a duplicate of the first, then updated.

from __future__ import print_function

import google.auth
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError


def filter_views(spreadsheet_id):
    """
        Creates the batch_update the user has access to.
        Load pre-authorized user credentials from the environment.
        TODO(developer) - See https://developers.google.com/identity
        for guides on implementing OAuth2 for the application.
            """
    creds, _ = google.auth.default()
    # pylint: disable=maybe-no-member
    try:
        service = build('sheets', 'v4', credentials=creds)

        my_range = {
            'sheetId': 0,
            'startRowIndex': 0,
            'startColumnIndex': 0,
        }
        addfilterviewrequest = {
            'addFilterView': {
                'filter': {
                    'title': 'Sample Filter',
                    'range': my_range,
                    'sortSpecs': [{
                        'dimensionIndex': 3,
                        'sortOrder': 'DESCENDING'
                    }],
                    'criteria': {
                        0: {
                            'hiddenValues': ['Panel']
                        },
                        6: {
                            'condition': {
                                'type': 'DATE_BEFORE',
                                'values': {
                                    'userEnteredValue': '4/30/2016'
                                }
                            }
                        }
                    }
                }
            }
        }

        body = {'requests': [addfilterviewrequest]}
        addfilterviewresponse = service.spreadsheets() \
            .batchUpdate(spreadsheetId=spreadsheet_id, body=body).execute()

        duplicatefilterviewrequest = {
            'duplicateFilterView': {
                'filterId':
                    addfilterviewresponse['replies'][0]
                    ['addFilterView']['filter']
                    ['filterViewId']
            }
        }

        body = {'requests': [duplicatefilterviewrequest]}
        duplicatefilterviewresponse = service.spreadsheets() \
            .batchUpdate(spreadsheetId=spreadsheet_id, body=body).execute()

        updatefilterviewrequest = {
            'updateFilterView': {
                'filter': {
                    'filterViewId': duplicatefilterviewresponse['replies'][0]
                    ['duplicateFilterView']['filter']['filterViewId'],
                    'title': 'Updated Filter',
                    'criteria': {
                        0: {},
                        3: {
                            'condition': {
                                'type': 'NUMBER_GREATER',
                                'values': {
                                    'userEnteredValue': '5'
                                }
                            }
                        }
                    }
                },
                'fields': {
                    'paths': ['criteria', 'title']
                }
            }
        }

        body = {'requests': [updatefilterviewrequest]}
        updatefilterviewresponse = service.spreadsheets() \
            .batchUpdate(spreadsheetId=spreadsheet_id, body=body).execute()
        print(str(updatefilterviewresponse))
    except HttpError as error:
        print(f"An error occurred: {error}")


if __name__ == '__main__':
    # Pass: spreadsheet_id
    filter_views("1CM29gwKIzeXsAppeNwrc8lbYaVMmUclprLuLYuHog4k")