01Intro
Title
  • Welcome people as they settle. Don't rush.
  • Introduce yourself briefly — more on the About Me slide.
  • "Today we're going to build something real — a live data dashboard, inside Google Sheets, powered by a ColdFusion API. Four iterations, each one building on the last."
  • Mention session track and approx run time.
02Intro
About Me
  • Keep this short — 60 seconds max.
  • Hit the pre-Google line — it usually gets a laugh from the CF crowd.
  • Mention the cert exam contribution — establishes credibility without bragging.
  • Taekwondo world champion — gets attention, humanises you. Move on quickly.
If the room is quiet, the pre-Google line is your icebreaker. Lean into it.
03Intro
What We'll Cover
  • "You already have a CF API. Your users already have Google Sheets open. Today we connect them."
  • Point out the equation on screen — CF API + Google Sheets = dashboard.
  • No new infrastructure. No install. Works on any machine with a browser.
  • Set expectation: four phases, each runnable as a standalone starting point.
04Intro
The Problem
  • "How many of you have been asked to get data out of an API and into something a non-developer can look at?" — pause for hands.
  • Walk through the usual answers: web app, BI tool, manual CSV export. All have cost or friction.
  • The bottom card is the pivot: Google Sheets is already open on everyone's machine.
  • This is the "why bother" slide. Make it land before moving on.
Wait for the bottom card reveal to make the point. Don't rush past it.
05Intro
What is Apps Script
  • "Apps Script is server-side JavaScript that runs on Google's servers, not in the browser."
  • Key points: no install, built into Sheets via Extensions menu, full HTTP access via UrlFetchApp.
  • "If you've written ColdFusion, you already think in server-side code. This will feel familiar."
  • No CORS — this is the killer feature. Server-side fetch means no preflight, no browser restrictions.
06Roadmap
Where We're Going
  • Walk through all four phases in one sentence each.
  • Phase 1: menu item, fetch, write. Proof of concept.
  • Phase 2: HTML sidebar, async bridge to server.
  • Phase 3: user-driven charts, smart diff rebuild.
  • Phase 4: POST data back, audit log, modal form.
  • "Every phase is a working app on its own. You can stop at any one and ship it."
07Phase 1
Phase 1 — Section Header
  • Transition beat. Let it breathe for a second.
  • "Phase 1 is the bare minimum. One function, one menu item, one HTTP call."
  • Mention ~30 lines of code. Sets expectation that this isn't complicated.
08Phase 1
Phase 1 · Two Key Concepts
  • onOpen(): "This is the Apps Script equivalent of onApplicationStart — it fires every time the spreadsheet opens and adds your menu."
  • UrlFetchApp: "This is your HTTP client. Server-side, no CORS, works exactly like CF's cfhttp."
  • Point out the analogy — CF developers already know this pattern, just different syntax.
  • Two concepts. That's all Phase 1 needs.
09Phase 1
Phase 1 · Demo
▶ Play Video
  • Play the Phase 1 demo video.
  • Narrate: click the menu item, watch it fetch, watch the data appear.
  • Point out: no login, no setup, just a menu click.
  • Keep commentary light — let the demo speak.
10Phase 1
Phase 1 · The Code
  • Scroll through the code slowly — audience needs time to read.
  • Call out the 6-step structure in the comments: fetch → clear → headers → map → write → tidy.
  • Highlight .map() — "transforms an array of objects into a 2D array that setValues() can consume in one call."
  • "One setValues() call instead of looping setValue() — that's the batch write pattern. Remember it."
Good moment to ask if anyone has questions before moving to Phase 2.
11Phase 2
Phase 2 — Section Header
  • "Phase 1 worked, but it's not user-friendly. Phase 2 adds a proper UI — a sidebar."
  • "This is where Apps Script gets interesting — you're writing HTML that runs inside the spreadsheet."
12Phase 2
Phase 2 · Two Environments
  • This is the most important concept in Phase 2. Spend time here.
  • Browser lane: your HTML runs in an iframe — it can't touch the spreadsheet directly.
  • Server lane: your Code.gs runs on Google's servers — it can do everything, but it's not live in the browser.
  • "The bridge between them is google.script.run. That's the whole trick."
  • Analogy: "Think of it like an Ajax call to a CF endpoint, except the endpoint is also inside Google Sheets."
