How can I use a custom function with FILTER?
google-apps-script, google-sheets
Solution
When a custom function is called passing a multi-cell range, it receives a matrix of values (2d array), it's doesn't matter if the range is a single column or a single row, it's always a matrix. And you should return a matrix as well.
Anyway, I would not use a custom function to this, as there is already the native spreadsheet formulas: `RegexMatch`, `RegexExtract` and `RegexReplace` formulas. To get the "if match" behavior, just wrap them in a `IfError` formula.
Problem
I have a custom function defined that extracts part of an address from a string: ``` /* * Return the number preceding 'N' in an address * '445 N 400 E' => '445' * '1083 E 500 N' => '500' */ function NorthAddress(address) { if (!address) return null; else { var North = new RegExp('([0-9]+)[\\s]+N'); var match = address.match(North); if (match && match.length >= 2) { return match[1]; } return null; } } ``` I want to use this function as one of the conditions in a call to `FILTER(...)` in the spreadsheet where I have these addresses stored: ``` =FILTER('Sheet 1'!A:A, NorthAddress('Sheet 1'!B:B) >= 450)) ``` But when I call `NorthAddress` like this, it gets an array of all the values in column B and I can't for the life of me find any documentation as to how I need to handle that. The most obvious way (to me) doesn't seem to work: iterate over the array calling `NorthAddress` on each value, and return an array of the results. What does my function need to return for `FILTER` to work as expected?