System Architecture

This site is a thin front-end layer on top of a Google Sheets + Apps Script workflow. Clients work in Sheets; the code handles the complexity.

📥 1. Ingest

Raw data enters via Gmail (CSVs) or direct entry into the "Master" Sheets.

⚙️ 2. Process (Apps Script)

Scripts normalize headers, map availability colors, aggregate KPIs, and build matrices.

🚀 3. Deploy

Static HTML & JSON files are generated, frozen, and pushed to Cloudflare Pages.

Billboard Rotary Map

Trigger: Menu item runs exportBillboardsMap_().
Logic: Reads columns via a flexible CONFIG.HEADERS map. Availability is mapped to hex colors.
Layering: Splits boards into "Priority" and "Normal" (grouped by facing).
Output: A bundled Leaflet HTML file dropped into Drive.

A fully automated pipeline connecting Gmail to Google Charts.

Ingest: fetchMagellanEpisodeWeekCsv scrapes Gmail for the latest CSV export.
Matrix: buildEpisodeWeekMatrices() transforms raw rows into Week vs. Episode tables.
Styling: applyPreferredColors_() forces a 120-shade palette onto the charts.
Result: Named Ranges feed live charts embedded in the deck.

Choropleth maps generated from aggregated listener data.

Dynamic Headers: detectIndicesByHeader_() finds KPI columns regardless of position.
Aggregation: Weighted averages for Response Rates; Sums for Spend/Impressions.
Injection: Writes per-podcast JSON files, injected into a GeoJSON template via a <script> block.