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:

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:

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:

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 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.

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

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.

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

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.

Python

sheets/snippets/spreadsheet_snippets.py
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()

Envoyer des commentaires concernant…

Besoin d'aide ? Consultez notre page d'assistance.