01. The Challenge & Core Requirement
Whoop fitness wearables produce raw multi-row daily exports containing heart rate variability (HRV in ms), recovery scores (%), and sleep performance (%). Processing raw CSV spreadsheet logs for multiple patients required clinical staff to manually copy data, compute averages, and create static charts—consuming 3+ hours per week per patient cohort.
The health tech studio needed an automated, embedded dashboard running directly inside Google Sheets that could aggregate multi-row intra-day entries, render 3 stacked clinical metric charts (HRV, Sleep, Recovery) with synchronized cursors, compare current vs. prior period metrics, and export high-resolution PNG charts directly into Healthie patient progress notes.
02. My Core Role & Dashboard Implementation
My responsibility was designing the client-side Chart.js dashboards, structuring the data aggregation pipeline from Sheets to sidebar HTML, and building the Google Apps Script integration code:
- Automated Data Aggregation (
aggregateByDate): Developed a custom JS data transformer that deduplicates multi-row intra-day entries, filters null/NaN values, and calculates clean daily averages for HRV, Recovery, and Sleep. - Synchronized 3-Metric Clinical Stack: Designed three stacked Chart.js line charts with synchronized crosshair tooltips—allowing clinicians to instantly correlate night-time sleep scores with morning recovery readiness.
- Dynamic Date Picker & Period Delta Engine: Implemented interactive period selection tabs (Weekly, Bi-Weekly, Monthly) and a date picker that calculates percentage change deltas against previous baseline periods.
- Google Apps Script Sidebar & Healthie Export: Deployed the web app as a custom Google Sheets sidebar (
Code.js+sidebar.html) with a one-clickhtml2canvasexporter generating 2x Retina PNG images for Healthie clinical notes.
03. Data Transformation Pipeline & Dashboard Visualization
Below is an interactive 3-stage visual flow demonstrating how messy raw spreadsheet logs are automatically aggregated into clean daily tables and transformed into final Whoop health dashboards:
01. Raw Spreadsheet Data (Multi-Patient Logs)
Raw wearable sensor exports contain mixed multi-patient entries with irregular timestamps, duplicate readings, and missing null values:
| # | Date | Time | Patient | HRV (ms) | Recovery | Sleep | RHR | Strain |
|---|---|---|---|---|---|---|---|---|
| 1 | 2026-05-04 | 08:30:15 | Patient #104 | 62 | 78% | null | 50 | 12.4 |
| 2 | 2026-05-04 | 14:15:00 | Patient #104 | null | 78% | null | null | null |
| 3 | 2026-05-04 | 22:00:45 | Patient #104 | 66 | null | 85% | 48 | 14.8 |
| 4 | 2026-05-04 | 07:45:10 | Patient #208 | 54 | 65% | 76% | 55 | 9.8 |
| 5 | 2026-05-04 | 18:20:00 | Patient #208 | 56 | 65% | null | 54 | 11.2 |
| 6 | 2026-05-05 | 08:10:00 | Patient #104 | 58 | 60% | 72% | 53 | 10.2 |
| 7 | 2026-05-05 | 08:00:00 | Patient #208 | 60 | 82% | 88% | 51 | 13.5 |
02. Data Readiness Check & Automation Dispatch Log
The script automatically validates whether date period ranges are complete (YES / NO), then records the automated Google Slides export logs:
| Patient | Month | Weekly 1 | Weekly 2 | Weekly 3 | Weekly 4 | Weekly 5 | Bi-Weekly 1 | Bi-Weekly 2 | Monthly |
|---|---|---|---|---|---|---|---|---|---|
| Patient #104 | 1 | NO | NO | NO | NO | NO | NO | NO | NO |
| Patient #104 | ... | NO | NO | NO | NO | NO | NO | NO | NO |
| Patient #104 | 5 | YES | NO | NO | NO | NO | NO | NO | NO |
| Patient #208 | 1 | NO | NO | NO | NO | NO | NO | NO | NO |
| Patient #208 | 5 | YES | NO | NO | NO | NO | NO | NO | NO |
| Patient | Rendered Timestamp | Doc Link | Data Range | Period | File Name | sent_at | Status | Source |
|---|---|---|---|---|---|---|---|---|
| Patient #104 | 14/05/2026 | https://docs.google.com/presentation/d/.../view | 2026-05-04_2026-05-10 | W1 | Patient104_Weekly_W1_2026-05-04_2026-05-10 | - | Saved | auto |
| Patient #208 | 14/05/2026 | https://docs.google.com/presentation/d/.../view | 2026-05-04_2026-05-10 | W1 | Patient208_Weekly_W1_2026-05-04_2026-05-10 | - | Saved | auto |
03. Whoop Health Report Dashboard
Final clinical dashboard output featuring period comparisons (W / 2W / M), vertical split lines, and p25-p75 interquartile bands:
Whoop Health Report
04. Key Deliverables & Outcomes
The automated Whoop reporting dashboard eliminated over 3.5 hours of manual reporting overhead per patient cohort each week. Clinicians can now open any patient Google Sheet, click the "Whoop Dashboard" sidebar menu, review live interactive recovery trends, and attach high-res clinical PNG charts directly into Healthie patient records with a single click.