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?

TheMaster · Accepted Answer · 2018-07-26T15:15:31.077

9

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

edited Jul 26 '18 at 15:15

answered Jul 24 '18 at 13:52

TheMaster

45,448
6
62
85

This worked! I just had to change filtercriteria to criteria or vice versa to match what I had in my original code. Thanks a bunch. I accepted this as the answer! – DanCue Jul 24 '18 at 15:03
I'm getting an error now after days of this working properly. "Service error: Spreadsheets (line 113..." I'm not sure how to fix this and my research has led me nowhere. – DanCue Jul 26 '18 at 11:31
It happens at this line: `spreadsheet.getActiveSheet().getFilter().setColumnFilterCriteria(12, criteria);` – DanCue Jul 26 '18 at 11:37
Will do. quick question regarding the code you provided. Does it delete unique values from the array? – DanCue Jul 26 '18 at 12:05
I think the 'Logger.log(hidden)' answered my question. The function does not remove duplicates. (That's what I meant to say. Not delete unique values.) – DanCue Jul 26 '18 at 12:07
Here is the link to the new question as requested. [https://stackoverflow.com/questions/51538942/service-error-spreadsheetsline-with-setcolumnfiltercriteria] – DanCue Jul 26 '18 at 12:33
would it help the execution speed if the function also deleted duplicates? – DanCue Jul 26 '18 at 13:41
I'm having issues filtering on a column that has numbers. Is there a reason why? For example, I'm filtering for 6634 and even though it is on the list it will not filter for it using your solution. – DanCue Jul 31 '18 at 20:35
@ I'-'I I'm grabbing the number from a user inputted html input box. I have to figure out how I would do that. When I do don't I also have to do the same for the values stored in an array? I guess I'm confused. I think the issue is that the hidden values array IS a string and when filtering on a column with numbers it is not finding any of these so all my rows are returned. – DanCue Jul 31 '18 at 21:35
I tried that. I'll open another question, but before I do I want to make sure I fully understand what this code is doing. I grab a user defined inputted number and store it as a string. I grab a column's values and store as a string. I compare the array to the user defined number and delete the number from the array if found. I use that new array to set as hidden values. At this point are the hidden values strings? I'm assuming so. That would explain why why it is not finding the strings to exclude because the column contains numeric values. – DanCue Jul 31 '18 at 22:02
Yes. I tried that. I can't seem to figure it out on my own. Maybe it's the long shift I've had today. :) Here is the new question:[https://stackoverflow.com/questions/51623234/apply-filter-to-column-with-numeric-values] – DanCue Jul 31 '18 at 23:55
I adapted this code (the .filter() version) so I can get the values() array from the filtered column, but it returns the array of unfiltered values. Indeed when I manually create a filter on the column using the Sheets webpage GUI, it displays filtered but getting the values() array gets the array of unfiltered values. Is there a workaround for the .values() call not respecting the filter? – Matthew Nov 16 '18 at 02:24
@Matthew This code is to set the filter from unfiltered getValues() array. Why on earth would you set the filter to sheet and try to get the filtered array back from the sheet? If your intention is to get a filtered array, just ``getValues()`` and then use `filter` inside the script itself. Don't go back and forth between sheet and script. Use only script. If you're stuck with something specific in that approach, perhaps ask a different question with more details? – TheMaster Nov 16 '18 at 02:46
Can someone confirm that getValues() doesn't respect the Sheet filter set by either setHiddenValues() or by the Sheet GUI? @TheMaster: On Earth it's better to use a single code or configuration component to perform a single function that serves both the GUI and internal computation. The Sheet filter has an implicit GUI with the same function as the internal computation' requirement, so that would be better than redundant code, and the extra code to integrate the two (eg. user turns off or changes filter). – Matthew Nov 17 '18 at 14:14
@Matthew I get where you're coming from. But going back and forth is extremely slow, unless absolutely necessary. Read [Best practices](https://developers.google.com/apps-script/guides/support/best-practices#minimize_calls_to_other_services).AFAIK, at present, there isn't a way to the get the values filtered by a filter to apps script "for manipulation". However, it's [easy to copy](https://stackoverflow.com/a/52227279) the filtered values only to somewhere else using apps-script- From there, you can also getValues to only get the filteredArray-This is highly inefficient:I suggest inbuilt code – TheMaster Nov 17 '18 at 14:53
@TheMaster: Does `sourceSheet.getFilter().getRange().copyTo(targetRange, SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);` actually copy just the filtered values, not just all the values in the range? FWIW performance is not a priority in my application, but maintainability is. – Matthew Nov 18 '18 at 15:05
@Matt Yes. Why don't you try and see? – TheMaster Nov 18 '18 at 15:17
@TheMaster: It copies only the values from the filtered rows (filter on one column and copy from another, like using the Sheets GUI). But copyTo() repeats the values as many times as will fit in the target range. Eg. `sheet.getRange("A1:A4").copyTo(sheet.getRange("B1:B10"), SpreadsheetApp.CopyPasteType.PASTE_NORMAL);` results in B1:B4 and B5:B8 each containing the values from A1:A4 (B9:B10 unchanged). Since the script doesn't know how many values the filter will produce as source for copying it must target the max rows range, so unpredictable repetitions. PASTE_VALUES also repeats to fit. – Matthew Nov 19 '18 at 20:17
@Mat Notice how I used it in my linked solution above and read the `copyTo` documentation. – TheMaster Nov 19 '18 at 20:56

DanCue · Answer 2 · 2018-08-02T18:46:15.010

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

Apply basic filter to multiple values in a spreadsheet column

2 Answers2

Linked