删除Google电子表格中的工作表后,删除菜单项,使用Apps脚本

huangapple go评论85阅读模式
英文:

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, and onOpen 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:

huangapple
  • 本文由 发表于 2023年3月15日 20:04:36
  • 转载请务必保留本文链接:https://go.coder-hub.com/75744457.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定