Triggers (onEdit, onOpen) in Google Sheets - Real Business Scenario

Scenario Mode

👤 Your Role: You are a data analyst at a small retail company.

📋 Request: Your manager wants an automated Google Sheet that welcomes users when they open the sheet and highlights any changes made to sales data immediately after editing.

📊 Data: You have a sales data sheet with columns: Date, Product, Region, Sales Amount. The data is updated daily by the sales team.

🎯 Deliverable: Create a Google Sheet with scripts that show a welcome message on opening and highlight edited cells in yellow.

Sample Data

Date	Product	Region	Sales Amount
2024-06-01	Widget A	North	1200
2024-06-01	Widget B	South	850
2024-06-02	Widget A	East	950
2024-06-02	Widget C	West	400
2024-06-03	Widget B	North	1100
2024-06-03	Widget C	South	700
2024-06-04	Widget A	East	1300
2024-06-04	Widget B	West	600

1

Step 1: Open the Google Sheets script editor by clicking Extensions > Apps Script.

No formula needed; just open the script editor.

Expected Result

You see a blank script editor ready for code.

2

Step 2: Write a function named onOpen to show a welcome message when the sheet opens.

function onOpen() { SpreadsheetApp.getUi().alert('Welcome! Please update sales data carefully.'); }

Expected Result

When the sheet is opened, a popup alert with the welcome message appears.

3

Step 3: Write a function named onEdit to highlight the edited cell in yellow after any change.

function onEdit(e) { e.range.setBackground('yellow'); }

Expected Result

Any cell edited in the sheet changes its background color to yellow immediately.

4

Step 4: Save the script and close the editor. Reload the Google Sheet to test the onOpen trigger.

No formula; just save and reload.

Expected Result

A welcome alert appears when the sheet reloads.

5

Step 5: Edit any sales data cell to test the onEdit trigger.

No formula; just edit a cell.

Expected Result

The edited cell background changes to yellow.

Final Result

Google Sheet: Sales Data
+------------+----------+--------+--------------+
| Date       | Product  | Region | Sales Amount |
+------------+----------+--------+--------------+
| 2024-06-01 | Widget A | North  | 1200         |
| 2024-06-01 | Widget B | South  | 850          |
| 2024-06-02 | Widget A | East   | 950          |
| 2024-06-02 | Widget C | West   | 400          |
| 2024-06-03 | Widget B | North  | 1100         |
| 2024-06-03 | Widget C | South  | 700          |
| 2024-06-04 | Widget A | East   | 1300         |
| 2024-06-04 | Widget B | West   | 600          |
+------------+----------+--------+--------------+

- Edited cells highlight in yellow.
- Welcome alert shows on open.

✓Users see a friendly welcome message each time they open the sheet.

✓Any changes to sales data are visually highlighted immediately.

✓This helps reduce errors and improves data tracking.

Bonus Challenge

Modify the onEdit function to only highlight cells in the 'Sales Amount' column and clear previous highlights in that column.

Show Hint

Use e.range.getColumn() to check if the edited cell is in the 'Sales Amount' column (column 4). Use sheet.getRange() to clear backgrounds in that column before highlighting.

Practice

1. What does the onOpen trigger do in Google Sheets?

easy

A. Saves the spreadsheet automatically

B. Runs a script automatically when the spreadsheet is opened

C. Runs a script when a cell is edited

D. Deletes all data when the sheet is opened

Solution

1. Step 1: Understand the purpose of onOpen

   The onOpen trigger runs a script automatically when the spreadsheet is opened by a user.

2. Step 2: Compare with other triggers

   Unlike onEdit, which runs on cell changes, onOpen activates only on opening the file.

3. Final Answer:

   Runs a script automatically when the spreadsheet is opened -> Option B

4. Quick Check:

   onOpen = runs on open [OK]

Hint: Remember: onOpen triggers when file opens, not on edits [OK]

Common Mistakes:

• Confusing onOpen with onEdit trigger
• Thinking onOpen runs on every cell change
• Assuming onOpen saves the file automatically

2. Which of the following is the correct way to define an onEdit trigger function in Google Sheets Apps Script?

easy

A. function onEdit(e) { /* code here */ }

B. function onEdit { /* code here */ }

C. function onEdit() { /* code here */ }

D. function onEdit(e) => { /* code here */ }

