Apply basic filter to multiple values in a spreads

问题:

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?

回答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;
}

回答2:

Using I'-'I's method worked but I found that filtering on numeric columns did not work. The solution was answered on this question.

Here is the solution provided by Tanaike. Replace the getHiddenValueArray function with this:

function getHiddenValueArray(colValueArr,visibleValueArr){
  var flatUniqArr = colValueArr.map(function(e){return e[0];})
  .filter(function(e,i,a){return (a.indexOf(e) == i && visibleValueArr.indexOf(e) == -1); })  //Handles numeric and string values.
  return flatUniqArr;
}

I have also created the following to search for a string within a cell. The above code seems to look for exact matches. I'm sure there is a way to combine the two and there may be a way to improve the following, but it works. Feel free to comment if there is a better way.

function getHiddenValueArrayStringSearch(colValueArr,visibleValueStr){
  var newArray= []
  for (var i = 0; i < colValueArr.length; i++) {
      if(colValueArr[i].toString().toLowerCase().indexOf(visibleValueStr.toString().toLowerCase()) == -1){newArray.push(colValueArr[i]);}    
  }
  return newArray
}

In some cases I want an exact match (first solution). Others I don't (second solution).