Notification rules in Google Sheets - Real Business Scenario

Scenario Mode

👤 Your Role: You are an office manager responsible for tracking team task updates.

📋 Request: Your manager wants you to set up notification rules in Google Sheets to get email alerts when any task status changes or when new tasks are added.

📊 Data: You have a task tracker sheet with columns: Task ID, Task Name, Assigned To, Status, and Last Updated.

🎯 Deliverable: Set up notification rules in Google Sheets to send email alerts when any cell in the sheet is changed or when a user submits a form adding a new task.

Sample Data

Task ID	Task Name	Assigned To	Status	Last Updated
101	Prepare report	Alice	In Progress	2024-06-01
102	Update website	Bob	Not Started	2024-05-28
103	Client meeting	Charlie	Completed	2024-05-30
104	Design brochure	Diana	In Progress	2024-06-02
105	Test software	Eva	Not Started	2024-05-29

1

Step 1: Open your Google Sheets task tracker file.

Expected Result

You see the task data with columns Task ID, Task Name, Assigned To, Status, and Last Updated.

2

Step 2: Click on the 'Tools' menu, then select 'Notification rules...'.

Expected Result

The Notification rules dialog box opens.

3

Step 3: In the Notification rules dialog, choose 'Any changes are made' to get alerts for any edits.

Expected Result

The rule is set to notify you when any cell in the sheet changes.

4

Step 4: Select 'Email - right away' to receive immediate email notifications.

Expected Result

You will get an email as soon as a change happens.

5

Step 5: Click 'Save' to activate this notification rule.

Expected Result

Notification rule is saved and active.

6

Step 6: To get notified when new tasks are added via form, repeat steps 2-5 but select 'A user submits a form' instead of 'Any changes are made'.

Expected Result

You will receive an email notification every time a new task is added through the form.

7

Step 7: Test the notification by changing a task status or adding a new task via the form.

Expected Result

You receive an email alert about the change or new task.

Final Result

-----------------------------------------
| Task Tracker Notification Rules Setup |
-----------------------------------------
| Rule 1: Notify on any changes - Email immediately
| Rule 2: Notify on form submissions - Email immediately
-----------------------------------------

✓Notification rules help you stay updated without checking the sheet constantly.

✓You get instant emails when tasks are updated or new tasks are added.

✓This setup improves team communication and task tracking efficiency.

Bonus Challenge

Create a custom script using Google Apps Script to send personalized email notifications only when the 'Status' column changes.

Show Hint

Use the onEdit(e) trigger and check if the edited cell is in the 'Status' column before sending emails.