Create Any Nested Custom Menu Structure in Google Sheets, Docs, Slides, Forms
Introduction
Custom menu is a very handy tool to manage the custom scripts and functionalities for the end users. However, it’s a little bit of crowded and tedious when more and more scripts are added to the menu. To organize the scripts and make it easier for the users to find them, we may need to create nested menu structures. And here is my solution to create this structure with one single function, and all you need to do is to prepare the menu items in a proper way.
Function createMenu_
This is the core function to create the custom menu object with the recursive method to handle the nested menu structure. For the custom menu, a caption should be passed, if not, the menu will be created for the add-on menu.
By changing the ui object, you can extend this function easily for Docs, Slides, and Forms.
const createMenu_ =
(ui = SpreadsheetApp.getUi()) => (items, caption = null) => {
const menu = caption ? ui.createMenu(caption) : ui.createAddonMenu();
const createMenuItem = ({ title, items, caption, fn, sep }) => {
if (title && items) {
return menu.addSubMenu(createMenu_(ui)(items, title));
}
if (caption && fn) return menu.addItem(caption, fn);
if (sep) return menu.addSeparator();
};
items.forEach(createMenuItem);
return menu;
};Menu Items Array
The item object can have three types.
Menu item object
// example
const menuItem = { caption: "Func A", fn: "fnToBeDone" };Menu separator object
// example
const menuSeparator = { sep: true };Sub menu object
// example
const subMenu = {
title: "Sub menu",
items: [{ caption: "Func A", fn: "fnToBeDone" }],
};Here is an example of menu items with nested menu structure.
const MENU_ITEMS = [
// menu item object with caption and fn
{ caption: "Func A", fn: "fnToBeDone" },
// menu separator object with sep = true
{ sep: true },
// Sub menu object with title and items
{
title: "Sub Menu",
items: [
{ caption: "Func A", fn: "fnToBeDone" },
{ sep: true },
{
title: "Sub Menu",
items: [
{ caption: "Func A ", fn: "fnToBeDone" },
{ caption: "Func B ", fn: "fnToBeDone" },
{ caption: "Func C", fn: "fnToBeDone" },
],
},
],
},
{ caption: "Func B", fn: "fnToBeDone" },
];
const fnToBeDone = () => {
SpreadsheetApp.getUi().alert("FN to be done.");
};Use Case
Create a custom menu for LC022 and an add-on menu to the Spreadsheet. By changing the UI object, you can create the same menu structure in Slides, Docs, and Forms.
const onOpen = () => {
const ui = SpreadsheetApp.getUi();
const buildMenu = createMenu_(ui);
// create a custom menu
const menu = buildMenu(MENU_ITEMS, "LC022");
// create an addon menu
const addonMenu = buildMenu(MENU_ITEMS);
menu.addToUi();
addonMenu.addToUi();
};