Why Apps Script automates Google Sheets - Dashboard Impact

Dashboard Mode - Why Apps Script automates Google Sheets

Dashboard Goal

Understand how Apps Script automates tasks in Google Sheets to save time and reduce errors.

Sample Data

Task	Status	Due Date	Assigned To	Completed
Send weekly report	Pending	2024-06-10	Alice	FALSE
Update inventory	Completed	2024-06-08	Bob	TRUE
Review budget	Pending	2024-06-12	Charlie	FALSE
Clean data	Completed	2024-06-09	Alice	TRUE
Prepare presentation	Pending	2024-06-15	Bob	FALSE

Dashboard Components

• KPI Card: Total Tasks
  Formula: =COUNTA(A2:A6)
  Shows total number of tasks (5)
• KPI Card: Completed Tasks
  Formula: =COUNTIF(E2:E6, TRUE)
  Shows how many tasks are completed (2)
• KPI Card: Pending Tasks
  Formula: =COUNTIF(E2:E6, FALSE)
  Shows how many tasks are still pending (3)
• Automated Status Update Table
  Uses Apps Script to automatically mark tasks as "Completed" if the due date is past today.
  Example script snippet:
  function autoCompleteTasks() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
const data = sheet.getRange('A2:E6').getValues();
const today = new Date();
for (let i = 0; i < data.length; i++) {
const dueDate = new Date(data[i][2]);
if (dueDate < today) {
sheet.getRange(i + 2, 5).setValue(true);
sheet.getRange(i + 2, 2).setValue('Completed');
}
}
}
• Summary Table
  Shows tasks grouped by Assigned To and counts completed:
  Formula example:
  =QUERY(A1:E6, "select D, count(E) where E = TRUE group by D", 1)
  Shows how many tasks each person completed.

Dashboard Layout

+----------------------+----------------------+----------------------+
| Total Tasks (5)      | Completed Tasks (2)   | Pending Tasks (3)    |
+----------------------+----------------------+----------------------+
| Automated Status Update Table (Tasks with updated status)          |
+-------------------------------------------------------------------+
| Summary Table (Completed tasks by person)                         |
+-------------------------------------------------------------------+

Interactivity

Adding a date filter lets you see tasks due before or after a certain date. When you change the filter, the Automated Status Update Table and KPI cards update automatically to reflect the filtered tasks. The summary table also updates to show completed tasks only for the filtered data.

Self Check

If you add a filter to show only tasks assigned to "Alice", which components update and what changes?

• KPI Cards update to show total, completed, and pending tasks only for Alice (Total: 2, Completed: 1, Pending: 1).
• Automated Status Update Table shows only Alice's tasks with updated statuses.
• Summary Table shows completed tasks count for Alice only.

Key Result

Dashboard shows how Apps Script automates task status updates and tracks task completion in Google Sheets.