kelin kelin - 1 year ago 169
Javascript Question

Google Sheets Script: Passing function as a parameter

I've made a script in Google Sheets:

* @param {array} input A row or a column.
* @param {function} condition A function returning bool.
* @return The last value in the input satisfying the condition.
* @customfunction
function GetLastGoodValIn1DArray(input, condition) {
// realization ...

When I'm trying to call this function with the next arguments:


I'm getting the next error:


TypeError: #NAME? is not a function, but a string. (line 26).

Obviously 'ISNUMBER' interpreted as a string. But how to pass it as a function?

Answer Source

It's not is possible to access internal google sheets functions from a script according to this old google docs forum and this Stack Overflow question.

Note that even google sheets internal functions can't take functions as parameters. For example, the internal function SUBTOTAL, instead of taking the function directly, takes in a numerical code that corresponds to the desired function.

The best I can think of is to build your own WorksheetFunction object like excel has for vba. That could be an object map that takes in an string and returns a function you wrote. For example:

var worksheetFunction = {
  isnumber: function (x) {
    return !isNaN(x);

Another Note: ISNUMBER isn't passed as a string to your custom function. Google sheets first calculates it as an error and then passes the string #NAME? to your custom function. You can see this by using the formula =type(ISNUMBER), which returns 16 (the code for an error) also in your custom function condition === "#NAME?" will evaluate to true.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download