13Phase 2
Phase 2 · How It All Flows
  • Let the animation run — it walks through the flow automatically.
  • Narrate each step as it lights up: click → script.run → server executes → result back → sidebar updates.
  • "Notice step 2 — the call is async. The browser doesn't freeze. The sidebar shows a spinner."
  • Loop runs for 30 seconds then pauses. Good time for questions on the flow.
Don't advance until the full loop has played at least once.
14Phase 2
Phase 2 · The Sidebar
  • Screenshot of the actual sidebar inside the sheet.
  • Point out: it's docked to the right, the sheet is still fully usable alongside it.
  • Note the 300px width — "that's all you get. Design for narrow from the start."
  • The Load Data button is the only action in Phase 2 — keeping it simple.
15Phase 2
Phase 2 · The Client Bridge
  • Focus on the google.script.run chain: .withSuccessHandler(), .withFailureHandler(), then the function call.
  • "The function call is always last in the chain — that's just how the API works."
  • "The success handler receives whatever your server function returns."
  • Return values must be simple types — strings, numbers, plain objects. No CF components or complex objects.
16Phase 2
Phase 2 · The Server Side
  • "The server function looks almost identical to Phase 1 — fetch, clear, write."
  • Key addition: it now returns a result object { success: true, count: n } instead of calling alert().
  • "The sidebar receives that object in its success handler and updates the UI. Clean separation."
  • Point out the try/catch — "always wrap your server functions. If it throws, the failure handler fires in the browser."
17Phase 2
Phase 2 · Demo
▶ Play Video
  • Play the Phase 2 demo video.
  • Point out the sidebar opening, the spinner while loading, the success message.
  • "Same data as Phase 1, but now it's a proper app experience."
18Phase 2
Phase 2 · The Code
  • Side-by-side view — Sidebar.html left, Code.gs right. Drag the divider if needed.
  • Left side: Bootstrap for styling, google.script.run chain, button state management.
  • Right side: showSidebar() creates the HTML output — note createHtmlOutputFromFile(), not createHtmlOutput().
  • "File name must match exactly — including capitalisation. Case-sensitive."
19Phase 3
Phase 3 — Section Header
  • "Phase 3 hands control to the user. They pick which stats to chart. The dashboard builds itself."
  • "We also introduce a pattern I call decoupled refresh — loading data and building charts become two separate operations."
20Phase 3
Phase 3 · Server-Side Config
  • STAT_CONFIG is the single source of truth. One object drives everything: sidebar chips, chart types, colours.
  • "Add a stat here and it automatically appears in the sidebar and gets a chart — no other changes needed."
  • Point out the three right-hand cards: one change propagates everywhere, config stays server-side, sidebar renders dynamically.
  • Analogy: "Like a ColdFusion application variable that drives your whole UI."
21Phase 3
Phase 3 · Decoupled Refresh + Smart Diff
  • The key insight: loading data and building charts are now two separate functions.
  • loadLeaderboardData() hits the API. refreshDashboard() reads from the Data sheet — no API call.
  • "Change your stat selection, click Refresh. No re-fetch, instant chart rebuild."
  • Smart diff: charts are tagged with [STATKEY] in their title. On refresh, only changed charts are touched — no flicker.
  • If a stat is removed, full rebuild. If only additions, only new charts are inserted.
22Phase 3
Phase 3 · Demo
▶ Play Video
  • Play the Phase 3 demo video.
  • Point out: stat checkboxes rendered dynamically, Load Data writes the Data sheet, Dashboard tab builds with charts.
  • Show the Refresh flow — change a stat selection, hit Refresh, watch only the changed chart update.
  • "That's the smart diff in action."
23Phase 3
Phase 3 · The Code
  • Left: sidebar calls getStatOptions() on load, renders chips from the response, two separate buttons for Load vs Refresh.
  • Right: STAT_CONFIG drives getStatOptions(), load/refresh split, smart diff using regex on chart titles.
  • Drag the divider to focus on whichever side needs more attention.
  • "The regex /\[([A-Z]+)\]$/ on the chart title is the whole secret to the diff — simple but effective."
