Google App Script dynamically defined user-defined functions

Question

How can I dynamically define a user-defined function for a table from onOpen() function code?

• write 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 calling =pow2() ("pow2" is the name of the function) from any cell
• get error message "#NAME" - undefined function

Answer 1

GAS does not support dynamic function calls from spreadsheets. As I wrote in my comment on @Srik's answer, the solution is to use a “static” dispatch function that has the 1st parameter, the name of the dynamic function and, starting from the second parameter, are the parameters of the dynamic function. In the table, it will look like =callFunction("pow2", 3) or =callFunction("mul", 3, 1) .

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

The workaround is to load the source code of the dynamic functions into the onOpen function, save it in the intermediate storage - Cache or ScriptDB or better the combination Cache + ScriptDB , search for the code by name in the intermediate storage and execute it inside the "static" send 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); } 

Answer 2

You do not require onOpen at all. Just write your pow2 () function and call it from any table in the form

 =pow2() 

In any case, the formulas will be recalculated each time you reopen your spreadsheet. Your pow2 function might look something like this:

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