在电子表格列中对多个值应用基本筛选器

Question

我正在努力通过Google Apps Script设置过滤器。从我的研究中，我得出结论，尽管.setVisibleValues()被列为可用选项，但它目前还不受支持。唯一可编程筛选列的方法是使用.setHiddenValues()。这会带来挑战，因为可能需要隐藏数百个值。

在下面的示例代码中，我选择在第12列（L）中排除值One、Two、Three、Five、Six和Seven。如果该列中只有七个值，则应返回一个仅包含"L"列中的"Four"的筛选数据集。

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

如果只能使用.setHiddenValues()，我的想法是构建一个排除某些值的项目列表。换句话说，如果L列中的值不等于“Four”，则在.setHiddenValues()列表中包含。我想这将需要一个循环，但我想知道你们的想法。我对GAS还比较新，不确定如何构建高效的循环来完成此操作。是否有更好的设置筛选器的方法？

- DanCue

Answer 1

是的，您可以使用`splice()`方法。您可以从以下内容更改此内容：

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

亲爱的：

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

另一种方法是使用filter()。这也会删除重复项：

---

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

- TheMaster

---

现在我遇到了一个错误，这个问题已经持续了好几天，之前一直正常运行。错误信息是：“服务错误：电子表格（第113行……” 我不确定该如何解决，我的研究也没有任何结果。 - DanCue

会做。关于您提供的代码，我有一个快速问题。它会从数组中删除唯一值吗？ - DanCue

我认为 'Logger.log(hidden)' 回答了我的问题。该函数不会删除重复项。(这就是我想表达的意思，不是删除唯一的值。) - DanCue

我修改了这段代码（使用.filter()版本），以便从过滤后的列中获取值数组（values()数组），但它返回未过滤的值数组。实际上，当我手动在Sheets网页GUI上创建一个列筛选器时，它会显示已筛选，但获取值（values()）数组却得到未筛选的值数组。是否有解决.values()调用不尊重过滤器的方法？ - Matthew

@Matthew 我理解你的想法。但是来回切换非常慢，除非绝对必要。请阅读最佳实践。据我所知，目前没有一种方法可以将筛选后的值传递给应用脚本进行“操作”。但是，使用应用程序脚本很容易仅复制筛选后的值到其他位置-从那里，您还可以获取值以仅获取筛选数组-这非常低效：我建议使用内置代码。 - TheMaster

Answer 2

使用I'-'I的方法可以工作，但我发现在数字列上进行过滤不起作用。解决方案在这个问题中得到了回答。

这里是Tanaike提供的解决方案。将getHiddenValueArray函数替换为以下内容：

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

我还创建了以下内容来搜索单元格中的字符串。上面的代码似乎只查找完全匹配的内容。我相信有一种方法可以将两者结合起来，也许还有一种方法可以改进以下内容，但它能够正常工作。如果有更好的方法，请随时评论。

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
}

在某些情况下，我需要精确匹配（第一种解决方案）。在其他情况下，我不需要（第二种解决方案）。

- DanCue