google spreadsheet add custom menu to list sheets

Question

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..?)

Answer 1

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();
}