24Phase 4
Phase 4 — Section Header
  • "Phase 4 closes the loop. We've been reading from the API. Now we write back to it."
  • "We also add a modal form for new player entry — a proper app pattern, not just a sidebar tweak."
25Phase 4
Phase 4 · HTTP POST
  • UrlFetchApp.fetch(url, options) — same function as GET, just pass an options object.
  • method: 'post', contentType: 'application/json', payload: JSON.stringify(data).
  • muteHttpExceptions: true — critical. Without this, a 4xx/5xx throws and you lose the response body.
  • "Always check getResponseCode() yourself. Don't assume success."
  • Demo endpoint is jsonplaceholder.typicode.com — safe, free, always available.
26Phase 4
Phase 4 · The Override Pattern
  • The four-step flow: Submit → POST → log to Submissions sheet → patch Data sheet cells → charts update automatically.
  • "Charts reference Data sheet cells directly. Patch the cells and the charts are live — no rebuild needed."
  • Match by player name (case-insensitive). Match found: overwrite stat cells. No match: append new row.
  • "This is the override pattern — submissions layer on top of API data without replacing it."
  • On next Load, overwriteDataWithSubmissions() re-applies the log so overrides survive a full reload.
27Phase 4
Phase 4 · Demo
▶ Play Video
  • Play the Phase 4 demo video.
  • Highlight: submitting a player update, watching the Submissions sheet log it, dashboard refreshing with the new value.
  • If time: show the New Player modal opening from the sidebar selector.
  • "This is the full loop — CF API in, form data back out, everything in sync."
28Phase 4
Phase 4 · The Code
  • Left: showNewPlayerModal() — one function, opens a centred dialog over the sheet. submitData() — POST + log + patch. logSubmission() — dynamic headers from STAT_CONFIG.
  • Right: overwriteDataWithSubmissions() — build player name map, loop submissions, match or append.
  • Drag the divider to focus on the override pattern — it's the most interesting part to explain.
  • "The cell patch is the key — getRange(row, col).setValue(val) directly on the matched row."
29Phase 4
Phase 4 · The Modal
  • Left: HTML structure — modal header, identity fields, dynamic stat grid from getStatOptions(), submit + cancel buttons.
  • Right: JS flow — load stat fields on open, validate all fields, call submitData(), on success call refreshDashboard(), then google.script.host.close().
  • "host.close() is how you close a modal from the client side — no server round-trip needed."
  • Point out the two-call chain: submit first, then refresh — they're intentionally separate operations.
30Wrap-Up
Transferable Patterns
  • Six patterns worth taking home — walk through each one in a sentence.
  • UrlFetchApp: server-side HTTP, no CORS, GET and POST any JSON API.
  • google.script.run: the async bridge — always use handlers, never expect a return value directly.
  • HtmlService: real web UI inside any Google Workspace app.
  • Server-side config: STAT_CONFIG pattern — one object drives sidebar, charts, and logging.
  • Decoupled refresh: separate data loading from visualisation. Works in any framework.
  • Audit log: every submission logged before the dashboard updates.
31Wrap-Up
Limitations & Gotchas
  • 300px sidebar: design for it from day one. Don't fight it.
  • No sheet DOM from sidebar: everything goes through google.script.run.
  • 6-minute execution limit: batch writes, never loop individual cell updates.
  • File names are case-sensitive: 'Sidebar' ≠ 'sidebar'. Fails silently.
  • google.script.run is always async: no return value — use withSuccessHandler().
  • Loop setValue() kills performance: always batch with setValues() on a range.
  • 30-second UrlFetchApp timeout: add muteHttpExceptions: true and check the response code.
Ask the room: "Anyone hit any of these already?" — usually gets a groan of recognition.
32Close
Thank You / Q&A
  • Leave this slide up for the entire Q&A — QR code lets people scan the repo right now.
  • Repo has all four phases: Code.gs, Sidebar.html, NewPlayerModal.html for each iteration.
  • Remind: @DFGrumpy on social for follow-up questions after the conference.
  • If someone asks something you don't know: "Great question — open a GitHub issue and I'll dig into it."
  • Watch the time — CFSummit sessions run tight.
Don't advance past this slide. Let the QR code do its job during Q&A.
← swipe to navigate →