To create a menu in a Google Sheet using Apps Script, you can use the onOpen() function along with the createMenu() method. Here’s an example code snippet:
function onOpen() { var ui = SpreadsheetApp.getUi(); // Create a menu and submenus ui.createMenu('Custom Menu') .addItem('Menu Item 1', 'menuItem1') .addItem('Menu Item 2', 'menuItem2') .addSeparator() .addSubMenu(ui.createMenu('Submenu') .addItem('Submenu Item 1', 'submenuItem1') .addItem('Submenu Item 2', 'submenuItem2')) .addToUi(); } // Functions to be executed when menu items are clicked function menuItem1() { SpreadsheetApp.getUi().alert('Menu Item 1 clicked!'); } function menuItem2() { SpreadsheetApp.getUi().alert('Menu Item 2 clicked!'); } function submenuItem1() { SpreadsheetApp.getUi().alert('Submenu Item 1 clicked!'); } function submenuItem2() { SpreadsheetApp.getUi().alert('Submenu Item 2 clicked!'); }
In this code, the onOpen function is triggered when the Google Sheet is opened. It uses the SpreadsheetApp.getUi() method to get the user interface of the spreadsheet.
You can then use the createMenu() method to create a new menu with the name ‘Custom Menu’. You can add multiple menu items using the addItem() method. Each menu item is associated with a specific function to be executed when clicked.
The addSeparator() method adds a separator line between menu items.
You can also create submenus using the addSubMenu() method. In the example, a submenu called ‘Submenu’ is added, and two submenu items are included within it.
Finally, the addToUi() method is called to add the menu to the spreadsheet’s user interface.
The subsequent functions (menuItem1(), menuItem2(), submenuItem1(), submenuItem2()) are the functions that will be executed when the corresponding menu items or submenu items are clicked. In this example, they display alert messages, but you can modify them to perform any desired actions.
When you open the Google Sheet, the custom menu will be available at the top of the screen. Clicking on the menu items will execute the associated functions.
Feel free to modify the code to add or remove menu items and submenus or customize the functionality of the menu items based on your specific requirements.