Chapter 3 Case study: tidying the monthly sales sheet ・ MISSION 12

Mission 12: Run it from your own menu (onOpen)

📋 Mission list

Task

Make a "Monthly tasks" menu appear when the spreadsheet opens, with "Mark large deals" running markLarge. After saving, reopen the spreadsheet by reloading the browser (F5) and run it from the menu.

October sales - Google Sheets
October sales

For practice, the table goes back to its starting state every time before the code runs (a real spreadsheet doesn't go back. Script changes can sometimes be undone with Ctrl+Z, but not all at once).

Apps ScriptUntitled projectDeploy ▾
function onOpen() {
SpreadsheetApp.getUi()
.('Monthly tasks')
.addItem('Mark large deals', '')
.();
}
function markLarge() {
const sheet = SpreadsheetApp.getActiveSheet();
const lastRow = sheet.getLastRow();
const amounts = sheet.getRange(2, 4, lastRow - 1, 1).getValues();
const labels = amounts.map((row) => [row[0] >= 100000 ? 'Large' : '']);
sheet.getRange(2, 5, labels.length, 1).setValues(labels);
}
Ctrl+S: save · Ctrl+R: run (editor) · F5: reopen

Chapter 3 ・ MISSION 12

Run it from your own menu (onOpen)

Make a "Monthly tasks" menu appear when the spreadsheet opens, with "Mark large deals" running markLarge. After saving, reopen the spreadsheet by reloading the browser (F5) and run it from the menu.

Now
Goal (run from the "Monthly tasks" menu)

Reading the code

function onOpen() {
A function that runs automatically when the spreadsheet opens. It won't run if you rename it.
SpreadsheetApp.getUi().createMenu('Monthly tasks')
Creates a menu called "Monthly tasks" in the screen (Ui).
.addItem('Mark large deals', 'markLarge')
Adds an item. Clicking it runs the function called markLarge.
.addToUi();
Shows the finished menu in the menu bar.

A function named onOpen runs automatically when the spreadsheet opens. If you add a menu here, people who never open Apps Script can run the task from the menu. Good for sheets the whole team uses.

How to get started with GAS and common code are covered in an article.What is GAS? Getting started and common code

The code you write runs in an isolated place inside this page. It can't reach outside the page or other sites.

* This is a simulation of how Google Sheets and Apps Script behave and may differ in some ways from the real thing.

* Google Sheets is a trademark of Google LLC. This site is not affiliated with Google.