Google Sheets Passing Range from separate sheet into custom function

Question

I have the following custom function I've built to basically reproduce a needle haystack type of functionality

function ListBy (Range, Range2, needle) {
  var holder = [];
  if(typeof Range != 'object') {
    throw "expected Range for input saw" + typeof Range;
    return false;
  } for(var i = 0; i < Range.length; i++) {
    if(Range[i] == needle) {
          holder.push(Range2[i]);
    }
  }

  return holder;
}

This script works perfectly when executed on one sheet, for instance

=ListBy(A1:A5, B1:B5, "needle");

But if I try and pass in data from another sheet like so

=ListBy('sheet1'!A1:A10, 'sheet1'!A1:A10, "needle")

I get a reference of range error. Why would this be the case?

Answer 1

You added single quotes to the sheet references:

=ListBy('sheet1'!A1:A10, 'sheet1'!A1:A10, "needle")

If you leave those:

=ListBy(Sheet1!A1:A10, Sheet1!A1:A10, "needle")

then your script works as planned.

Screenshot

Sheet2:

Sheet1: