Filters

Filters allow you to sort and filter the data that you see when you view a spreadsheet. Filters don't change the data values in your spreadsheet. You can use filters to temporarily hide or sort information. Data that matches the specified filter criteria doesn't appear while the filter is on. With filter views, you can also save different named filters and switch between them whenever you like.

The following are some example use cases for filters:

  • 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 2 years.
  • Hide data that matches a certain value. For example, hide all issues with status "closed."

Basic filter

The BasicFilter for a spreadsheet is the default filter that's applied whenever anyone views the spreadsheet. A spreadsheet can have one basic filter per sheet. You can turn off the basic filter 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 must set the criteria again.

Manage the basic filter

To set or clear the basic filter, use the spreadsheets.batchUpdate method with the appropriate request type:

To list the basic filter, use the spreadsheets.get method and set the fields URL parameter to sheets/basicFilter. The following spreadsheets.get code sample shows a Google Sheets URL with a field mask:

GET https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID?fields=sheets/basicFilter)

Filter views

A FilterView is a named filter that you can turn off and on whenever you like. A spreadsheet can have multiple filter views, but you can only apply one at a time.

The following are some example use cases for filter views:

  • You have several different filters that you want to switch between when viewing the data.
  • You don't have edit access to a spreadsheet but you still want to apply a filter. In this case, you can create a temporary filter view that's only visible to you.

  • You want each person that you share your spreadsheet with to view the data differently. You can specify the filter view you want to apply by providing the spreadsheetId and filterViewId in the spreadsheet URL. To do so, use the filterViewId returned in the response when you create the filter view.

    The following code sample shows a Google Sheets URL with a filter view:

    https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/edit#gid=0&fvid=FILTER_VIEW_ID

Manage filter views

To create, duplicate, modify, or delete filter views, use the spreadsheets.batchUpdate method with the appropriate request type:

To list all your filter views, use the spreadsheets.get method and set the fields URL parameter to sheets/filterViews. The following spreadsheets.get code sample shows a Google Sheets URL with a field mask:

GET https://sheets.googleapis.com/v4/spreadsheets/SPREADSHEET_ID?fields=sheets/filterViews)

Filter representation

The following code sample shows the JSON representation for a FilterView object. The BasicFilter object is the same except that it lacks the filterViewId and title fields, and it can't use a named range.

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

Example data

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

Table 1. Example sales data
A B C D E F G
1 Item Category Model Number Cost Quantity Region Salesperson Ship Date
2 Wheel W-24 $20.50 4 West Beth 3/1/2016
3 Door D-01X $15.00 2 South Amir 3/15/2016
4 Frame FR-0B1 $34.00 8 East Hannah 3/12/2016
5 Panel P-034 $6.00 4 North Devyn 3/15/2016
6 Panel P-052 $11.50 7 East Erik 5/16/2016
7 Wheel W-24 $20.50 11 South Sheldon 4/30/2016
8 Engine ENG-0161 $330.00 2 North Jessie 7/2/2016

Sort specifications

A filter can have multiple sort specifications. These specifications determine how to sort the data and are applied in the specified order. The SortSpec.dimensionIndex attribute specifies the column index that the sort should be applied to.

The following code sample shows a sort specification:

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

When applied to the example sales data, this specification sorts by "Quantity" first and then, if 2 rows have the same quantity, by "Ship Date."

Table 2. Sales data sorted by 2 columns
A B C D E F G
1 Item Category Model Number Cost Quantity Region Salesperson Ship Date
2 Door D-01X $15.00 2 South Amir 3/15/2016
3 Engine ENG-0161 $330.00 2 North Jessie 7/2/2016
4 Wheel W-24 $20.50 4 West Beth 3/1/2016
5 Panel P-034 $6.00 4 North Devyn 3/15/2016
6 Panel P-052 $11.50 7 East Erik 5/16/2016
7 Frame FR-0B1 $34.00 8 East Hannah 3/12/2016
8 Wheel W-24 $20.50 11 South Sheldon 4/30/2016

Filter criteria

The FilterCriteria method determines what spreadsheet data is shown or hidden in a basic filter or filter view. Each criterion depends on the values in a specific column. 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 boolean condition, the condition must be True for values to be shown. The condition doesn't override hiddenValues. If a value is listed under hiddenValues, all matches for a value are still hidden.

The following code sample shows a filter criteria map:

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

When applied to the example sales data, this criteria only shows rows where the "Item Category" is not "Panel", and where the "Ship Date" is before April 30, 2016.

Table 3. Sales data using filter criteria
A B C D E F G
1 Item Category Model Number Cost Quantity Region Salesperson Ship Date
2 Wheel W-24 $20.50 4 West Beth 3/1/2016
3 Door D-01X $15.00 2 South Amir 3/15/2016
4 Frame FR-0B1 $34.00 8 East Hannah 3/12/2016

Sample

The following code sample shows how to create a filter view, duplicate it, and then update the duplicated version using the example sales data above.

Python

sheets/snippets/sheets_filter_views.py
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")