Apply basic filter to multiple values in a spreadsheet column

Question

I am working on getting filters set through Google Apps Script. From my research I have come to the conclusion that although .setVisibleValues() is listed as available, it is not yet supported. The only way to programmatically filter a column would be to use .setHiddenValues(). This presents a challenge because there can be hundreds of values that will need to be hidden.

In the example code below I have chosen to exclude values One, Two, Three, Five, Six, and Seven in column 12 (L). If there are only seven values in that column, this should return a filtered data set with only "Four" in column L.

function testFilter() {
  var spreadsheet = SpreadsheetApp.getActive();
  var criteria = SpreadsheetApp.newFilterCriteria()
    .setHiddenValues(['One', 'Two', 'Three', 'Five', 'Six', 'Seven'])
    .build();
  spreadsheet.getActiveSheet().getFilter().setColumnFilterCriteria(12, criteria);
};

If using .setHiddenValues() is the only way, my thought was to build a list of items to exclude that do not include a certain value or values. In other words, if the values in column L do not equal 'Four' include in the list of .setHiddenValues(). I imagine this will require a loop but I wanted to see what the thoughts were. I am fairly new to GAS so I am not sure how to build an efficient loop that will accomplish this. Is there a better way to set filters?

Answer 1

Yes. You can use splice()method. You can change this from:

var criteria = SpreadsheetApp.newFilterCriteria()
  .setHiddenValues(['One', 'Two', 'Three', 'Five', 'Six', 'Seven'])
  .build();

to:

  var sh = spreadsheet.getActiveSheet();    
  var filterRange = sh.getRange('L1:L'+sh.getLastRow()).getValues(); //Get L column values    
  var hidden = getHiddenValueArray(filterRange,["four"]); //get values except four    
  var filtercriteria = SpreadsheetApp.newFilterCriteria().setHiddenValues(hidden).build();



//flattens and strips column L values of all the values in the visible value array
function getHiddenValueArray(colValueArr,visibleValueArr){
  var flatArr = colValueArr.map(function(e){return e[0];}); //Flatten column L
  visibleValueArr.forEach(function(e){ //For each value in visible array    
    var i = flatArr.indexOf(e.toString()); 
    while (i != -1){ //if flatArray has the visible value        
      flatArr.splice(i,1); //splice(delete) it
      i = flatArr.indexOf(e.toString());
    }
  });
  return flatArr;
}

---

Another method is to use filter(). This will also remove duplicates:

function getHiddenValueArray(colValueArr,visibleValueArr){
  var flatUniqArr = colValueArr.map(function(e){return e[0];})
  .filter(function(e,i,a){return (a.indexOf(e.toString())==i && visibleValueArr.indexOf(e.toString()) ==-1); })
  return flatUniqArr;
}