How to Create a Custom Menu in Google Apps Script: A Step-by-Step Guide

Google Apps Script is a powerful tool that allows users to extend and automate Google Workspace applications like Sheets, Docs, and Forms. One of its useful features is the ability to create custom menus that enhance the functionality of these apps. In this guide, you will learn how to create a custom menu in Google Sheets using Google Apps Script.

Step 1: Open the Script Editor

1. Open your Google Sheet.
2. Go to the menu bar and click on Extensions > Apps Script. This will open the Google Apps Script editor in a new tab.

Step 2: Write the Script

In the script editor:

1. Delete any default code present.
2. Copy and paste the following code:

function onOpen() {
  // Create a custom menu
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('My Custom Menu')
    .addItem('Say Hello', 'sayHello') // Adds a menu item
    .addSeparator() // Adds a separator line
    .addItem('Show Alert', 'showAlert') // Adds another menu item
    .addToUi(); // Displays the menu in the Google Sheet
}

function sayHello() {
  SpreadsheetApp.getActiveSpreadsheet().toast('Hello, World!', 'Greeting');
}

function showAlert() {
  const ui = SpreadsheetApp.getUi();
  ui.alert('This is an alert from your custom menu!');
}

The onOpen function runs automatically when the spreadsheet is opened. It creates a custom menu called My Custom Menu.
The sayHello function displays a toast message at the bottom-right corner of the screen.
The showAlert function shows a popup alert message.

Step 3: Save and Name Your Script

1. Click on the Save icon or press Ctrl + S (Windows) / Cmd + S (Mac).
2. Enter a meaningful name for your project, such as “Custom Menu Script”.

Step 4: Authorize and Test the Script

1. Click on the dropdown menu in the toolbar and select onOpen.
2. Click the Run button (the triangular play icon).
3. You will be prompted to authorize the script. Follow these steps:

Click Review Permissions.
Choose your Google account.
Click Advanced > Go to [Project Name] (unsafe).
Click Allow.

4. Refresh the Google Sheet, and you should see a new menu called My Custom Menu in the menu
bar.

Step 5: Use the Custom Menu

1. Click on My Custom Menu in the menu bar.
2. Select Say Hello to display a toast message.
3. Select Show Alert to see a popup alert.

Step 6: Customize Your Menu (Optional)

You can modify the custom menu to suit your needs:

1. Change the menu name in ui.createMenu('Your Menu Name').
2. Add more menu items with .addItem('Item Name', 'FunctionName').
3. Link each menu item to a different function in your script.

Example: Adding a Submenu

To add a submenu, modify the onOpen function like this:

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('My Custom Menu')
    .addItem('Say Hello', 'sayHello')
    .addSubMenu(
      ui.createMenu('Submenu')
        .addItem('Submenu Item 1', 'submenuItem1')
        .addItem('Submenu Item 2', 'submenuItem2')
    )
    .addToUi();
}

function submenuItem1() {
  SpreadsheetApp.getActiveSpreadsheet().toast('This is Submenu Item 1');
}

function submenuItem2() {
  SpreadsheetApp.getActiveSpreadsheet().toast('This is Submenu Item 2');
}

Final Tips

Actionable Final Tips:

Test your script thoroughly to ensure it works as expected.
Keep your code organized and use meaningful names for your menu items and functions.
Share your Google Sheet with others, and they can use your custom menu after accepting script permissions.

With these steps, you can create custom menus in Google Sheets to streamline tasks and enhance productivity.