Problem and constraints
The practice's monthly picture - transactions, unique patients, average per transaction, active days, and realisation against the incentive target - had to be assembled from the cashier workbook by hand, so the numbers only existed after somebody did that work. The target itself lived outside the workbook, and the workbook had to keep working exactly as before: it is the practice's book of record, so nothing in the new tooling may change or overwrite it.
Approach
An Apps Script sidebar bound to the cashier workbook reads the payment tab by header name and computes six KPI cards: revenue, transactions, unique patients, average per transaction, active days, and realisation against the target with a progress bar. A month filter reslices the cards, the treatment ranking and the recent-transaction list, while the revenue trend always shows every month so a filtered view never hides the shape of the year. One timezone-safe formatter handles every date, a Config tab holds the monthly target override and the auto-open flag, and a small insight strip counts zero-value transactions, which in practice are free consultations. The sidebar never writes back: all aggregation happens in the script, the workbook stays untouched.
Measured result
Opening the workbook now answers the current month by itself - the KPIs, the revenue trend, the top treatments and realisation against the target, with the target held as a Config value instead of a person's memory.
| What | Value | Scope and source |
|---|---|---|
| KPI cards | 6 | revenue, transactions, unique patients, average per transaction, active days, realisation against target |
| write paths from the dashboard | 0 | read-only by design: aggregation happens in the script, the workbook is never modified |
| Config keys | 2 | monthly target override and auto-open flag |
| date handling | one helper | every date goes through a single timezone-safe formatter, so no locale drift between the script and the sheet |