-1

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

}

TheMaster
  • 45,448
  • 6
  • 62
  • 85
cadbury4all
  • 9
  • 2
  • 4
  • Have you looked at the documentation for [`FilterCriteria`](https://developers.google.com/sheets/api/reference/rest/v4/FilterCriteria)? A quick read of it gives a great idea of what to try, so ... what did you try? – tehhowch Jul 20 '18 at 19:24
  • 1
    @tehhowch. Being a newbie to scripts, how do I modify the existing string of hidden values to include "Canceled" status? I did check Filter criteria but wasn't able to figure it out. I tried using "hiddenValues": ["Completed", "Canceled"] but that doesn't seem to work. Also, wouldn't it be better to ask the question on what has been attempted before downvoting the question? – cadbury4all Jul 20 '18 at 19:45
  • 1
    You can always approach this "how do i" from the opposite end - set the filter in the UI, then use the API to request the existing filter data. You can then easily compare your input syntax, with what the syntax should be. – tehhowch Jul 20 '18 at 19:47
  • Please keep in mind that I'm very new to Google Apps script. I'm on the business project management team and have created this tracker to help our product managers report their status. I'd appreciate a direct answer to my question or else I'll wait for someone else to answer the question. I'm not sure how to do any of the things that you've recommended. – cadbury4all Jul 20 '18 at 19:52
  • Use the Google APIs Explorer. For example, you query spreadsheet metadata such as `basicFilter` using [`spreadsheets.get`](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/get), which has this [API Explorer](https://developers.google.com/apis-explorer/#p/sheets/v4/sheets.spreadsheets.get?fields=sheets%252FbasicFilter) page. It has an interactive partial response editor, hover text, etc. `hiddenValues: [ "A", "B", "C", ... "stuff" ]` is the correct initialization of a `FilterCriteria` for multiple hidden values. PS: "It doesn't work" is not a valid problem description. – tehhowch Jul 20 '18 at 22:31

1 Answers1

5

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)
}
TheMaster
  • 45,448
  • 6
  • 62
  • 85