Google Apps 脚本:动态创建电子表格菜单项 [英] Google Apps Script: Dynamically creating spreadsheet menu items
问题描述
我正在尝试使用 Google Apps 脚本在 Google 表格中创建一个动态填充的菜单.
- 我有一张课程"表，其中列出了我教授的课程.
- 在运行我的脚本时，我让我的脚本读取这些类并将其加载到数组中.
- 为了仅对原始类"表中的值进行硬编码，我想为每个类创建一个子菜单项.
该工作表称为类".类表中的值为 8H、9p1、9p2 等.它们位于单元格 A1:A12 中.在调试器中，数组 menuItemArray 正确加载了类"表中所有预期的类.
我得到的错误是:
<块引用>类型错误:在对象 9p1 中找不到函数 addSubMenu.(第 13 行，文件代码")
这是进入队伍的时候
menuItemArrayClass = menuItemArray [menuCount]如果您对我做错了什么或有什么更好的方法提供帮助，我将不胜感激.
这是我的代码:
function onOpen(e) {var ui = SpreadsheetApp.getUi();var menuCount = 0;ui.createMenu('管理时间表').addItem('第一项', '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('管理类').addItem(menuItemArrayClass [menuCount] + 'Schedule Timetable', 'runBatch1')).addToUi();}}- 您想创建自定义菜单.
- 您想通过编辑电子表格来更新自定义函数.
- 您想为自定义菜单中的多个函数使用一个函数名称，例如
myFunction(). - 当从自定义菜单运行函数时，您希望将每个值作为参数提供给函数.
- 示例情况如下.
- A"到I"列中有值.
- 自定义菜单中有 9 个功能.自定义菜单是在电子表格打开时创建的.函数名对应每一列名.
- 点击A"列的功能时，A"列的值被激活.
- 将I"列复制到H"列后，新功能将添加到自定义菜单中.
我理解如上.如果我的理解是正确的，这个答案怎么样?请将此视为几种可能的答案之一.
问题和解决方法:
不幸的是，在当前阶段，当使用addItem方法将功能添加到自定义菜单时，无法使用该参数.并且当自定义菜单中的功能之一运行时，无法检索有关运行的功能名称的信息.这样，您的目标就无法直接实现.所以需要使用变通方法.
当我看到你的问题时，为了你的目标，我认为
注意:
- 为了动态创建自定义菜单，该脚本需要在函数运行的初始阶段运行.所以需要像
onOpen();一样把它作为全局变量. - 在这个变通方法中，当函数运行时，
onOpen每次都会运行.所以当列数很大时，工艺成本会很高.所以请注意这一点. - 这是一个简单的示例脚本，用于解释解决方法的一种方法.因此，请根据您的情况进行修改.
参考:
- 自定义菜单
- GAS 中 3 个匿名函数的不同输出
- 我认为这个帖子对于思考 OP 的目标很有用.
I am trying to create a dynamically populated menu in Google Sheets using Google Apps Script.
- I have a sheet, 'Classes', where I list the classes I teach.
- On running my script I get my script to read and load these classes into an array.
- In order to only hard-code values in the original 'Classes' sheet I want to then to create a sub-menu item for each of these classes.
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.
The error I get is:
TypeError: Cannot find function addSubMenu in object 9p1. (line 13, file "Code")
This is when stepping into the line
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.
Here is my code:
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();
}
}
- 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.
Issue and workaround:
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.
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.
Modified script:
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.
From:
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!');
}
To:
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.
- 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() {}andfunction onChange() {}are used for runningonOpen();.
Result:
Note:
- 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,
onOpenis 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.
References:
- 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屋！
