Geek asks and answers

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

Is it possible to get filtering criteria for my data set. For example, if one of my columns is “Department”, and I filtered the data to display only “IT”. How to get filtered IT criteria. I need to get filtered criteria in my GAS to do some other manipulations.

Thank.

+5
source share
5 answers

Try this example for a simple filter. It will filter the 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);

     }

  }

}
+4
source

Google Spreadsheet FILTER ( , , ). , , :

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

,

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

( )

- Script, . 2D Array2

+3

:

, :

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);
}
+3

:

  • ;
  • (, , ) ( F);
  • script:
    • ( F)
    • .

. vivualize script, script, .

This is one extra small effort for the end user, but IMHO is the only way to use only 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")
         }
       }
    }
0
source

Based on mhawksey's answer, I wrote the following function:

//
function celdasOcultas(ssId, rangeA1) {  
  // limit what 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;
  }
}
Run codeHide result

then calls it like this:

    // 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");
    }
Run codeHide result

Than the hidden line log is blocked. You can see this in action at: Prueba script , the draft note should enable the Google Sheets API in the Google APIs console. enter image description here

Hope this helps. Thank!

0
source

More articles:


All Articles