Google App Script / Spreadsheet - How to get filter criteria?

Question

Is it possible to get the filter criteria of my data set. For example, if one of my column is "Department" and I filtered the data to display only "IT". How do we get the filtered criteria, "IT". I need to get that filtered criteria into my GAS to do some other manipulation.

Thanks.

Answer 1

Try this example for a simple filter. It will filter information (change XXX to something meaningful) from the first column:

  function onOpen(){

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var filterMenuEntries = [{name: "filter Column1", functionName: "filter"}];
  ss.addMenu("choose filter", filterMenuEntries);

  }

 function filter(){

 var sheet = SpreadsheetApp.getActiveSheet();
 var rows = sheet.getDataRange();
 var numRows = rows.getNumRows();
 var values = rows.getValues();

 for (var i=1; i <=numRows -1; i++){

   var row =values[i];

   // Column value
   var myValue = row[0];

   // filter value 
   if (myValue == "XXX"){

     sheet.hideRows(i+1);

     }

  }

}

Answer 2

Google Spreadsheet already has a FILTER formula (I always use this page to remind me how to do it). So for example if your data looked like this

  A
1 Department
2 IT Department (Edinburgh)
3 Department of IT
4 Other Department

to get a filtered list you could use the formula

=FILTER(A:A;FIND("IT",A:A)>0)

(Working example here)

If you want to do something entirely in Apps Script Romain Vialard has written a Managed Library with a filter function. Here are instructions for installing and using the 2D Array2 library

Answer 3

Filters are now available using with the recent launch of the Advanced Sheets Service: Here is a link to the article

Here is a little snippet of how to do :

function setSheetBasicFilter(ssId, BasicFilterSettings) {
  //requests is an array of batchrequests, here we only use setBasicFilter
  var requests = [
    {
      "setBasicFilter": {
        "filter": BasicFilterSettings
      }
    }
  ];
  Sheets.Spreadsheets.batchUpdate({'requests': requests}, ssId);
}