Manage data visibility with filters

This document is about using filters to sort and filter the data shown in a spreadsheet.

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.

To filter data returned in a Google Sheets API request, use the DataFilter object. For more information, see Read, write, and search metadata.

Filter use cases

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

Basic filter

The BasicFilter object for a spreadsheet is the default filter that's applied whenever anyone views the spreadsheet. A spreadsheet can have only 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 sheet can have multiple filter views saved, but you can only apply one at a time. A sheet can also contain both a basic filter and multiple filter views, but you cannot apply both simultaneously on the same data range.

Filter view use cases

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 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 Sheets URL with a field mask:

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

JSON representation of a filter

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)
    },
    ...
  }
}

Sample sales data

The rest of this document references the following sample sales data table:

Table 1. Sample 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 sample sales data, this specification sorts by the "Quantity" column first and then, if two rows have the same quantity, by "Ship Date."

Table 2. Sales data sorted by two 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 object 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 indexes, 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 sample sales data, this criteria only shows rows where the "Item Category" column value isn't "Panel", and where the "Ship Date" column value 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

Filter view code sample

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

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")