Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.2k views
in Technique[技术] by (71.8m points)

google apps script - Service error: Spreadsheets(line... with .setColumnFilterCriteria

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

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

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?

Attempted fixes:

  • I've tried to add rows to the end of my data. Did not fix.
  • I tried removing filter, flushing, setting filter. Did not fix. (updated code above with what I did to flush in case anyone else is interested.)
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.

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.

Hope that helps!


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...