CODEWITHSUNDEEP
javascriptfunctiongoogle-apps-scriptgoogle-sheets
kelin
kelin

Reputation: 11974

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:

=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?

Upvotes: 2

Views: 2154

Answers (1)

Joshua Dawson
Joshua Dawson

Reputation: 629

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.

Upvotes: 2

Related Questions