Google-sheets – Filtering a range by another range, custom function just says “internal error”

google sheetsgoogle-apps-script

I'm trying to filter a range by comparing it to another range. I couldn't figure out a way to do this with functions in Google Spreadsheets so I made my own.

function FilterByArray(range, filterRange)
{
  var rangeLength = range.length;
  var filterRangeLength = filterRange.length;
  var outputArray = [];

  for(i = 0; i < rangeLength; i++)
  {
    var rangeValue = range[i];
    for(ii = 0; ii < filterRangeLength; i++)
    {
      if(rangeValue == filterRange[ii])
      {
        outputArray.push(rangeValue);
      }
    }
  }
  return outputArray;
}

At first glance I thought this would work, and should process in milliseconds with only a couple thousand total iterations. I try and test it and it says Loading... for 10-20 seconds then errors out with Internal Error executing the custom function.

Why is this, and what can I do to fix it?

Edit: A note, I come from C#, so if there are any glaring issues in this that are unrelated to my question please let me know.

Best Answer

There is a way to intersect two arrays without a script: see How to get the intersection of two sets

One issue with your code is that all ranges of more than one cells are represented as double arrays for the scripts. E.g., a row 1 2 3 is [[1,2,3]] while a column of the same elements is [[1],[2],[3]]. (On the other hand, a single-cell range is just a value.) So you are comparing objects instead of values, which probably isn't going to do what you want, regardless of size.

I use this auxiliary function to flatten double arrays, turning them into single arrays; it also correctly handles one-cell ranges.

function flatten(arg) {
  if (arg.constructor === Array) {
    return arg.reduce(function(a, b) { return a.concat(b); });
  }
  else {
    return [arg];
  }
}

With that at hand, I'd write something like this:

function FilterByArray(range, filterRange) {
  var flatRange = flatten(range);
  var flatFilter = flatten(filterRange); 
  var outputArray = [];

  for (var i = 0; i < flatRange.length; i++) {
    var value = flatRange[i];
    if (flatFilter.indexOf(value) != -1) {
      outputArray.push(value);
    } 
  }
  return outputArray;
}
Related Topic