The Problem
A business team needed recurring finance and compliance reports delivered automatically, visually rich (real charts, not just tables), gated to the right days, and reliable enough to run for months without a human babysitting them. A missed report meant a manual scramble to reconstruct numbers.
It grew into a suite of seven reports in one monorepo. Four are live: a daily finance report (built every weekday but emailed only Mon/Wed/Fri), a weekly compliance audit (Fridays), an open-items report (event-driven, split per client group with Excel + PDF delivered to per-group Google Drive folders), and a weekly invoicing workbook delivered to a Google Chat space. Three are not live yet: an email-triggered weekly PDF-per-market report that is built and in its proving period, a forecast-vs-actuals report in test, and a pending-closeout prototype. Reliability was the constraint. Each report is triggered by something more dependable than a bare cron timer, and re-running one produces an identical artifact.
Architecture
A Google Apps Script time-trigger fires and posts a repository_dispatch to GitHub (the open-items report is instead chained off the upstream local-pipeline data refresh, so its email never goes out with stale data). The receiving workflow applies a weekend check and, for daily finance, a Mon/Wed/Fri day-of-week gate, both evaluated in Eastern time so they behave the same whether the trigger fired the workflow or someone ran it manually. On a sending day the Python generator queries read-only Supabase analytics views (asyncpg + psycopg2), renders Chart.js 4.4 charts inside an HTML page that Playwright headless Chromium prints to PDF, and ships via the Gmail API. Open-items additionally writes per-group Excel + PDF to dated Drive sub-folders and shares the folder links in the email body.
The reports
The reports share the trigger → render → deliver stack but differ in cadence, data, and output:
| Report | Trigger | Cadence | Output | Status |
|---|---|---|---|---|
| Daily finance | Apps Script time trigger | Built weekdays; emailed Mon/Wed/Fri | HTML + PDF via Gmail | Live |
| Weekly compliance audit | Apps Script time trigger | Fridays | PDF via Gmail | Live |
| Open items | Upstream pipeline completion | Mon and Fri | Per-client-group Excel + PDF to Google Drive folders | Live |
| Weekly invoicing workbook | Upstream pipeline completion, Mondays only | Weekly | Two-sheet Excel workbook posted to a Google Chat space | Live |
| Weekly completion report per market | An inbound email from a market lead | Weekly, per market | One-page PDF per market, archived to Drive and emailed | Built, proving period |
| Forecast vs actuals | n/a | n/a | n/a | Test, go-live pending |
| Pending closeout | n/a | n/a | n/a | Prototype |
The Google Chat-delivered weekly report
This replaced an analyst's manual Monday post. It builds a two-sheet Excel workbook that reproduces the columns of her earlier spreadsheet export (with a placeholder row when a sheet is empty, so the file is never confusingly blank) and posts it to a Google Chat space through the Chat REST API over OAuth, with the usual greeting. It is dispatched from the end of the task pipeline's run, on Mondays only, so it fires when the data is fresh rather than at a fixed time. The workflow has a test/production target switch and a dry-run option.
The email-triggered weekly PDF-per-market report
Market leads email two Excel files with a subject convention (tracker name, market code, date). An Apps Script mailbox watcher matches the subject tolerantly, replies with the expected convention when the format is wrong, and fires repository_dispatch. The pipeline loads the files into raw, staging and reference tables with full history, takes the reported week from a column inside the data rather than from the subject line, and flags anomalies (the two files disagreeing, an unknown market registering itself). It renders a one-page PDF with seven visuals per market, archives it to Drive, and emails it. It is built and tested but still in its proving period: until the leads trigger it unprompted, output goes to me only.
Key Decisions
- Apps Script / event-driven triggers over GitHub Actions cron. GHA cron drifts and silently skips. Apps Script time-triggers fire reliably, and the open-items report goes one better: it's dispatched by the upstream data pipeline's end-of-run, so the report can't beat its own data.
- Day-of-week gate at the workflow, not the trigger. The Mon/Wed/Fri (
{0, 2, 4}) gate and weekend check run inside the job, in Eastern time, so a manualworkflow_dispatchrespects the same rules as the scheduled trigger. One source of truth. - Chart.js rendered to PDF via Playwright, not matplotlib. Bundling Chart.js locally and printing the page with headless Chromium gives crisp, web-quality charts in the PDF and reuses the exact same rendering the HTML email uses. Base64 is used only to embed the logo.
- Gmail API, not SMTP. A shared OAuth token (
build_gmail_service()/send_email()) sends from the notifier account. There are no SMTP credentials to rotate, and the same token builds the Drive service for open-items uploads. - Freshness guard + retries. Reports refuse to email a stale snapshot, and
_connect_with_retryrides out transient pooler hiccups with exponential backoff.
Code Samples
Weekend check plus the Mon/Wed/Fri day-of-week gate, evaluated in Eastern time so manual runs obey the same rules:
def is_weekend(iso_date: str) -> bool:
"""True if 'YYYY-MM-DD' falls on Saturday or Sunday."""
return date.fromisoformat(iso_date[:10]).weekday() >= 5
_now_et = datetime.now(ZoneInfo("America/New_York"))
_EMAIL_ALLOWED_DOW = {0, 2, 4} # Mon, Wed, Fri. Change cadence here.
if args.force_send:
print(f"--force-send: bypassing DOW gate (today is {_now_et:%A}).")
elif _now_et.weekday() not in _EMAIL_ALLOWED_DOW:
print(f"Skipping send_email: today is {_now_et:%A}; allowed only Mon/Wed/Fri. "
f"Report HTML + PDF already built and uploaded to Drive.")
return
Gmail API send with a shared OAuth token, no SMTP:
def build_gmail_service(credentials_dir: Path):
return build("gmail", "v1", credentials=_load_creds(credentials_dir))
def send_email(*, body_html, attachments, recipients, service, ...):
msg = EmailMessage()
msg["To"] = ", ".join(recipients)
msg.add_alternative(body_html, subtype="html") # inline-CSS, Gmail/Outlook safe
for att in attachments:
... # xlsx / pdf attachments
raw = base64.urlsafe_b64encode(msg.as_string().encode()).decode()
service.users().messages().send(userId="me", body={"raw": raw}).execute()
Connection with exponential backoff so a single transient pooler blip doesn't fail the run:
def _connect_with_retry(url, attempts=3, backoff=2.0):
for i in range(attempts):
try:
return psycopg2.connect(url, connect_timeout=30)
except psycopg2.OperationalError:
if i == attempts - 1:
raise
time.sleep(backoff ** i)
Metrics
- Delivery latency: ~5–15 minutes trigger-to-inbox, dominated by Apps Script poll interval + GHA cold start + Playwright Chromium on a cache miss.
- Daily finance run: ~4 minutes end to end.
- Suite: 7 reports (4 live, 1 in its proving period, 1 in test, 1 prototype); open-items split across three client groups into per-group Drive folders.
- Tests: 33 test files, most of them on the newest report's inbox parsing, loading and validation.
What I Learned
- Treat GHA cron as best-effort, not reliable. Anything that has to fire on time gets a real trigger source: an Apps Script time-trigger, or, better, the upstream data pipeline's completion event so the report can't run ahead of its data.
- Gate at the workflow, never per entry point. The weekend + DOW logic runs once inside the job, in Eastern time, so scheduled runs, manual dispatches, and any future webhook all obey the same rules with zero duplication.
- Render charts the way the web does. Bundling Chart.js and printing with Playwright Chromium gave better-looking PDFs than a separate static-chart library and kept email and PDF visually identical, with months of monitored operation and clean recovery on transient failures.
Links
Public reference repository. The production system runs in a private employer repository.