Case study · In production · public code

Report Automation

Scheduled report suite: finance, compliance and open-items reports via Gmail, Drive and Google Chat

PythonSupabasePlaywrightGmail APIGoogle DriveGitHub Actions
30-second version
Problem
Recurring finance and compliance reports had no reliable automated delivery; a missed report meant reconstructing numbers by hand.
Built
Python report suite triggered by Apps Script and upstream pipeline events, Chart.js rendered to PDF via Playwright, sent over the Gmail API.
Result
4 of 7 reports live for months; ~4-minute daily finance run, 5–15 minutes from trigger to inbox.
Role
Sole engineer

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

rendering diagram…

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:

ReportTriggerCadenceOutputStatus
Daily financeApps Script time triggerBuilt weekdays; emailed Mon/Wed/FriHTML + PDF via GmailLive
Weekly compliance auditApps Script time triggerFridaysPDF via GmailLive
Open itemsUpstream pipeline completionMon and FriPer-client-group Excel + PDF to Google Drive foldersLive
Weekly invoicing workbookUpstream pipeline completion, Mondays onlyWeeklyTwo-sheet Excel workbook posted to a Google Chat spaceLive
Weekly completion report per marketAn inbound email from a market leadWeekly, per marketOne-page PDF per market, archived to Drive and emailedBuilt, proving period
Forecast vs actualsn/an/an/aTest, go-live pending
Pending closeoutn/an/an/aPrototype

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 manual workflow_dispatch respects 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_retry rides 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.

Public reference repository. The production system runs in a private employer repository.