Create Nested Menu Structure for Google Sheets, Docs, Slides, and Forms: LC022

March 29, 2024

Create Nested Menu Structure for Google Sheets, Docs, Slides, and Forms: LC022

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 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 helper function to create the nested structure with one single function. All you need to do is copy the core function “createMenu_” to your project to use. If you are not very familiar with Google Apps Script, you can follow the video below to code step by step with me.

YouTube

Instructions

Here is the core function “createMenu_”.

ui: you can use ui of Google Sheets, Docs, Slides, and Forms, it’s for Google Sheets by default
items: an array with menu items, can be nested

title: the name of the menu or sub menu
items: the menu items for the sub menu
caption: the name of the script which users will see
fn: the function name of the script
sep: create a line separator when it’s true

caption: the name of the menu, an editor add-on menu will be created when it’s empty or null

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;

};

Demo function for all scripts “fnToBeDone_”, in your project, each menu item will have its own script function.

const fnToBeDone = () => {

SpreadsheetApp.getUi().alert("FN to be done.");

};

Sample menu structure with 3 nested levels, all scripts are calling the same function “fnToBeDone” which was defined above.

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" },

];

Example code: create a custom menu and an add-on menu in Google Sheets with the menu items defined above, when the caption parameter is not assigned, the menu will be created for add-on which you can access them from the “Extensions > LC022”.

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

};

Custom Menu