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. 

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.


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.

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;
}