Script editor overview in Google Sheets - Dashboard Guide

Dashboard Mode - Script editor overview

Dashboard Goal

Understand how to use the Google Sheets Script Editor to automate tasks and add custom functions.

Sample Data

Task	Status	Due Date
Send invoices	Pending	2024-06-10
Update prices	Completed	2024-06-05
Prepare report	Pending	2024-06-12
Team meeting	Completed	2024-06-07
Backup data	Pending	2024-06-15

Dashboard Components

• KPI Card: Count of Pending Tasks
  Formula: =COUNTIF(B2:B6, "Pending")
  Result: 3
• Custom Script Function: function daysUntilDue(taskRow) { const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); const dueDate = sheet.getRange(taskRow, 3).getValue(); const today = new Date(); const diff = Math.ceil((dueDate - today) / (1000 * 60 * 60 * 24)); return diff; }
  Usage in sheet: =daysUntilDue(2) returns days until due for row 2 task.
• Button to Run Script: A button linked to a script that marks all Pending tasks as Completed.
  Script snippet:
  function markAllCompleted() { const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); const lastRow = sheet.getLastRow(); for(let i=2; i<=lastRow; i++) { if(sheet.getRange(i, 2).getValue() === 'Pending') { sheet.getRange(i, 2).setValue('Completed'); } } }

Dashboard Layout

+----------------------+-------------------------+
|  KPI: Pending Tasks   |  Button: Mark Completed  |
|       (3)             |                         |
+----------------------+-------------------------+
|  Sample Data Table (Tasks, Status, Due Date)          |
|  Rows 2-6                                             |
+-------------------------------------------------------+
|  Custom Function Usage Example (daysUntilDue)         |
+-------------------------------------------------------+

Interactivity

The button runs the markAllCompleted script to update task statuses from Pending to Completed. This changes the data table and updates the KPI card automatically.

The custom function daysUntilDue can be used in any cell to show how many days remain until a task's due date. It updates dynamically based on the current date.

Self Check

If you click the "Mark Completed" button, which components update?

• The Sample Data Table updates to show all tasks as Completed.
• The KPI Card updates to show 0 Pending tasks.
• The Custom Function results remain the same because due dates do not change.

Key Result

A simple dashboard showing task status with a KPI, data table, custom script function, and a button to automate status updates.