Google Sheets Script: Passing function as a parameter

Question

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:

=GetLastGoodValIn1DArray(D11:H11;ISNUMBER)

I'm getting the next error:

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?

The formula is not written properly. Try =GetLastGoodValIn1DArray(D11:H11;ISNUMBER(something)) where something could be a value or a cell reference. — Wicket
– Wicket, Commented Jul 23, 2016 at 23:52

Community · Accepted Answer · 2017-05-23 11:44:21Z

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.

Collectives™ on Stack Overflow

Google Sheets Script: Passing function as a parameter

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related