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, Slides,
or Forms, but script functions can also be triggered by clicking on images and
drawings in Google Sheets.
Apps Script can add new menus in Google Docs, Sheets, Slides,
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. For more
information on the types of dialogs you can open, see the
guide to dialogs and sidebars
.
function onOpen() {
var ui = SpreadsheetApp.getUi();
// Or DocumentApp, SlidesApp 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, SlidesApp or FormApp.
.alert('You clicked the first menu item!');
}
function menuItem2() {
SpreadsheetApp.getUi() // Or DocumentApp, SlidesApp or FormApp.
.alert('You clicked the second menu item!');
}
A document, spreadsheet, presentation, 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 replaces the old. 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.
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.
- In Google Sheets, select the menu item
Extensions
>
Apps Script
to create a
script that is bound to the spreadsheet.
Delete any code in the script editor and paste in the code below.
function showMessageBox() {
Browser.msgBox('You clicked it!');
}
Return to Sheets and insert an image or drawing by selecting
Insert > Image
or
Insert > Drawing
.
After inserting the image or drawing, click it. A small drop-down menu
selector appears in the top right-hand corner. Click it and choose
Assign script
.
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
.
Click the image or drawing again. The function now executes.