Google Script for Sheets: Set multiple filters

2019-09-20 12:18发布

问题:

I've a question around applying filters to multiple values to hide them using Google Script. I've a Project Tracker that has multiple statuses such as On Target, Completed, Canceled, Delayed, etc. I'd like to write a script that sets the filter values for the Status column such that the rows marked "Completed" or "Canceled" are hidden. I've written the code snippet below for hiding Completed status, how do I add Canceled to it? Thanks for your help.

//Below code has been written to hide rows with status = Completed. 
//I'd like to modify it to hide rows with status = Completed AND rows with status = Canceled.

function SetFilters() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var ssId = spreadsheet.getId();
var sheet = spreadsheet.getSheetByName("Project Tracker");

var lastRow = sheet.getLastRow();
var lastColumn = sheet.getLastColumn();
var sheetId = sheet.getSheetId();

var filterSettings = {
  "range": {
    "sheetId": sheetId,
    "startRowIndex": 0,
    "endRowIndex": lastRow,
    "startColumnIndex": 0,
    "endColumnIndex": lastColumn
  }
};

filterSettings.criteria = {};
var columnIndex = 2;
filterSettings['criteria'][columnIndex] = {
  'hiddenValues': ["Completed"]
};

var requests = [{
  "setBasicFilter": {
    "filter": filterSettings
  }
}];
Sheets.Spreadsheets.batchUpdate({'requests': requests}, ssId);

}

回答1:

There's no need to use Advanced Sheets services. We can use Filter class available.

function setFilters() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName("Project Tracker");
  var rang = sh.getDataRange();
  var filtercriteria = SpreadsheetApp.newFilterCriteria().setHiddenValues(["Completed","Cancelled"]).build();//used Cancelled NOT Canceled; Create criteria with two hidden values
  var filter = rang.getFilter() || rang.createFilter();// getFilter already available or create  a new one
  filter.setColumnFilterCriteria(2, filtercriteria);//set the criteria against Col2 (B column)
}