Google Apps脚本:动态创建电子表格菜单项 [英] Google Apps Script: Dynamically creating spreadsheet menu items

问题描述

我正在尝试使用Google Apps脚本在Google表格中创建一个动态填充的菜单.

I am trying to create a dynamically populated menu in Google Sheets using Google Apps Script.

1. 我有一张班级"工作表，其中列出了我所教的课程.
2. 在运行脚本时，我得到了脚本以读取这些类并将其加载到数组中.
3. 为了只对原始类"表中的值进行硬编码，我想为每个这些类创建一个子菜单项.

该工作表称为类". 类别表中的值为8H，9p1、9p2等. 它们在单元格A1:A12中. 在调试器中，数组menuItemArray正确加载了类"表中的所有预期类.

The sheet is called 'Classes'. The values in the classes sheet are 8H, 9p1, 9p2 etc. They are in cells A1:A12. In the debugger the array, menuItemArray, loads correctly with all expected classes from the 'Classes' sheet.

我得到的错误是:

TypeError:在对象9p1中找不到函数addSubMenu. (第13行 文件代码")

TypeError: Cannot find function addSubMenu in object 9p1. (line 13, file "Code")

这是当您进入生产线时

menuItemArrayClass =  menuItemArray [menuCount]

对于我做错了什么或做任何更好的方法的任何帮助，我将非常感谢.

I would be really grateful for any help as to what I am doing wrong or any better ways to do it.

这是我的代码:

function onOpen(e) {
    var ui = SpreadsheetApp.getUi(); 
    var menuCount = 0; 
    ui.createMenu('Manage Timetable')
    .addItem('First item', 'menuItem1')
    .addSeparator()

    var menuItemArray =     SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Classes').getDataRange().getValues();  
    for (menuCount=1;menuCount < menuItemArray.length;++menuCount) {
        var menuItemArrayClass = [] 
        menuItemArrayClass =  menuItemArray [menuCount]
        .addSubMenu(ui.createMenu('Manage Classes')
            .addItem(menuItemArrayClass [menuCount] + 'Schedule Timetable', 'runBatch1'))
        .addToUi();
    }     
}

推荐答案

• 您要创建自定义菜单.
• 您要通过编辑电子表格来更新自定义功能.
• 您要在自定义菜单中为多个功能使用一个功能名称，例如myFunction().
• 当您从自定义菜单运行函数时，您希望将每个值都作为函数的参数.
• 示例情况如下.
  • "A"至"I"列中有值.
  • 自定义菜单中有9个功能.打开电子表格时，将创建自定义菜单.函数名称与每个列名称相对应.
  • 单击"A"列的功能时，将激活"A"列的值.
  • 将"I"列复制到"H"列时，新功能将添加到自定义菜单.
  • You want to create custom menu.
  • You want to update the custom functions by editing the Spreadsheet.
  • You want to use one function name like myFunction() for several functions in the custom menu.
  • You want to give each value to the function as the argument when a function is run from the custom menu.
  • The sample situation is as follows.
    • There are values in the column "A" to "I".
    • There are 9 functions in the custom menu. The custom menu is created when the Spreadsheet is opened. The function names are corresponding to each column name.
    • When the function of the column "A" is clicked, the values of the column "A" is activated.
    • When the column "I" is copied to the column "H", the new function is added to the custom menu.

    我理解如上.如果我的理解是正确的，那么这个答案呢?请认为这只是几个可能的答案之一.

    I understand like above. If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

    不幸的是，在当前阶段，当使用addItem方法将功能添加到自定义菜单时，无法使用该参数.并且，当运行自定义菜单中的功能之一时，无法检索有关所运行的功能名称的信息.这样，您的目标就无法直接实现.因此，需要使用替代方法.

    Unfortunately, in the current stage, when a function is added to the custom menu with addItem method, the argument cannot been able to be used. And when one of functions in the custom menu is run, the information about the function name which was run cannot be retrieved. By this, your goal cannot be directly achieved. So it is required to use the workaround.

    当我看到您的问题时，出于您的目标，我认为该线程很有用.在google.script.run，要求能够直接在脚本编辑器上运行该功能，并且该功能包含在this中.但是在自定义菜单上，当功能包含在this中时，即使无法直接在脚本编辑器中运行该功能，也可以运行该功能.当仅在GAS端运行该功能时，即使无法使用脚本编辑器直接运行该功能，也可以运行该功能.我认为这种情况可以用于解决方法.

    When I saw your question, for your goal, I thought that this thread is useful. At google.script.run, it is required to be able to directly run the function at the script editor and the function is included in this. But at the custom menu, when the function is included in this, the function can be run even when the function cannot be directly run at the script editor. When the function is run in only GAS side, the function can be run even when the function cannot be directly run with the script editor. I thought that this situation can be used for the workaround.

    当通过包含此替代方法来修改脚本时，脚本将如下所示.请复制并将其粘贴到Spreadsheet的容器​​绑定脚本中，该脚本的第一行具有标头("Col1"，"Col2"，，")，第二行具有相应的值.并且当您运行脚本时，请打开电子表格.这样，添加了自定义菜单.通过复制添加新列时，附加列也将添加到自定义菜单中.并且运行自定义菜单上的功能时，将激活与该列对应的值.

    When your script is modified by including this workaround, it becomes as follows. Please copy and paste it to the container-bound script of Spreadsheet which has the headers ("Col1", "Col2",,,) at the 1st row and the values from 2nd row. And when you run the script, please open the Spreadsheet. By this, the custom menu is added. And when new column is added by copying, the additional column is also added to the custom menu. And when the function at the custom menu is run, the values corresponding to the column are activated.

    function onOpen(e) {
      var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
      var headers = ss.getRange(1, 1, 1, ss.getLastColumn()).getValues()[0];
      var ui = SpreadsheetApp.getUi();
      var menu = ui.createMenu('Custom Menu')
      .addItem('First item', 'menuItem1')
      .addSeparator();
      var subMenu = ui.createMenu('Sub-menu');
      for (var i = 0; i < headers.length; i++) {
        var dynamicMenu = headers[i];
        subMenu.addItem(dynamicMenu,'dynamicItem');
      }
      menu.addSubMenu(subMenu).addToUi();
    }
    
    function onEdit(e) {
      onOpen(e);
    }
    
    function menuItem1() {
      SpreadsheetApp.getUi()
      .alert('You clicked the first menu item!');
    }
    
    function dynamicItem() {
      SpreadsheetApp.getUi()
      .alert('You clicked the dynamic menu item!');
    }
    

    收件人:

    function installFunctions() {
      var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
      var headers = ss.getRange(1, 1, 1, ss.getLastColumn()).getValues()[0];
      var ui = SpreadsheetApp.getUi();
      var menu = ui.createMenu('Custom Menu')
      .addItem('First item', 'menuItem1')
      .addSeparator();
      var subMenu = ui.createMenu('Sub-menu');
      for (var i = 0; i < headers.length; i++) {
        var dynamicMenu = headers[i];
        this[dynamicMenu] = dynamicItem(i); // Added
        subMenu.addItem(dynamicMenu,dynamicMenu); // Modified
      }
      menu.addSubMenu(subMenu).addToUi();
    }
    
    function dynamicItem(i) { // Added
      return function() {
        var sheet = SpreadsheetApp.getActiveSheet();
        sheet.getRange(2, i + 1, sheet.getLastRow() - 1, 1).activate();
      }
    }
    
    installFunctions(); // Added
    
    function onOpen() {} // Modified: This can be used as the simple trigger.
    
    function onEdit() {} // Modified: This can be used as the simple trigger.
    
    function onChange() {} // Added: Please install OnChange event trigger to this function.
    

    • 在使用此脚本之前，请将OnChange事件触发器安装到onChange()的功能.这样，当删除列时，将更新自定义菜单.
    • function onEdit() {}和function onChange() {}的功能用于运行onOpen();.
    • Before you use this script, please install the OnChange event trigger to the function of onChange(). By this, when the column is deleted, the custom menu is updated.
    • The functions of function onEdit() {} and function onChange() {} are used for running onOpen();.
    • 为了动态创建自定义菜单，需要在运行该功能时的初始阶段运行此脚本.因此，必须像onOpen();一样将其放置为全局变量.
    • 在这种解决方法中，当函数运行时，每次都运行onOpen.因此，当列数很大时，处理成本将很高.所以请注意这一点.
    • 这是一个简单的示例脚本，用于解释解决方法的一种方法.因此，请根据您的情况进行修改.
    • In order to dynamically create the custom menu, this script is required to be run at initial stage when the function is run. So it is required to be put it as the global like onOpen();.
    • In this workaround, when the function is run, onOpen is run every time. So when the number of columns are large, the process cost will be high. So please be careful this.
    • This is a simple sample script for explaining one methodology of the workaround. So please modify this for your situation.
    • 自定义菜单
    • GAS中3个匿名函数的输出不同
      • 我认为该线程对于思考OP的目标很有用.
      • Custom Menus
      • Different output in 3 anonymous function in GAS
        • I think that this thread was useful for thinking OP's goal.

        这篇关于Google Apps脚本:动态创建电子表格菜单项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！