服务错误:电子表格(行...带有.setColumnFilterCriteria) [英] Service error: Spreadsheets(line... with .setColumnFilterCriteria

问题描述

这个问题是另一个问题的延伸. 将基本过滤器应用于电子表格列中有多个值

This question is an extension from another. Apply basic filter to multiple values in a spreadsheet column

我遇到错误，尤其是Service error: Spreadsheets (line 8, file "Filter")，其代码如下:

I am experiencing an error, specifically Service error: Spreadsheets (line 8, file "Filter") with the following code:

function testFilter() {
  var ss = SpreadsheetApp.getActive();
  var monthlyDetailSht = ss.getSheetByName("Monthly_Detail");
  var filterRange = monthlyDetailSht.getRange(2,12,359,1).getValues(); //Get L column values    
  var hidden = getHiddenValueArray2(filterRange,["Apple"]); //get values except Apple    
  var filterCriteria = SpreadsheetApp.newFilterCriteria().setHiddenValues(hidden).build();
  var rang = monthlyDetailSht.getDataRange();
  var filter = rang.getFilter() || rang.createFilter();// getFilter already available or create  a new one

  //remove filter and flush
  if(monthlyDetailSht.getFilter() != null){monthlyDetailSht.getFilter().remove();}
  SpreadsheetApp.flush();

  filter.setColumnFilterCriteria(12, filterCriteria);
};



    //flattens and strips column L values of all the values in the visible value array
    function getHiddenValueArray2(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;
    }

我使用了Logger.log(hidden)来捕获函数返回的值，并且它是所有其他水果"的列表，这些列表重复了L列中可用的次数.用于敏感数据.

I have used a Logger.log(hidden) to capture the values returned by the function and it is a list of all of the other "fruits" repeated as many times as they are available in column L. I am using fruits as a substitute for the sensitive data.

这就是我的问题.当几天正常运行时，为什么现在会收到该错误?我该如何纠正?

So here goes my question. Why am I getting that error now when it was working perfectly fine for a couple of days? How can I correct this?

尝试的修复程序:

• 我尝试将行添加到数据的末尾.没有解决.
• 我尝试删除过滤器，冲洗，设置过滤器.没有修复. (上面更新了代码，以防万一有其他人感兴趣时我进行了刷新.)

推荐答案

它现在正在工作.对于要在Google搜索中偶然发现此问题的人，我要注意几件事.首先，问题实际上是Google方面的错误.使用上面具有的相同代码，现在可以正常工作.我没有更改.

It's working now. A couple of things I want to note for people who stumble upon this with their google searches. First, the issue was in fact an error on Google's side. Using the same code I have above it now works. I did not change it.

第二，我能够通过宏记录器记录过滤，而当我的原始代码不起作用时，该代码就可以工作.这可能会帮助那些时间紧迫而迫不及待地希望Google将他们的东西汇集在一起​​的人们.我仍然不确定原始代码中具体是什么引起了该错误，但是我猜这并不重要.我已经花了一整天的时间研究这个错误，而且似乎是零星的，没有一个罪魁祸首.如果以后发生，我的问题可能与您的问题不同.

Second, I was able to record the filtering through the macro recorder and that code worked when my original code did not. This may help people who are on a time crunch and can't wait for google to get their stuff together. I'm still not sure what specifically in my original code caused the error, but my guess is that it does not matter. I've dedicated a full day to researching this error and it seems sporadic with not a single culprit. My issue may not be the same as yours if it happens in the future.

希望有帮助！

这篇关于服务错误:电子表格(行...带有.setColumnFilterCriteria)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！