Google Apps Script

Custom Menus in Google Apps

Scripts can extend certain Google products by adding user-interface elements that, when clicked, execute an Apps Script function. The most common example is running a script from a custom menu item in Google Docs, Sheets, or Forms, but script functions can also be triggered by clicking on images and drawings in Google Sheets, or by clicking on a link in Google Sites.

Custom menus in Google Docs, Sheets, or Forms

Apps Script can add new menus in Google Docs, Sheets, or Forms, with each menu item tied to a function in a script. (In Google Forms, custom menus are visible only to an editor who opens the form to modify it, not to a user who opens the form to respond.)

A script can only create a menu if it is bound to the document, spreadsheet, or form. To display the menu when the user opens a file, write the menu code within an onOpen() function.

The example below shows how to add a menu with one item, followed by a visual separator, then a sub-menu that contains another item. (Note that in Google Sheets, unless you're using the new version, you must use the addMenu() syntax instead, and sub-menus are not possible.) When the user selects either menu item, a corresponding function opens an alert dialog (msgBox() in the older version of Google Sheets). For more information on the types of dialogs you can open, see the guide to dialogs and sidebars.

Google Docs, Forms, or new Sheets Older version of Google Sheets
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('Custom Menu')
      .addItem('First item', 'menuItem1')
      .addSeparator()
      .addSubMenu(ui.createMenu('Sub-menu')
          .addItem('Second item', 'menuItem2'))
      .addToUi();
}

function menuItem1() {
  SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
     .alert('You clicked the first menu item!');
}

function menuItem2() {
  SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
     .alert('You clicked the second menu item!');
}
function onOpen() {
  var ss = SpreadsheetApp.getActive();
  var items = [
    {name: 'First item', functionName: 'menuItem1'},
    null, // Results in a line separator.
    {name: 'Second item', functionName: 'menuItem2'}
  ];
  ss.addMenu('Custom Menu', items);
}

function menuItem1() {
  Browser.msgBox('You clicked the first menu item!');
}

function menuItem2() {
  Browser.msgBox('You clicked the second menu item!');
}

A document, spreadsheet, or form can only contain one menu with a given name. If the same script or another script adds a menu with the same name, the new menu will replace the old. In the older version of Google Sheets, the method removeMenu(name) will remove a menu. In Google Docs, Forms, and the new version of Sheets, menus cannot be removed while the file is open, although you can write your onOpen() function to skip the menu in the future if a certain property is set.

Scripts that are published as add-ons for Google Docs or the new version of Google Sheets cannot create top-level custom menus, but instead automatically get a sub-menu within the Add-ons menu. If an add-on tries to create a top-level menu using the createMenu(name) syntax shown above, the name argument is ignored and the script is given an entry in the Add-ons menu under the add-on's published name. Scripts that have not yet been published as add-ons can accomplish the same effect by calling createAddonMenu() instead of createMenu(name), as this complex sample for a dynamic add-on menu shows:

function onOpen(e) {
  var menu = SpreadsheetApp.getUi().createAddonMenu(); // Or DocumentApp.
  if (e && e.authMode == ScriptApp.AuthMode.NONE) {
    // Add a normal menu item (works in all authorization modes).
    menu.addItem('Start workflow', 'startWorkflow');
  } else {
    // Add a menu item based on properties (doesn't work in AuthMode.NONE).
    var properties = PropertiesService.getDocumentProperties();
    var workflowStarted = properties.getProperty('workflowStarted');
    if (workflowStarted) {
      menu.addItem('Check workflow status', 'checkWorkflow');
    } else {
      menu.addItem('Start workflow', 'startWorkflow');
    }
    // Record analytics.
    UrlFetchApp.fetch('http://www.example.com/analytics?event=open');
  }
  menu.addToUi();
}

Clickable images and drawings in Google Sheets

You can also assign an Apps Script function to an image or drawing in Google Sheets, so long as the script is bound to the spreadsheet. The example below shows how to set this up.

  1. In Google Sheets, select the menu item Tools > Script editor to create a script that is bound to the spreadsheet.
  2. Delete any code in the script editor and paste in the code below.
    function showMessageBox() {
      Browser.msgBox('You clicked it!');
    }
  3. Return to Sheets and insert an image or drawing by selecting Insert > Image or Insert > Drawing.
  4. After inserting the image or drawing, click it. A small drop-down menu selector will appear in the top right-hand corner. Click it and choose Assign script.
  5. In the dialog box that appears, type the name of the Apps Script function that you want to run, without parentheses — in this case, showMessageBox. Click OK.
  6. Click the image or drawing again. The function will now execute.

You can also assign an Apps Script function to a link in Google Sites, so long as the script is bound to the site. The example below shows how to set this up.

  1. In a Google Site, click More > Manage site.
  2. In the sidebar, click Apps Scripts, then Add new script to create a script that is bound to the site.
  3. Delete any code in the script editor and paste in the code below, which will send an email when the user clicks a link.
    function sitesLink() {
      var recipient = Session.getActiveUser().getEmail();
      GmailApp.sendEmail(recipient, 'Email from your site', 'You clicked a link!');
    }
  4. Return to the Google Site and edit a page. Type a label that will become a link, such as Click me, then highlight the text and select Insert > Link.
  5. In the dialog that appears, click Apps Script, then click the sitesLink function that you just created. Click OK.
  6. Click Save at the top of the page.
  7. Click the link you added to the page.
  8. A dialog box will appear and tell you that the script requires authorization. Click OK. A second dialog box will then request authorization for specific Google services. Read the notice carefully, then click Accept, then Close.
  9. Now that the script is authorized, click the link you added to the page again. The function will now execute. Check your email to see the email you sent yourself.

Authentication required

You need to be signed in with Google+ to do that.

Signing you in...

Google Developers needs your permission to do that.