01. The Challenge & Client Requirement
The client manages health tracking for multiple patients using Whoop fitness wearables. Raw Whoop exports arrive as multi-column spreadsheets with daily biometric readings—HRV (ms), Recovery Score (%), and Sleep Score (%). The data is stored in a shared Google Sheet, with one tab per patient containing daily rows.
The main bottleneck was that producing visual trend comparisons (weekly, bi-weekly, monthly) and exporting patient reports required hours of manual chart creation every week. The client needed charts that look and feel like the Whoop app's own trend views, but with configurable date windows and multi-patient support.
02. Technical Architecture & Solution
I built an all-in-one Vanilla JS dashboard with Chart.js for rendering and html2canvas for high-res PNG export. The dashboard runs in two modes: standalone browser preview (index.html) and production deployment as a Google Apps Script sidebar/dialog inside the client's Google Sheet.
- 3 Stacked Line Charts: HRV (ms), Sleep Score (%), and Recovery (%) rendered side-by-side with bar comparison panels showing prior vs. recent period averages.
- Calendar Date Picker & Period Tabs: Click an end date on the built-in calendar; prior and recent periods are computed backward automatically. Weekly / Bi-Weekly / Monthly tabs switch the comparison window.
- Highlight Tool (Drag Annotations): Drag on any chart to mark a date range—annotations appear synchronized across all 3 charts simultaneously via a custom Chart.js plugin.
- Daily Aggregation (
aggregateByDate()): Groups multiple intra-day rows per patient into clean daily averages, eliminating jagged x-axis duplication. - Patient Sidebar: Searchable patient list with full-dataset average HRV per patient, loaded from a single CSV source.
- PNG Export: High-resolution export via html2canvas for pasting directly into patient progress reports on Healthie.
03. Google Apps Script Deployment
The production dashboard is deployed as a Google Apps Script project (Code.js + sidebar.html) attached to the client's Google Sheet. It creates a custom "Whoop Dashboard" menu in the spreadsheet. Clicking Open Dashboard launches the chart interface as a modeless dialog.
The server-side Apps Script functions (getWhoupData(), insertChartImage()) handle reading patient data directly from sheet tabs and inserting exported PNG snapshots back into a dedicated "Dashboard" sheet.
04. Core Aggregation Pipeline
// aggregateByDate() — groups multi-row daily entries into clean averages
function aggregateByDate(entries) {
const map = {};
entries.forEach(e => {
if (!map[e.date]) map[e.date] = { hrv: [], recovery: [], sleep: [] };
if (e.hrv !== null) map[e.date].hrv.push(e.hrv);
if (e.recovery !== null) map[e.date].recovery.push(e.recovery);
if (e.sleep !== null) map[e.date].sleep.push(e.sleep);
});
return Object.keys(map).sort().map(date => {
const d = map[date];
const mean = arr => arr.length
? Math.round(arr.reduce((a, b) => a + b, 0) / arr.length * 10) / 10
: null;
return { date, hrv: mean(d.hrv), recovery: mean(d.recovery), sleep: mean(d.sleep) };
});
}05. Delivered Impact & Outcome
The delivered dashboard completely automated the client's weekly reporting workflow. Patient trend charts—with prior vs. recent period comparison—are now generated in one click directly inside Google Sheets. PNG exports go straight into clinical progress notes on Healthie, replacing the previous manual chart-creation process.