5

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.

Alex
  • 63
  • 1
  • 1
  • 5

6 Answers6

4

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

     }

  }

}
user1553846
  • 63
  • 1
  • 4
3

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

mhawksey
  • 2,013
  • 5
  • 23
  • 61
  • Probably I wasn't clear enough on what I am trying to do in my question. Allow me to explain further. I have a Spreadsheet that contains columns -> Department | Unit | Staff Name | Salary. A user can filter the spreadsheet by Department and Unit. I also have a custom menu called "Process". If user click on "Process", it will run a custom script function to do some processing based on the criteria filtered by the user. Is is possible to get the criteria filtered by the user in script? – Alex Jul 27 '12 at 00:55
  • As far as I'm aware there is no way to get user applied filters – mhawksey Jul 28 '12 at 07:58
  • There might be another way of doing this but it's dependant on the answer to this question. When a user filters by Department and Unit are they single options or does the user select multiple departments/units? – mhawksey Aug 07 '12 at 20:24
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);
}
Fares Droubi
  • 332
  • 3
  • 4
  • Sheets is only available for APIs (https calls) - he is asking for App Script - they are two different albeit similar, things. – amok Mar 05 '18 at 00:19
  • 1
    @amok: Sheets API is available in Apps Script you just need to activate it in resources => Advanced Google services => Google sheets API – Fares Droubi Mar 13 '18 at 11:16
0

In search for an answer to this question, I came up with the next workaround:

  • apply filters in the sheet;
  • color the filtered (and therefore visible) cells (in the example code red in column F);
  • run script:
    • reading background colors (of column F) in array colors
    • iterating this array
    • building up the new array of row numbers that are visible.

This last array can be used to further manipulations of the sheet data. To vivualize the effect of the script, I made the script setting the background of used cells to green.

It's one extra small effort for the end user to make, but IMHO it's the only way to make it possible to only use the filtered data. 

function getFilterdData(){  
   var s = SpreadsheetApp.getActive();
   var sheet= s.getSheetByName('Opdrachten en aanvragen');//any sheet
   var rows = new Array();
   var colors = sheet.getRange(1, 6, sheet.getLastRow(), 1).getBackgrounds();
   for(var i = 0; i < colors.length; i++){
       if(colors[i] == "#ff0000"){
         var rowsIndex = rows.length;
         rows[rowsIndex] = i+1;
         sheet.getRange(i+1, 6).setBackground("#d9ead3")
         }
       }
    }
user3722096
  • 74
  • 7
0

Based on mhawksey answer, I wrote the following function:

//
function celdasOcultas(ssId, rangeA1) {  
  // limit what's returned from the API
  var fields = "sheets(data(rowMetadata(hiddenByFilter)),properties/sheetId)";
  var sheets = Sheets.Spreadsheets.get(ssId, {ranges: rangeA1, fields: fields}).sheets;
  if (sheets) {
    return sheets[0].data[0].rowMetadata;
  }
}

then called it this way:

    // i.e. : ss = spreadsheet, ranges = "Hoja 2!A2:A16"
    Logger.log(range.getA1Notation());
    var ocultas = celdasOcultas(ss.getId(), sheetName + "!" + range.getA1Notation());
    // Logger.log(ocultas);
    var values = range.getValues();
    for (var r = 0; r < values.length; r++) {
      if (!ocultas[r].hiddenByFilter) {
        values[r].forEach( function col(c){
          Logger.log(c);
        });
      }
      //range.setBackground("lightcoral");
    }

Than prevent the log of the hidden rows. You can see it in action at: Prueba script, note project has to get Google Sheets API enabled on Google API Console. enter image description here

Hope it helps. Thank you!

Chesare
  • 333
  • 4
  • 8
0

This as raised in Google's Issue Tracker as Issue #36753410.

As of 2018-04-12, they have posted a solution:

Yesterday we released some new functionality that makes it possible to manipulate filters with Apps Script:

Unfortunately Apps Script still doesn't have methods for determining if a given row or column is hidden by the filter. See comment #157 for a workaround utilizing the Sheets Advanced Service.

The Filter class lets you get the FilterCriteria for each column in turn.

Engineer Toast
  • 364
  • 10
  • 21