Create Custom Menus in Google Sheets

If you are familiar with Google Apps Script or using any of our Google Sheets templates then you are probably running custom functions to make your spreadsheet better.

Did you know that you can create a custom menu that lets you run any of these functions right from the sheet?

The value in a custom menu is you can make it do anything you want. In this instance we created a trigger that calls another function to export the dashboard in email.

After you have written a scripts function, here is the snippet you will need to create a custom menu of your own.

function onOpen() {
  var spreadsheet = SpreadsheetApp.getActive();
  var functions = [
    {name: 'Email Dashboard', functionName: 'emailTrigger'}
  ];
  spreadsheet.addMenu('Custom Menu', functions);
}

Here is an explanation of each line to walk you through the function:

function onOpen()

Runs the function when you the spreadsheet and have permission to edit.

var spreadsheet = SpreadsheetApp.getActive();

Calls the current spreadsheet tab you are in

var functions = [
    {name: 'Email Dashboard', functionName: 'emailTrigger'}
  ];

State the name of the trigger and the function name you want to call. In this instance I want the dropdown to include “Email Dashboard” but the function that actually executes the email is called “emailTrigger”.

spreadsheet.addMenu('Custom Menu', functions);

Lastly, add’s the menu name and calls the above “functions” you tell it you want to execute.

If you are looking for more Google Sheets tips check out our Data & Analytics resource center.