Google Sheets Tutorial: Building Custom Menus with Apps Script

Creating a custom menu for a Google Sheets add-on using Google Apps Script involves writing JavaScript code to define the menu items and their corresponding functions. Here’s a step-by-step guide on how to do it:

1. Open Google Sheets and the Script Editor:

• • Open your Google Sheets document.

    • Go to Extensions > Apps Script.

• 2. Create a New Script:

  • In the script editor, you’ll see a default script file called Code.gs. You can use this file or create a new one by going to File > New > Script file.

• 3. Write the Menu Code:

  • Add the following code to your script file to create a custom menu:

    function onOpen() {
      var ui = SpreadsheetApp.getUi();
      // Add a custom menu to the Google Sheets UI
      ui.createMenu('Custom Menu')
        .addItem('First Item', 'menuItem1')
        .addItem('Second Item', 'menuItem2')
        .addSeparator() // Optional: adds a separator line
        .addSubMenu(ui.createMenu('Sub-menu')
          .addItem('Sub-menu Item 1', 'subMenuItem1')
          .addItem('Sub-menu Item 2', 'subMenuItem2'))
        .addToUi();
    }

    function menuItem1() {
      SpreadsheetApp.getUi().alert('You clicked the first item!');
    }

    function menuItem2() {
      SpreadsheetApp.getUi().alert('You clicked the second item!');
    }

    function subMenuItem1() {
      SpreadsheetApp.getUi().alert('You clicked the first sub-menu item!');
    }

    function subMenuItem2() {
      SpreadsheetApp.getUi().alert('You clicked the second sub-menu item!');
    }

4. Save and Test Your Script:

   • Save your script by clicking on the disk icon or going to File > Save.

   • Close the script editor and reload your Google Sheets document.

   • You should see a new menu called Custom Menu in the menu bar.

5. Authorize the Script:

   • The first time you run the script, you’ll need to authorize it to access your Google Sheets document.

   • Click on one of the custom menu items. You’ll be prompted to authorize the script. Follow the authorization steps.

6. Using the Custom Menu:

   • Once authorized, clicking on the items in your custom menu will trigger the corresponding functions defined in your script.

Conclusion

This basic example demonstrates creating a custom menu with two items and a sub-menu.

You can expand this by adding more items, sub-menus, and corresponding functions to handle various tasks within your Google Sheets add-on.

If you like our content and want to stay updated on what we’re working on, check out our new product suite, Workconnect360. It's designed for small and medium businesses, offering solutions for invoicing, payroll generation, expense tracking, employee shift, and vacation management.

Feel free to reach out if you have any questions or need further assistance.