Add a Custom Menu to Google Sheets, Docs, Slides, and Forms

Simple Guide to Adding a Custom Menu Using Google Apps Script


Google Apps Script allows users to develop business applications and custom functions that integrate with G Suite Applications like Sheets, Docs, Gmail, Calendar, and Forms.


These custom functions can be triggered by a time-based trigger, or by an event such as on open or on form submit, or manually. This post will focus on manual triggering of script functions.

There are two primary ways to trigger a script manually. You can assign a script to a drawing/image so that when a user clicks the drawing/image, the script function will run. Or you can create a custom menu and run the script function from this menu.


Custom menus can be created in Google Docs, Sheets, Slides, or Forms. Below I will guide you through how to build a menu with two sub items that run separate functions in your Google Sheet.


In Google Sheets, there are actually two ways to create a custom menu. You could use the addMenu method which only works for Sheets or the createMenu method which works for all the apps mentioned above. We'll use the createMenu in this guide since it's more widely applicable.


Here is the Class Menu description from Google's App Script Guide.


Class Menu


A custom menu in an instance of the user interface for a Google App. A script can only interact with the UI for the current instance of an open document or form, and only if the script is container-bound to the document or form. For more information, see the guide to menus.


Methods



Here is the result of the above script.


Let's go through the code.


Since we want our custom menu item to appear when the sheet first loads, we will use the onOpen(e) function trigger. The onOpen(e) trigger runs automatically when a user opens a Sheet, Doc, Slide, or Form that they have permission to edit. Note that the trigger only appears on a Form when editing it.


SpreadsheetApp.getUi() returns an instance of the spreadsheet's user-interface environment that allows the script to interact with the UI. If you want to add a custom menu to a Google Doc, Slides, or Form, just change this line to DocumentApp.getUi(), etc.


The next line creates a menu item on the existing toolbar called 'My Menu'. We've added two items to this menu using the addItem() method and separating them with a visual separator. Each of the items calls a different function when selected.


Finally, addToUi() inserts the custom menu into the instance of the user interface.


Conclusion


Once you save the script, reload the sheet, doc, slide, or form and the custom menu should appear in the toolbar. The first time you click the menu it should ask you to authorize the function associated with the menu item. After that, you should be all set to use the custom menu to run your Google Script functions.



1,331 views

© 2018  Proudly created with Wix.com