Triggers (onEdit, onOpen) in Google Sheets - Dashboard Guide

Dashboard Mode - Triggers (onEdit, onOpen)

Dashboard Goal

This dashboard shows how to use Google Sheets triggers onEdit and onOpen to automate tasks. It answers: How can we automatically update or notify when data changes or when the sheet opens?

Sample Data

Task	Status	Last Updated
Prepare Report	Pending	2024-06-01 09:00
Send Email	Done	2024-06-01 08:30
Update Website	Pending	2024-05-31 17:00
Backup Data	Done	2024-06-01 07:45
Team Meeting	Scheduled	2024-06-02 10:00

Dashboard Components

• KPI Card: Pending Tasks Count
  Formula: =COUNTIF(B2:B6, "Pending")
  Shows how many tasks are still pending.
• KPI Card: Last Sheet Open Time
  Value updated by onOpen trigger script.
  Shows the last time the sheet was opened.
• Log Table: Edit Log
  Populated by onEdit trigger script.
  Shows which cell was edited, old value, new value, and timestamp.
• Script Snippet: onOpen Trigger
  function onOpen() { const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Dashboard'); sheet.getRange('E1').setValue(new Date()); }
• Script Snippet: onEdit Trigger
  function onEdit(e) { const logSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('EditLog'); const range = e.range; const oldValue = e.oldValue || 'N/A'; const newValue = range.getValue(); logSheet.appendRow([new Date(), range.getA1Notation(), oldValue, newValue]); }

Dashboard Layout

+----------------------+--------------------+
| Pending Tasks: [ 2 ] | Last Open: [time]  |
+----------------------+--------------------+
|                      Edit Log Table          |
|  Timestamp  | Cell | Old Value | New Value    |
|-------------|------|-----------|--------------|
| 2024-06-01 | B3   | Pending   | Done         |
| ...         | ...  | ...       | ...          |
+---------------------------------------------+

Interactivity

When you open the sheet, the onOpen trigger runs and updates the Last Sheet Open Time KPI automatically.

When you edit any cell, the onEdit trigger runs and adds a new row to the Edit Log Table with details about the change.

The Pending Tasks Count updates automatically if you change any task status in the data table.

Self Check

Try this: Edit the status of a task from "Pending" to "Done".

Question: Which components update?

• The Pending Tasks Count KPI updates to reflect the new count.
• The Edit Log Table adds a new row with the edit details.
• The Last Sheet Open Time does not change because the sheet was not reopened.

Key Result

Dashboard demonstrating Google Sheets onEdit and onOpen triggers to automate task status tracking and logging.