Google Sheets 插件:使用 Google App Script 将匿名/动态功能设置为菜单 [英] Google Sheets add-on: Set anonymous/dynamic functions to Menu using Google App Script

问题描述

我想为 Google 表格插件中的动态菜单设置动态功能.我正在使用以下代码:

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:

"找不到脚本函数:function-name"

"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.

任何帮助将不胜感激.

谢谢.

推荐答案

修改点:

• 在您的脚本中，当电子表格打开时，onOpen(e) 只运行一次.By this, when the menu is selected, the functions are not installed.我认为这就是您出现问题的原因.
• 为了运行动态安装的功能，每次都需要运行创建菜单的脚本.这似乎是由于当前的规范造成的.

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.

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);
  };
}

• 在这个修改后的脚本中，当电子表格打开时，自定义菜单被创建.And, when the menu is selected, the dynamically installed function is run.
• 此脚本在每次运行函数时运行 installFunctions(); 行.这样，安装的功能就可以运行了.这对于创建动态安装的函数很重要.
• 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.
• Google Workspace 中的自定义菜单
• 相关答案1
• 相关答案2

这篇关于Google Sheets 插件:使用 Google App Script 将匿名/动态功能设置为菜单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！