The User Interface Problem
Writing a powerful Google Apps Script to automate your spreadsheet is an excellent first step. You might write a script that formats raw data, sends automated emails, or calculates complex tax brackets.
The problem arises when you share that spreadsheet with your non-technical colleagues. To run your automation, they have to click on “Extensions,” open the daunting “Apps Script” editor, find the correct function, and click “Run.” They will almost certainly break the code or simply refuse to use it.
To make your automations accessible, you must build a Custom Menu. Using a few simple lines of Apps Script, you can inject a brand new dropdown menu directly into the standard Google Sheets toolbar (right next to “File”, “Edit”, and “View”). Your colleagues can then trigger your complex scripts with a simple, familiar mouse click.
Step 1: The `onOpen` Trigger
Google Apps Script includes several “Simple Triggers”—special functions that run automatically when a specific event occurs. To create a menu the moment a user opens the spreadsheet, we must use the exact function name onOpen().
- Open your Google Sheet.
- Click on Extensions > Apps Script.
- Delete any default code and paste the following:
function onOpen() {
var ui = SpreadsheetApp.getUi();
// Create a new menu named "My Automations"
ui.createMenu('My Automations')
.addItem('Format Report', 'runFormatter')
.addItem('Send Emails', 'runEmailer')
.addSeparator() // Adds a horizontal line for organization
.addItem('Clear Data', 'runClear')
.addToUi();
}
Understanding the Code
SpreadsheetApp.getUi()fetches the user interface environment of the active spreadsheet.createMenu('Name')establishes the top-level title that will appear in the toolbar.addItem('Display Text', 'functionName')is the crucial link. The first argument is the plain text the user will see. The second argument (in quotes) is the exact name of the Apps Script function that should execute when they click it.addToUi()finalizes the build and injects it into the toolbar.
Step 2: Define the Worker Functions
If you click your new menu buttons right now, they will fail, because the functions runFormatter, runEmailer, and runClear do not exist yet.
Paste the following dummy functions below your onOpen() code to simulate the actual automations:
function runFormatter() {
SpreadsheetApp.getUi().alert('Formatting the report now...');
// Your actual formatting code would go here
}
function runEmailer() {
SpreadsheetApp.getUi().alert('Sending emails to clients...');
// Your actual email code would go here
}
function runClear() {
var ui = SpreadsheetApp.getUi();
// It is best practice to ask for confirmation before destructive actions
var response = ui.alert('Are you sure you want to clear all data?', ui.ButtonSet.YES_NO);
if (response == ui.Button.YES) {
ui.alert('Data cleared.');
// Your actual clear code would go here
}
}
Step 3: Test the Integration
- Click the Save icon (the floppy disk) in the Apps Script editor.
- Close the Apps Script tab and return to your Google Sheet.
- Refresh the web page (F5 or the browser refresh button).
Wait a few seconds for the sheet to fully load. You will see a brand new menu item appear at the top of the screen titled “My Automations”.
Click it, and you will see your beautifully formatted dropdown options. Click “Format Report”, and your custom alert box will pop up on the screen.
Conclusion
By wrapping your complex code behind a native, graphical Custom Menu, you transform a fragile script into a robust internal application. This removes the technical barrier for your team, ensuring your automations are actually utilized without requiring anyone to look at a single line of JavaScript.