Solution

1. Step 1: Recall correct function syntax with event object

   The onEdit trigger function must accept an event object parameter e to access edit details.

2. Step 2: Check syntax correctness

   function onEdit(e) { /* code here */ } uses correct function declaration with parameter e and braces.

3. Final Answer:

   function onEdit(e) { /* code here */ } -> Option A

4. Quick Check:

   onEdit needs (e) parameter and braces [OK]

Hint: Include (e) parameter to access edit info in onEdit [OK]

Common Mistakes:

• Omitting the (e) parameter in onEdit function
• Using arrow function syntax which is invalid here
• Missing parentheses or braces in function declaration

3. Given this onEdit function in Google Sheets Apps Script:

function onEdit(e) {
  if (e.range.getA1Notation() === 'A1') {
    e.source.getActiveSheet().getRange('B1').setValue('Edited!');
  }
}

What happens when you edit cell A1?

medium

A. Nothing happens

B. Cell A1 will be cleared

C. Cell B1 will display the text 'Edited!'

D. An error occurs because of wrong syntax

Solution

1. Step 1: Understand the trigger condition

   The function checks if the edited cell is 'A1' using e.range.getA1Notation().

2. Step 2: Analyze the action on condition true

   If true, it sets the value of cell 'B1' to 'Edited!' on the active sheet.

3. Final Answer:

   Cell B1 will display the text 'Edited!' -> Option C

4. Quick Check:

   Editing A1 triggers B1 = 'Edited!' [OK]

Hint: Check edited cell with e.range.getA1Notation() [OK]

Common Mistakes:

• Assuming the edited cell changes instead of B1
• Thinking the script clears A1
• Believing the code has syntax errors

4. This onOpen function is intended to show a custom menu, but it doesn't work:

function onOpen() {
  var ui = SpreadsheetApp.getUi;
  ui.createMenu('My Menu')
    .addItem('Say Hello', 'sayHello')
    .addToUi();
}

What is the error?

medium

A. Missing parentheses after getUi

B. Function onOpen must have parameter e

C. createMenu is not a valid method

D. addItem requires two parameters

Solution

1. Step 1: Identify method call syntax

   The code uses SpreadsheetApp.getUi without parentheses, so it references the function but does not call it.

2. Step 2: Correct method call

   Adding parentheses getUi() calls the method and returns the UI object needed to create the menu.

3. Final Answer:

   Missing parentheses after getUi -> Option A

4. Quick Check:

   Method calls need () to execute [OK]

Hint: Always add () to call methods like getUi() [OK]

Common Mistakes:

• Forgetting parentheses on method calls
• Thinking onOpen needs parameters
• Misunderstanding addItem parameters

5. You want to create an onEdit trigger that automatically timestamps column B when a user edits column A in the same row. Which script correctly does this?

hard

A. function onEdit(e) {
if (e.range.getColumn() === 2) {
var row = e.range.getRow();
e.source.getActiveSheet().getRange(row, 1).setValue(new Date());
}
}

B. function onEdit(e) {
if (e.range.getColumn() === 1) {
e.source.getActiveSheet().getRange('B' + e.range.getRow()).setValue('Timestamp');
}
}

C. function onEdit(e) {
if (e.range.getRow() === 1) {
e.source.getActiveSheet().getRange('B1').setValue(new Date());
}
}

D. function onEdit(e) {
if (e.range.getColumn() === 1) {
var row = e.range.getRow();
e.source.getActiveSheet().getRange(row, 2).setValue(new Date());
}
}

Solution

1. Step 1: Check column condition for edits in column A

   The script must detect edits in column 1 (A) using e.range.getColumn() === 1.

2. Step 2: Set timestamp in column B of the same row

   It gets the row number and sets the current date/time in column 2 (B) of that row using setValue(new Date()).

3. Final Answer:

   function onEdit(e) { if (e.range.getColumn() === 1) { var row = e.range.getRow(); e.source.getActiveSheet().getRange(row, 2).setValue(new Date()); } } -> Option D

4. Quick Check:

   Detect col A edit, timestamp col B same row [OK]

Hint: Use getColumn() and getRow() to target cells dynamically [OK]

Common Mistakes:

• Checking wrong column number for edits
• Using fixed cell references instead of dynamic rows
• Setting text instead of actual timestamp