Google Sheets Filter View

Posted : admin On 10.08.2019

LEARN BY EXAMPLE‎ > ‎Google Sheets API‎ > ‎

Create, update & clear filters + Get filtered rows

Here are a few code samples to help you play with filters in Google Sheets via Apps Script and the Advanced Sheets Service.

You can also read this article published on the official G Suite developer blog to see how some Add-ons use filtering: 'Using Google Sheets filters in Add-ons with Google Apps Script'.
If you need additional help you can post comments on the official tracker.

Contents

Create a new filter

This example is based on the following spreadsheet and applies a filter with minimal parameters (the range on which to apply the filter, and the values to hide in a specific column) to only display the number of entries / people in Spain.

var ss = SpreadsheetApp.getActiveSpreadsheet();

var filterSettings = {};

// The range of data on which you want to apply the filter.

// optional arguments: startRowIndex, startColumnIndex, endRowIndex, endColumnIndex

sheetId: ss.getActiveSheet().getSheetId()

// https://developers.google.com/sheets/api/reference/rest/v4/FilterCriteria

var columnIndex = 2;

'hiddenValues': ['England', 'France']

'setBasicFilter': {

}

Sheets.Spreadsheets.batchUpdate({'requests': [request]}, ss.getId());

Reset all existing filters on a given range

(filter still exists but there are no rows hidden)

var ss = SpreadsheetApp.getActiveSpreadsheet();

var dataSheet = ss.getActiveSheet();

var lastColumn = dataSheet.getLastColumn();

'range': {

'startRowIndex': 0,

'startColumnIndex': 0,

}

var requests = [{

'filter': filterSettings

}];

Sheets.Spreadsheets.batchUpdate({'requests': requests}, ssId);

Clear / remove all filters

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheetId = ss.getActiveSheet().getSheetId();

'clearBasicFilter': {

}

Sheets.Spreadsheets.batchUpdate({'requests': requests}, ssId);

Get filtered rows

The snippet below will return the indexes of the filtered rows in a given Sheet. Note that it is also possible to retrieve the list of rows hidden manually, using the 'hide row' menu item in Google Sheets, as indicated in the API documentation. In the code sample here, we’re only exposing rows hidden by filter.

function getIndexesOfFilteredRows(ssId, sheetId) {

var fields = 'sheets(data(rowMetadata(hiddenByFilter)),properties/sheetId)';

var sheets = Sheets.Spreadsheets.get(ssId, {fields: fields}).sheets;

for (var i = 0; i < sheets.length; i++) {

var data = sheets[i].data;

for (var j = 0; j < rows.length; j++) {

}

Get hidden columns

The snippet below will return the indexes of the hidden columns in a given Sheet.

function getIndexesOfHiddenColumns(ssId, sheetId) {

var fields = 'sheets(data(columnMetadata(hiddenByUser)),properties/sheetId)';

var sheets = Sheets.Spreadsheets.get(ssId, {fields: fields}).sheets;

for (var i = 0; i < sheets.length; i++) {

var data = sheets[i].data;

for (var j = 0; j < columns.length; j++) {

if (columns[j].hiddenByUser) hiddenColumns.push(j);

}

return hiddenColumns;

Google Sheets Filter View Android
Google Sheets Multiple Filter Views

filter views

In Google sheets, one option for viewing a subset of data is to use Filter views (Data > Filter views). This is the simplest way to make a reusable filtered/sorted view of your data.

In effect it sits on top of your data set and any sort or filter you apply does not affect what others will see in the source data. The colour scheme around the sheet changes to indicate you are in filter view.

Filters views can also be named and saved, and will be accessible to others with whom the file is shared. Any viewer can set up a filter view, but you need edit permissions to save it.

Example Filter views are included in the data functions sample file (see Data > Filer Views...).

There's a video clip below that shows how they work:

An example of Google Sheets Filter View

Google Sheets Filter Views

Google Sheets Filter View Android

In Google sheets, one option for viewing a subset of data is to use Filter views (Data Filter views). This is the simplest way to make a reusable filtered/sorted view of your data. In effect it sits on top of your data set and any sort or filter you apply does not affect what others will see in the source data.

Google Sheets Multiple Filter Views

If you’ve read my getting-started article on the Filter function in Google Sheets, you’ll know that it’s a very powerful function when working with data in Google Sheets. In this post, I want to share a few more advanced filter options, such as working with dates and using OR logic.
Filter your data. To filter your data: On your computer, open a spreadsheet in Google Sheets. Select a range of cells. Click Data Create a filter. To see filter options, go to the top of the range and click Filter. Filter by condition: Choose from a list of conditions or write your own.