英文:
Delete Menu Items if sheets are deleted in Google Spreadsheet using Apps Script
问题
我的onOpen()函数中有一个脚本,用于清除动态工作表内的内容。
每个子菜单的名称与工作表的名称相同。
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('重置')
.addItem("Meta","resetmetaFunction")
.addItem("Snapchat","resetsnapFunction")
.addItem("TikTok","resettiktokFunction")
.addItem("Twitter","resettwitterFunction")
.addItem("Youtube","resetyoutubeFunction")
.addItem("Google","resetgoogleFunction")
.addItem("LinkedIn","resetlinkedinFunction")
.addItem("Programmatic","resetprogrammaticFunction")
.addItem("清除全部","resetAll")
.addToUi();
}
如果要删除特定工作表,我希望相应的子菜单也会消失。
例如,如果删除工作表"Snapchat",则子菜单"Snapchat"也应该被删除。
我该如何在Apps Script中执行这个操作?
谢谢。
英文:
My onOpen() has a script that clears content inside dynamic sheets.
Each submenu name has the same name as the Sheet.
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Reset')
.addItem("Meta","resetmetaFunction")
.addItem("Snapchat","resetsnapFunction")
.addItem("TikTok","resettiktokFunction")
.addItem("Twitter","resettwitterFunction")
.addItem("Youtube","resetyoutubeFunction")
.addItem("Google","resetgoogleFunction")
.addItem("LinkedIn","resetlinkedinFunction")
.addItem("Programmatic","resetprogrammaticFunction")
.addItem("Clear All","resetAll")
.addToUi();
}
I want those submenus to disappear if a specific sheet is removed.
For example, if the sheet "Snapchat" is removed, the submenu "Snapchat" should also be removed.
How can I execute this in Apps Script ?
Thank you.
答案1
得分: 1
在您的情况下,以下修改如何?
修改后的脚本:
请将以下脚本复制并粘贴到电子表格的脚本编辑器中,并保存脚本。
// 如果您希望在删除表格时动态更改自定义菜单,请在 `onChange` 函数中安装 OnChange 触发器。
function onChange(e) {
if (e.changeType == "REMOVE_GRID") { // 或者如果 (e.changeType == "REMOVE_GRID" || e.changeType == "INSERT_GRID") {
onOpen(e);
}
}
function onOpen(e) {
// 这是来自您的脚本。
var obj = [
{ sheetName: "Meta", func: "resetmetaFunction" },
{ sheetName: "Snapchat", func: "resetsnapFunction" },
{ sheetName: "TikTok", func: "resettiktokFunction" },
{ sheetName: "Twitter", func: "resettwitterFunction" },
{ sheetName: "Youtube", func: "resetyoutubeFunction" },
{ sheetName: "Google", func: "resetgoogleFunction" },
{ sheetName: "LinkedIn", func: "resetlinkedinFunction" },
{ sheetName: "Programmatic", func: "resetprogrammaticFunction" },
{ sheetName: "Clear All", func: "resetAll" },
];
var { source } = e;
var source = SpreadsheetApp.getActiveSpreadsheet();
var sheetObj = Object.fromEntries(source.getSheets().map(s => [s.getSheetName(), true]));
var menu = SpreadsheetApp.getUi().createMenu('重置');
obj.forEach(({ sheetName, func }) => {
if (sheetObj[sheetName]) {
menu.addItem(sheetName, func);
}
});
menu.addToUi();
}
- 在这个脚本中,当电子表格被打开时,将使用当前的工作表创建自定义菜单。
- 如果您希望在删除表格时动态更改自定义菜单,请在
onChange
函数中安装 OnChange 触发器。这样,当您删除一个工作表时,onChange
函数被运行,然后运行onOpen
,并且使用当前的工作表更新自定义菜单。 - 如果您希望在表格被删除和插入时动态运行脚本,请将
(e.changeType == "REMOVE_GRID")
修改为(["REMOVE_GRID", "INSERT_GRID"].includes(e.changeType))
。
参考:
英文:
In your situation, how about the following modification?
Modified script:
Please copy and paste the following script to the script editor of Spreadsheet, and save the script.
// If you want to dynamically change the custom menu when the sheet is deleted, please install OnChange trigger to `onChange` function.
function onChange(e) {
if (e.changeType == "REMOVE_GRID") { // or if (["REMOVE_GRID", "INSERT_GRID"].includes(e.changeType)) {
onOpen(e);
}
}
function onOpen(e) {
// This is from your script.
var obj = [
{ sheetName: "Meta", func: "resetmetaFunction" },
{ sheetName: "Snapchat", func: "resetsnapFunction" },
{ sheetName: "TikTok", func: "resettiktokFunction" },
{ sheetName: "Twitter", func: "resettwitterFunction" },
{ sheetName: "Youtube", func: "resetyoutubeFunction" },
{ sheetName: "Google", func: "resetgoogleFunction" },
{ sheetName: "LinkedIn", func: "resetlinkedinFunction" },
{ sheetName: "Programmatic", func: "resetprogrammaticFunction" },
{ sheetName: "Clear All", func: "resetAll" },
];
var { source } = e;
var source = SpreadsheetApp.getActiveSpreadsheet();
var sheetObj = Object.fromEntries(source.getSheets().map(s => [s.getSheetName(), true]));
var menu = SpreadsheetApp.getUi().createMenu('Reset');
obj.forEach(({ sheetName, func }) => {
if (sheetObj[sheetName]) {
menu.addItem(sheetName, func);
}
});
menu.addToUi();
}
- In this script, when the Spreadsheet is opened, the custom menu is created using the current sheets.
- If you want to dynamically change the custom menu when the sheet is deleted, please install OnChange trigger to
onChange
function. By this, when you delete a sheet,onChange
function is run, andonOpen
is run, and the custom menu is updated using the current sheets. - If you want to dynamically run the script when a sheet is deleted and inserted, please modify
(e.changeType == "REMOVE_GRID")
to(["REMOVE_GRID", "INSERT_GRID"].includes(e.changeType))
.
References:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论