Search code examples
javascriptgoogle-apps-scriptgoogle-sheetsmenuitemadd-on

Set anonymous/dynamic functions to Menu

I would like to set dynamic functions for dynamic menus in Google sheets add-on. I am using the following code:

function onOpen(e) {
  var menu = SpreadsheetApp.getUi().createAddonMenu();
  
  for (var i = 0; i < array.length; i++) {
        const element = array[i];
        var functionName = "_" + element.name;
        var args = element.args;
        
        this[functionName] = dynamicItem(args); //didn't work
        //this[functionName] = function () {myopen(args);} //didn't work
        //eval("function " + functionName + "() { myopen('" + args + "') }"); //didn't work
        menu.addItem(element.name, functionName);
      }
   menu.addToUi();
 }

 function dynamicItem(args) {
    return function () {
       myopen(args);
    };
 }

When I click on the menu item, I get the following exception:

"Script function not found: function-name"

I got help from Anonymous function, Dynamic menus and Dynamically Updating Custom Menu, but I don't know why it's not working for me.

Any help would be highly appreciated.

Thanks.

Solution

  • Modification points:

    • In your script, when the Spreadsheet is opened, onOpen(e) is run only one time. By this, when the menu is selected, the functions are not installed. I think that this is the reason of your issue.
    • In order to run the dynamically installed functions, it is required to run the script for creating the menu every time. It seems that this is due to the current specification.

    When above points are reflected to your script, it becomes as follows.

    Modified script:

    function installFunctions() {
  
  // Samples
  var array = [{name: "sample1", args: "sample1"}, {name: "sample2", args: "sample2"}, {name: "sample3", args: "sample3"}];
  
  var menu = SpreadsheetApp.getUi().createMenu("sample");
  for (var i = 0; i < array.length; i++) {
    const element = array[i];
    var functionName = "_" + element.name;
    var args = element.args;
    this[functionName] = dynamicItem(args);
    menu.addItem(element.name, functionName);
  }
  menu.addToUi();
}

installFunctions(); // This function is run when the Spreadsheet is opened and each menu is selected.

function onOpen() {}

function dynamicItem(args) {
  return function () {
    Browser.msgBox(args);  // Sample script.
    // myopen(args);
  };
}
    • In this modified script, when the Spreadsheet is opened, the custom menu is created. And, when the menu is selected, the dynamically installed function is run.
    • This script runs the line of installFunctions(); every run of the functions. By this, the installed functions can be run. This is the important for creting the dynamically installed functions.

    References: