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.