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 set the basic filter, use the
SetBasicFilterRequestmethod. - To clear the basic filter, use the
ClearBasicFilterRequestmethod.
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
spreadsheetIdandfilterViewIdin the spreadsheet URL. To do so, use thefilterViewIdreturned 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 create a filter view, use the
AddFilterViewRequestmethod. - To make a copy of a filter view, use the
DuplicateFilterViewRequestmethod. - To modify the properties of a filter view, use the
UpdateFilterViewRequestmethod. - To delete a filter view, use the
DeleteFilterViewRequestmethod.
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"
:
nu
mber
,
"title"
:
s
tr
i
n
g
,
"range"
:
{
objec
t
(GridRa
n
ge)
},
"namedRangeId"
:
s
tr
i
n
g
,
"sortSpecs"
:
[
{
objec
t
(Sor
t
Spec)
}
],
"criteria"
:
{
s
tr
i
n
g
:
{
objec
t
(Fil
ter
Cri
ter
ia)
},
...
}
}
Sample sales data
The rest of this document references the following sample sales data table:
| 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."
| 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
:
{
'hidde
n
Values'
:
[
'Pa
nel
'
]
},
6
:
{
'co
n
di
t
io
n
'
:
{
'
t
ype'
:
'DATE_BEFORE'
,
'values'
:
{
'userE
ntere
dValue'
:
'
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".
| 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 .
