Google App Script dynamically defined custom functions

Question

How can I dynamically define custom function for spreadsheet from onOpen() function code?

• write a code...

function onOpen() {
  //var s = SoapService.wsdl("http://example.com/service.wsdl", "serv");
  //var funcs = s.getServerFunctions();
  var funcs = { "pow2": "function (v) { return v*v};" }
  for(var f in funcs)
  {
     this[f] = eval(funcs[f]) // define server functions as custom google-script functions for spreadsheet using this[function_name] = eval(function_code)
  }
}

• try to call =pow2() ("pow2" is a function name) from any cell
• get an error "#NAME" - undefined function

Ossiferous

Answer 1

The GAS does not support dynamic functions calls from spreadsheets. As I wrote in my comment to the @Srik answer, a solution is to use a "static" dispatcher function which has 1st parameter the dynamic function name and starting from 2nd parameter are parameters of the dynamic function. In spreadsheet it will look like =callFunction("pow2", 3) or =callFunction("mul", 3, 1).

But there is another problem. It seems that the GAS internals instance the script every call of the script functions, which means, that dynamic function created in the onOpen function will be not visible in the other functions. The following code demonstrates it. A cell containing the =pow2static(3) contains the error: ReferenceError: "pow2" is not defined. (line XX) error text.

A workaround is to download dynamic functions source code in the onOpen function, store it in an intermediate storage - either Cache or ScriptDB or better a combination Cache + ScriptDB, search the code by the name in the intermediate storage and execute it inside of the "static" dispatch function.

function onOpen() {
  var funcs = { "pow2": "function (v) { return v*v};" }
  for(var f in funcs) {
    this[f] = eval(funcs[f]);
  }
}

function pow2static(val) {
  return pow2(val);
}

Indocile

Thank you for reply! Looks like you are right... and this is sad. Don't you know - is there any ErrorHandler which could catch and override "undefined functions" call? – Ossiferous

Yes, an instance, containing the ReferenceError: "myFunction" is not defined text, of the standard ReferenceError class is thrown. – Indocile

Answer 2

You don't require the onOpen at all. Just write your pow2() function and call it from any spreadsheet as

=pow2()

The formulas will anyway be recalculated each time you reopen your spreadsheet. Your pow2 function can look something like

function pow2(val){
  var ret = parseInt(val) * parseInt(val); 
  return ret;
}

Oeflein

The author's idea is "hidden" in the commented lines - //var s = SoapService.wsdl("http://example.com/service.wsdl", "serv"); //var funcs = s.getServerFunctions();, i.e. he wants to donwload formulas dynamically using the SOAP protocol. IMHO a solution is to use a "static" function which has 1st parameter the dynamic function name and starting from 2nd parameter are parameters of the dynamic function. In spreadsheet it will look like =callFunction("pow2", 3) or =callFunction("mul", 3, 1) – Indocile