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.
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.
https://developers.google.com/sheets/api/reference/rest/v4/FilterCriteria
function setFilter() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var filterSettings = {};
// The range of data on which you want to apply the filter.
// optional arguments: startRowIndex, startColumnIndex, endRowIndex, endColumnIndex
filterSettings.range = {
sheetId: ss.getActiveSheet().getSheetId()
};
// Criteria for showing/hiding rows in a filter
// https://developers.google.com/sheets/api/reference/rest/v4/FilterCriteria
filterSettings.criteria = {};
var columnIndex = 2;
filterSettings['criteria'][columnIndex] = {
'hiddenValues': ["England", "France"]
};
var request = {
"setBasicFilter": {
"filter": filterSettings
}
};
Sheets.Spreadsheets.batchUpdate({'requests': [request]}, ss.getId());
}
Reset all existing filters on a given range
(filter still exists but there are no rows hidden)
function resetFilter() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var ssId = ss.getId();
var dataSheet = ss.getActiveSheet();
var lastRow = dataSheet.getLastRow();
var lastColumn = dataSheet.getLastColumn();
var sheetId = dataSheet.getSheetId();
var filterSettings = {
"range": {
"sheetId": sheetId,
"startRowIndex": 0,
"endRowIndex": lastRow,
"startColumnIndex": 0,
"endColumnIndex": lastColumn
}
};
var requests = [{
"setBasicFilter": {
"filter": filterSettings
}
}];
Sheets.Spreadsheets.batchUpdate({'requests': requests}, ssId);
}
Clear / remove all filters
function clearFilter() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var ssId = ss.getId();
var sheetId = ss.getActiveSheet().getSheetId();
var requests = [{
"clearBasicFilter": {
"sheetId": sheetId
}
}];
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.
https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets#DimensionProperties
function getIndexesOfFilteredRows(ssId, sheetId) {
var hiddenRows = [];
// limit what's returned from the API
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++) {
if (sheets[i].properties.sheetId == sheetId) {
var data = sheets[i].data;
var rows = data[0].rowMetadata;
for (var j = 0; j < rows.length; j++) {
if (rows[j].hiddenByFilter) hiddenRows.push(j);
}
}
}
return hiddenRows;
}
Get hidden columns
The snippet below will return the indexes of the hidden columns in a given Sheet.
function getIndexesOfHiddenColumns(ssId, sheetId) {
var hiddenColumns = [];
// limit what's returned from the API
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++) {
if (sheets[i].properties.sheetId == sheetId) {
var data = sheets[i].data;
var columns = data[0].columnMetadata;
for (var j = 0; j < columns.length; j++) {
if (columns[j].hiddenByUser) hiddenColumns.push(j);
}
}
}
return hiddenColumns;
}