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 set the basic filter, use the
SetBasicFilterRequest
method. - To clear the basic filter, use the
ClearBasicFilterRequest
method.
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
andfilterViewId
in the spreadsheet URL. To do so, use thefilterViewId
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 create a filter view, use the
AddFilterViewRequest
method. - To make a copy of a filter view, use the
DuplicateFilterViewRequest
method. - To modify the properties of a filter view, use the
UpdateFilterViewRequest
method. - To delete a filter view, use the
DeleteFilterViewRequest
method.
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:
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."
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.
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.