谷歌电子表格添加自定义菜单到列表表 [英] google spreadsheet add custom menu to list sheets

问题描述

我有一个Google电子表格，其中包含很多工作表.去我想要的一张纸很不舒服.

因此，我想创建一个列出我的工作表的自定义菜单.有没有简单的方法可以做到这一点?(使用Google Apps脚本..?)

解决方案

我相信您的目标如下.

• 您要通过在Google Spreadsheet的自定义菜单上选择表格来移动表格.

为此，这个答案如何?

在这个答案中，我想提出一个示例脚本，通过修改此答案来实现您的目标.

注意:

• 在这种情况下，自定义菜单会动态更新.因此，即使添加或删除了工作表，当运行 installFunctions()的功能时，自定义菜单也会更新.
• 并且在上述脚本中，当电子表格打开时，自定义菜单由OnOpen事件触发器添加.
• 当现有功能名称与安装的功能名称相同时，该功能无法正常使用.所以请注意这一点.

参考文献:

• Google Apps脚本:动态创建电子表格菜单项
• G Suite中的自定义菜单

已添加:

但是，如果工作表名称中有特殊字符(例如①，②..)，则会出现错误.我认为，如果菜单上没有隐藏的表格，那会更好.你能给我更多建议吗?

在您的其他请求之后，修改后的脚本如下.

在此示例脚本中，可以使用特定字符，并且不包括隐藏的工作表.

示例脚本:

  installFunctions();//这是运行该函数所必需的.所以请不要删除它.function onOpen(){}//可以用作简单触发器.函数installFunctions(){const menu = SpreadsheetApp.getUi().createMenu('sample menu');const ss = SpreadsheetApp.getActiveSpreadsheet();ss.getSheets().forEach(s => {如果(！s.isSheetHidden()){const名称= s.getSheetName();const functionName =`sample $ {Utilities.base64Encode(name).replace(/[= + \/]/g，")}`;;this [functionName] =()=>ss.getSheetByName(name).activate();menu.addItem(name，functionName);}})menu.addToUi();} 

I have a google spreadsheet which contains a lot of sheets. It's uncomfortable to go to a sheet that I want.

So I want to make a custom menu that list my sheets. Is there an easy way to make this? (using google apps script..?)

解决方案

I believe your goal as follows.

• You want to move the sheet by selecting the sheet at the custom menu on Google Spreadsheet.

For this, how about this answer?

In this answer, I would like to propose a sample script for achieving your goal by modifying this answer. Ref

Sample script:

Please copy and paste the following script to the script editor. And, please run installFunctions(). By this, the custom menu is added, and you can see the sheet names in the menu. When you select one of them, the active tab is changed to the selected sheet name.

installFunctions(); // This is required to run the function. So please don't remove this.
function onOpen() {} // This can be used as the simple trigger.

function installFunctions() {
  const menu = SpreadsheetApp.getUi().createMenu('sample menu');
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.getSheets().forEach(s => {
    const name = s.getSheetName();
    this[`sample${name}`] = () => ss.getSheetByName(name).activate();
    menu.addItem(name, `sample${name}`);
  })
  menu.addToUi();
}

Result:

Note:

• In this case, the custom menu is dynamically updated. So even when the sheet is added and removed, when the function of installFunctions() is run, the custom menu is updated.
• And, in above script, when the Spreadsheet is opened, the custom menu is added by the OnOpen event trigger.
• When the existing function names are the same with the installed function name, the function is not correctly worked. So please be careful this.

References:

• Google Apps Script: Dynamically creating spreadsheet menu items
• Custom Menus in G Suite

Added:

But, If there is a special character in the sheet name, such as ①,②.., there is an error. and I think it would be better if the hidden sheets weren't on the menu. Can you give me some more advice?

Above your additional requests, the modified script is as follows.

In this sample script, the specific characters can be used and the hidden sheets are not included.

Sample script:

installFunctions(); // This is required to run the function. So please don't remove this.
function onOpen() {} // This can be used as the simple trigger.

function installFunctions() {
  const menu = SpreadsheetApp.getUi().createMenu('sample menu');
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.getSheets().forEach(s => {
    if (!s.isSheetHidden()) {
      const name = s.getSheetName();
      const functionName = `sample${Utilities.base64Encode(name).replace(/[=+\/]/g, "")}`;
      this[functionName] = () => ss.getSheetByName(name).activate();
      menu.addItem(name, functionName);
    }
  })
  menu.addToUi();
}

这篇关于谷歌电子表格添加自定义菜单到列表表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！