3

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.)
DanCue
  • 619
  • 1
  • 8
  • 17
  • 1
    Did you add or delete anything in the ss between when it was working and when it gave the error? If so, Is there anything specific/ different about what was added? – TheMaster Jul 26 '18 at 13:01
  • 1
    Does removing the filter, flushing, then setting the filter help? From the rest API, it is an error to attempt to set a basic filter if one already exists, so a similar issue may be at fault with the Apps Script methods. Also, are there duplicate values in the hidden value array? – tehhowch Jul 26 '18 at 13:01
  • @I'-'I no, the code was not changed after it was working. This code was created on this file but then applied to another file. The other file began to give me the error so I referred back to this to see if the original was also giving me the error and it was. – DanCue Jul 26 '18 at 13:09
  • @tehhowch, How would I do the flushing? – DanCue Jul 26 '18 at 13:10
  • @tehhowch, I figured out how to do what you recommended. No luck. – DanCue Jul 26 '18 at 13:21
  • As @i'-'i asked, what has changed *on the sheet* since your code last worked? New values? If so, share in your question what has changed. If you remove any duplicate values from the sheet, does your function work again? – tehhowch Jul 26 '18 at 13:26
  • Directly set `var hidden = ["Orange","Kiwi"]` Does it work now?. I get the same error now.... Maybe they're working on visibleValues()? – TheMaster Jul 26 '18 at 13:27
  • @tehhowch Ah. I read that wrong. When you say SS does that refer to the the active sheet when the code is run? If so, I run the code from the script editor manually while the active sheet has been different. If you are referring to the sheet "Monthly_Detail" then nothing has changed with the columns. Every morning though the data is replaced with an updated set of data. That is done with a trigger. I don't think that impacts anything though as the code was working yesterday afternoon and then not working yesterday evening, before the dataset was replaced. – DanCue Jul 26 '18 at 13:31
  • @Danilo Try after sometime. Probably a actual service error. I can't even directly `createFilter()` – TheMaster Jul 26 '18 at 13:36
  • @I'-'I `var hidden = ["Orange","Kiwi"]` did not work. – DanCue Jul 26 '18 at 13:38
  • @I'-'I Ok. Seems strange. I appreciate it though. Let me try again after a day. – DanCue Jul 26 '18 at 13:39
  • Just wanted to say thanks to you both for your help. Things are back up and running perfectly. – DanCue Jul 26 '18 at 20:28
  • Btw,After you remove the filter and flush(),You need to create the filter again and then apply the criteria to the new filter created. – TheMaster Jul 28 '18 at 15:19
  • This portion of the code addresses the filtering again. Do you mean something else? `var rang = monthlyDetailSht.getDataRange(); var filter = rang.getFilter() || rang.createFilter();// getFilter already available or create a new one` – DanCue Jul 30 '18 at 11:47

1 Answers1

5

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!

DanCue
  • 619
  • 1
  • 8
  • 17