Create, update & clear filters + Get filtered rows

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#setbasicfilterrequest

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

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#clearbasicfilterrequest

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;

}