Task guide
Add a custom menu to a Google Sheet
Add a menu named Helpful tools to the menu bar of a Google Sheet. Its one item shows which cell is selected. Save the script, reload the Sheet, and the menu appears.
1 · Prepare
Put these pieces in place
- Start from the Google Sheet where you want the menu to appear.
- Open the script from that Sheet with Extensions, then Apps Script. A project started at script.google.com cannot add a menu to the Sheet.
- After you save the script, reload the Sheet to see the menu. Editors who already have the Sheet open must reload it too.
Change these values
- Menu name
Helpful toolsThis is the menu label shown beside the standard Sheet menus.
- Menu item name
Show selected cellThis is the action editors choose from the menu.
- Message text
You selectedChange it if you want the alert to use different words.
2 · Review before running
What the script can access and change
Permissions you approve
- See, edit, create, and delete all your Google Sheets spreadsheets. Google asks the first time each person chooses Show selected cell. Building the menu needs no approval. The code only reads which cell is selected.
Effects of one run
- Adds a Helpful tools menu each time someone who can edit the Sheet opens it.
- Shows the selected cell and tab name in an alert. It does not change any Sheet data.
3 · Copy
Paste this into the Apps Script editor
Create or open a script file, replace the example values marked above, then save the project.
const MENU_NAME = 'Helpful tools'; // This is the menu label shown beside the standard Sheet menus.
const MENU_ITEM_NAME = 'Show selected cell'; // This is the action editors choose from the menu.
const MESSAGE_TEXT = 'You selected'; // Change it if you want the alert to use different words.
function onOpen() {
SpreadsheetApp.getUi()
.createMenu(MENU_NAME)
.addItem(MENU_ITEM_NAME, 'showSelectedCell')
.addToUi();
}
function showSelectedCell() {
const range = SpreadsheetApp.getActiveRange();
if (!range) throw new Error('Select a cell before using ' + MENU_NAME + '.');
const message = MESSAGE_TEXT + ' ' + range.getA1Notation() + ' on the ' + range.getSheet().getName() + ' tab.';
SpreadsheetApp.getUi().alert(message);
}4 · Run
Follow these steps in order
Open the script from the Sheet
In the Sheet, choose Extensions, then Apps Script. The editor opens in a new browser tab, and the script it holds is attached to this spreadsheet.
Paste both functions and save
Paste both functions over the starter code and save. Keep onOpen and showSelectedCell together, because the menu calls the second function by name. You do not need to click Run.
Reload the Sheet and try the menu
Reload the Sheet in your browser. Helpful tools appears at the end of the menu bar. Select a cell, choose Helpful tools, then Show selected cell. The first time, Google asks you to approve access. An alert then names the selected cell and tab.
5 · Confirm
How to tell it worked
After you reload the Sheet, Helpful tools appears in the menu bar. Choosing Show selected cell opens an alert such as You selected B4 on the Orders tab.
If the result is different
Troubleshooting
- Helpful tools does not appear in the menu bar.
- Save the script, then reload the Sheet. Check that the function is named onOpen exactly, and that you opened the script from this Sheet with Extensions, then Apps Script.
- Choosing the item shows "Script function not found: showSelectedCell".
- Check that a function named showSelectedCell exists in the script and that its spelling matches the name in addItem. Save, then reload the Sheet.
Technical details
Names used in this script
Open these details when you want to adapt the example or look up an exact service or method name.
Show Apps Script symbols and service pages
Apps Script symbols
Service pages
Keep going
Related tasks, basics, and apps
- Task guide Send an email for each row in a Google Sheet
Send one email to each person listed on a Customers tab. The script marks each row Sent as it goes, so a later run sends only to rows that are not marked yet.
- Task guide Create a Google Doc from each Sheet row
Create one short Google Doc for each row on a Projects tab that has no document link yet. The script writes the link to the new Doc back into the row.
- Object reference Spreadsheet
A Spreadsheet is the whole Google Sheets file. Use SpreadsheetApp.getActiveSpreadsheet() or openById() to get it, then call getSheetByName() or getActiveSheet().
- Object reference Sheet
A Sheet is one tab inside a Google spreadsheet. Get it with getSheetByName() or getActiveSheet(). Use getLastRow() to find the last row with content, or getRange() to select cells to read.
- Object reference Range
A Range represents one cell such as A2 or a rectangular block of rows and columns in a Sheet. Use getValue() for one cell and getValues() or setValues() for a 2D array of rows.
- Basics Run your first Apps Script function
Your first script, from opening the editor to reading its output. You replace the starter function, save, click Run, and read what Logger.log printed in the Execution log.
- Basics Bound scripts and standalone scripts
Every Apps Script project is either attached to one Google file or standing on its own. The kind of project affects how your code opens files, who can see the code, and where you find it later.
- Google app Google Sheets
SpreadsheetApp lets a Google Sheets script read a Sheet or Range, add a custom menu or formula, and act on each row, such as sending one email per row.