Hints 0 / 16
Marketing AI hackathon · 1 hour · Apps Script + Gemini

Build the weekly marketing update that writes itself.

You'll start with a spreadsheet of B2B SaaS marketing data and end the hour with a tool that builds your dashboard, finds anomalies, drafts the stakeholder email, and pushes it all to a Slides deck — on brand, in your voice, with one click.

Get started → Jump to the ladder

The shape of the hourWhat you'll do, when

Don't try to climb every rung. Most people get to level 3 or 4 in an hour, which is already remarkable. Pick the rungs that solve your Friday afternoon.

0–10 min
Setup
API key, Sheet, paste the starter, build the basic dashboard.
10–25 min
First win
AI weekly summary running. The bar is one paragraph that's actually useful.
25–50 min
Climb
Pick a rung. Anomalies, sparklines, Gmail draft, Slides deck. Your call.
50–60 min
Show & tell
Demo what you built. The interesting thing isn't completeness — it's your angle.

Section 1Setup, in three steps

Three steps. The slowest is the API key. From clicking the template link to a working dashboard is about five minutes.

1

Get a Gemini API key

Open AI Studio, click Create API key, copy the key. Free tier is plenty for the hour.

aistudio.google.com/apikey ↗
2

Open the template, click Make a copy

One link. The template comes with the dataset, the bound Apps Script, and the Marketing engine menu already wired up. Google opens the "Copy document?" dialog for you — click Make a copy and it lands in your Drive.

Open the template ↗

What's in your copy: a Data tab with 60 rows of B2B SaaS marketing data (12 weeks × 5 channels × 4 metrics — sessions, signups, MQLs, demos), three patterns hidden inside (two loud, one quiet), and the starter script that builds a basic dashboard and an AI weekly summary from it.

Prefer manual setup? Download the CSV, paste the starter Code.gs, and follow the long-form SETUP.md. Same end state, more clicks.
3

Save the API key as a Script Property

In your copied Sheet, open Extensions → Apps Script. In the script editor, click the gear icon (Project Settings) on the left rail, scroll down to Script Properties, click Add script property.

  • Name: GEMINI_API_KEY
  • Value: paste your AI Studio key

Save. Reload the Sheet. The Marketing engine menu appears next to Help.

Why a Script Property? The key never lives in your code, never gets pushed anywhere, never appears in version control. You can rotate it without touching the script.

Section 2Your first two clicks

Before you climb anywhere, get the bare-bones working. These two menu items prove your Gemini wiring is alive.

Build basic dashboard

Marketing engine → Build basic dashboard. A new Dashboard tab appears with a chart of total sessions per week.

The first time you click, Google will warn you the app isn't verified. This is expected — it's your own script asking permission to access your own Sheet. Click Advanced → Go to project (unsafe) → Allow. After that the menu items work without prompting.

Generate weekly summary

Marketing engine → Generate weekly summary. A Summary tab appears with 4–6 bullets written by Gemini. This is your foothold for everything else.

The first Gemini call takes 5–10 seconds. Subsequent calls are 2–4 seconds. If it's hanging longer than 30 seconds, jump to the troubleshooting card below.

When things go wrong

Quick reference. The four most common stumbles in the first 10 minutes:

What you seeWhat it usually means
No Marketing engine menu after reloadingThe script wasn't pasted, or the Sheet wasn't reloaded. Check the Apps Script editor has your code, then reload the Sheet (Cmd-R / Ctrl-R).
Cell shows API 401 or API 400The GEMINI_API_KEY Script Property is missing or misspelled. Project Settings → Script Properties — the name is case sensitive (exactly GEMINI_API_KEY).
"No 'Data' tab" alertThe imported tab is named something else. Right-click the tab → Rename → make it exactly Data (capital D, no trailing space).
Permissions dialog looks scaryNormal. Click Advanced → Go to project (unsafe) → Allow. You're authorising your own script to talk to your own Sheet.

Section 3Before you climb

Two minutes that pay back across every rung. You'll set up a re-usable data context block, and learn the prompting pattern we'll use the whole way up.

The pattern

Every ladder prompt has the same shape: your current code, your data context, what you want, and how Gemini should respond. Once you generate the data context once, you'll paste it into every prompt for the rest of the hour.

Two prompting surfaces to know about:

  • Sheets Gemini side panel (the ✨ icon top right of your Sheet) — has your data automatically. Use it for "describe my data" or "what's in this Sheet" questions.
  • Gemini Canvas (gemini.google.com → Canvas) — better for "write me Apps Script code" requests. You can iterate on the same artifact, see code update inline, and copy clean blocks straight to your Code.gs. (If you've got access to Gemini Agent Mode, use that instead — even better for multi-step build prompts.)

Step 1 — Get your data context

Open your copied Sheet. Click the ✨ Gemini icon (top right) to open the side panel. Paste this prompt:

You're looking at my Sheet directly. Help me understand it.

Tell me:
1. What columns we have, with the data type of each (date, text, integer, etc.)
2. The shape of the data — how many rows, how many distinct values per categorical column
3. Three sample rows that span the time range
4. Anything you'd flag as unusual at first glance

Format your reply as a clean "Context:" block I can paste into other prompts. No commentary outside that block.

Gemini will reply with a Context: block summarising your schema, sample rows, and any patterns it spots. Copy that whole reply. You'll paste it into every ladder prompt below as the == DATA CONTEXT == block.

Step 2 — Know your prompt shape

Every prompt below has four parts. Skim once, then forget — you'll just paste and go.

You're helping me extend a Google Apps Script project I already have.

== MY CURRENT CODE.GS ==
[copy/paste your Code.gs from the Apps Script editor]

== DATA CONTEXT ==
[paste the Context: block from the kickoff prompt above]

== WHAT I WANT ==
[the actual upgrade ask — be specific about what you want it to do]

Update the Apps Script code.

Section 4The upgrade ladder

Eight rungs. Pick whichever fit your Friday-afternoon pain. Each one has a Gemini prompt template you can paste straight into Gemini Canvas (or Agent Mode) to get the code.

Make it yours: Sessions stacked by channel is the obvious chart. What does your CMO actually look at first? Edit the question before you ask it.

Guidance

The starter drew one chart of total sessions per week. The next obvious move is to break that down by channel — but it's not the only move. Funnel charts (sessions → demos), conversion-rate trend lines, channel-comparison bars all answer different questions. Pick the question that matters most.

The more specific you can be about what the chart should answer, the better the result. "Stacked sessions by channel, weeks on the x-axis, paid channels on top of organic" beats "make a sessions chart" every time. If you've got a chart your CMO already loves, describe it instead of inventing a new one.

Marketing performance dashboard with three charts: total acquisition funnel, session-to-demo conversion rate, and demos by channel
Example: stacked-channel sessions, conversion-rate trendline, demos-by-channel bars
Costs 1 hint
Costs 1 more hint

Make it yours: The default summary writes for "your team." Whose voice does your team actually use? What words do they hate?

Guidance

The starter passes all 60 rows to Gemini and asks for 4-6 bullets. Verbose, generic, doesn't sound like your team. The smart version: pass less data (latest two weeks only), ask for named sections, and bake your team's voice into the prompt.

The big design choice is which sections matter. "What changed" is universal. "Recommended next step" might be redundant if your CMO already has next-step instincts. Adapt the structure to your team.

Screenshot example coming soon — what good looks like for this rung.
Costs 1 hint
Costs 1 more hint

Make it yours: ±2σ catches loud anomalies. What's the quiet anomaly your team has been arguing about for weeks? Write the prompt that finds that one.

Guidance

The dataset has three patterns. Two are loud — ±2σ rolling-mean detection finds them in seconds. One is quiet — the conversion-rate drift in Paid search — and ±2σ on raw values misses it entirely.

Two design choices: what window size for the rolling mean (4 weeks is a default; shorter is twitchier, longer dampens real signal)? And do you flag absolute values or ratios between funnel stages? Ratios catch the quiet ones.

Screenshot example coming soon — what good looks like for this rung.
Costs 1 hint
Costs 1 more hint

Make it yours: Sparklines of Sessions are the default. Which metric matters most to YOU? It's probably not sessions.

Guidance

Sparklines are tiny line charts that live in single cells via =SPARKLINE(range, options). Cheap, dense, glance-able. They complement the big chart — let you see all 5 channels' shapes at once without taking up half the screen.

The design choice: which metric do you sparkline? Sessions (the obvious one), conversion rate (the interesting one), demos (the bottom-of-funnel one). Different choices tell different stories.

Screenshot example coming soon — what good looks like for this rung.
Costs 1 hint
Costs 1 more hint

Make it yours: Who's going to read the email? Your CMO? Your CFO? Your team? Voice changes for each one. Edit before you draft.

Guidance

GmailApp.createDraft(to, subject, body, {htmlBody}) is the whole API. The hard part isn't the call — it's the email itself. Email-safe HTML means: tables for layout, inline styles only, no <style> tags, no external CSS. Most modern clients are forgiving but Outlook still isn't.

The design choice is who's reading. CMO? Strip to a paragraph and one chart. Whole team? Bullet list of what changed. CFO? Numbers and money first, story second. Don't write one email for all of them.

Screenshot example coming soon — what good looks like for this rung.
Costs 1 hint
Costs 1 more hint

Make it yours: Decks are read by people who skipped the meeting. What's the one slide they need? Lead with that.

Guidance

SlidesApp.create(name) makes a new deck. slide.insertSheetsChart(chart) embeds your dashboard chart live-linked — edit the Sheet later, the deck updates. AI-generated speaker notes mean the presenter has something to say even if they didn't write the slides themselves.

Design choice: how many slides? A 3-slide deck is presented in 60 seconds. A 15-slide deck takes 15 minutes. Match the deck to the meeting it's going to.

Screenshot example coming soon — what good looks like for this rung.
Costs 1 hint
Costs 1 more hint

Make it yours: Your default brand voice is "plain and direct." Yours might be punchier, drier, more technical. Edit DEFAULT_BRAND_DNA before you ask Gemini to use it.

Guidance

Small change, big payoff: your AI summaries stop sounding like generic Gemini output. The pattern is brand rules in a Doc → Apps Script reads it → injects into every prompt as system context.

Design choice: what goes in the brand DNA? At minimum: tone rules and a banned-words list. Better: a sample on-brand sentence and a sample off-brand sentence side by side. Models learn voice from examples better than from rules.

Screenshot example coming soon — what good looks like for this rung.
Costs 1 hint
Costs 1 more hint

Make it yours: Friday at 4pm is the obvious cadence. What's YOUR team's actual rhythm? Tuesday at 9? End-of-month? Edit the trigger to match.

Guidance

ScriptApp.newTrigger(handler).timeBased().onWeekDay(...).atHour(...).create(). That's the API. The hard part is making the setup function idempotent — running it twice shouldn't create two emails. Always remove existing triggers for the same handler before creating a new one.

Design: when does it run? Friday afternoon, just before the weekend, so the email's already there Monday morning? Or Monday at 9am, so it's the first thing you see? Pick the answer that matches your team's rhythm — not mine.

Screenshot example coming soon — what good looks like for this rung.
Costs 1 hint
Costs 1 more hint

Section 5Prompting Gemini for Apps Script

Apps Script isn't quite the same as the JavaScript Gemini sees in its training data. A few sharp instructions save a lot of debugging.

Always say "Apps Script, not Node"

Without this, Gemini will reach for fetch, require, process.env — none of which exist. Say "Apps Script V8 runtime" up front.

List the global services you have

Tell it you have SpreadsheetApp, DocumentApp, GmailApp, SlidesApp, UrlFetchApp, PropertiesService, CacheService. Otherwise it'll invent imports.

Show it the data shape

Don't say "I have some marketing data". Say "Data sheet: columns Week, Channel, Sessions, Signups, MQLs, Demos. 60 rows." Specifics beat adjectives.

Specify the model and endpoint

"Use gemini-2.5-flash via v1beta generateContent" lands cleaner than "use Gemini". Otherwise it'll guess at the API shape and the field names.

Ask for error handling explicitly

Gemini skips error handling unless you ask. Add: "Handle non-200 responses gracefully, return a string starting with 'API error: ...'."

Show existing code it should integrate with

Paste your existing callGemini_() helper. It'll reuse it instead of rewriting it. Less code, fewer bugs.

Specify what NOT to refactor

"Show me only the new function. Don't change my existing onOpen." Stops it volunteering "improvements" you didn't ask for.

Ask for the smallest version first

Don't ask for the full feature in one prompt. Ask for a working bare version, then add features one at a time. Each addition is a new prompt with the prior version as context.

Always paste your current Code.gs first

Every ladder prompt has a == MY CURRENT CODE.GS == block. Fill it. Without it, Gemini invents stubs that don't match your real helpers, and the code it gives back won't fit. The 5 seconds of paste saves 5 minutes of debugging.

Section 6Apps Script cheat sheet

Six interfaces, what to reach for when, and the one limit that'll bite you. Skim now; come back when something doesn't quite fit.

Six interfaces — when to use which

You don't need to know the code for any of these. You need to know which one solves your problem, and what to say to Gemini to get it.

Custom menu

Best for: a teammate who already lives in the doc and wants a button. Lowest friction. Works in Sheets, Docs, Slides, Forms.

Say to Gemini

"Add a custom menu to my Sheet called Marketing engine with three items — Build dashboard, Generate weekly summary, Draft weekly email — each calling the function with the matching name."

Sidebar

Best for: a real UI — charts, forms, refresh buttons — without leaving the document. Whole HTML pages live here.

Say to Gemini

"Add a function showInsights() that opens a sidebar on the right side of my Sheet. The sidebar should show our weekly AI insights, list any flagged anomalies, and have a Refresh button that re-runs the analysis."

Dialog box

Best for: confirmation, configuration, multi-step setup. Blocks the document until dismissed.

Say to Gemini

"Before my draftWeeklyEmail() function runs, open a modal dialog asking the user to pick the audience — CMO, CFO, or whole team. Pass that choice into the prompt so the email voice matches."

Web app

Best for: a tool you give to people outside the doc. One Deploy click, you've got a URL — auth handled by Google.

Say to Gemini

"Walk me through deploying this Apps Script as a web app I can share. I want a URL my team can open. Start with a simple doGet() that returns one HTML page with the latest weekly summary."

Custom function

Best for: when your user already thinks in cells. =ASK(...) works alongside =SUM and =VLOOKUP.

Say to Gemini

"Add an =ASK() custom function so I can write =ASK("summarise this week", A1:F61) in any cell. Keep the prompt focused — one row at a time max, since each call has a 30-second cap."

Trigger

Best for: things that should happen without anyone clicking. No UI. The most hands-off interface there is.

Say to Gemini

"Set up a time-driven trigger that runs buildDashboard() and draftWeeklyEmail() every Friday at 4pm. Make it idempotent — running setup twice shouldn't create duplicates."

The gold-standard prompt

A gold-standard ask. Notice what makes it good: it tells Gemini what context to assume, what helpers already exist, what the output format should be, and — critically — what the team's voice sounds like. Steal the structure for any new request.

You're helping me extend an Apps Script project I have.

== CONTEXT ==
- A Sheet with a "Data" tab — 12 weeks of marketing data,
  5 channels × 4 metrics (sessions, signups, MQLs, demos).
- Existing helpers in my Code.gs:
  - callGemini_(prompt) — talks to Gemini for me.
  - getData_() — returns the rows from the Data tab.
- My team's voice: plain, calm, no jargon. We never use
  "leverage", "synergy", "unlock", "supercharge", "seamless".
  We're specific with numbers — say "+23%" not "up significantly".

== WHAT I WANT ==
A function `weeklyExecBrief()` that:
1. Reads the latest two weeks of data
2. Computes weekly totals + week-over-week change
3. Calls Gemini ONCE with all the numbers, asking for:
   - One headline sentence
   - Three bullets of what changed
   - One concrete next step
4. Drops the result into a "Brief" tab with the week as a heading

In the prompt the function sends to Gemini, include:
- The voice rules above
- "If you don't know why a number moved, say 'we don't know yet' —
  don't invent a cause."

Update the Apps Script code.
Show me the helper Gemini wrote (you don't need to read it)

This is the callGemini_ function already in your starter Code.gs. Every function above uses it. Useful if you ever need to recreate it from scratch — otherwise ignore.

function callGemini_(prompt) {
  const apiKey = PropertiesService.getScriptProperties()
    .getProperty('GEMINI_API_KEY');
  if (!apiKey) throw new Error('Set GEMINI_API_KEY in Script Properties.');

  const url = 'https://generativelanguage.googleapis.com/v1beta/models/' +
              'gemini-2.5-flash:generateContent?key=' + apiKey;

  const response = UrlFetchApp.fetch(url, {
    method: 'post',
    contentType: 'application/json',
    payload: JSON.stringify({
      contents: [{ parts: [{ text: prompt }] }],
      generationConfig: { temperature: 0.6 }
    }),
    muteHttpExceptions: true
  });

  if (response.getResponseCode() !== 200) {
    return 'API ' + response.getResponseCode() + ': ' +
           response.getContentText().substring(0, 200);
  }
  const data = JSON.parse(response.getContentText());
  return (data.candidates && data.candidates[0].content.parts[0].text) || '';
}

What fits in one run

Apps Script kills any function that runs longer than 6 minutes. Custom functions used inside cells time out at 30 seconds each. Each Gemini API call takes 3–10 seconds. Most of what you'll build today fits comfortably; a few patterns won't.

✓ Likely to work✗ Likely to time out
One AI weekly summary — single Gemini call, ~5–10s =ASK() filling 100 cells — Sheets recalcs, 100 × 5s ≈ 8 min, hits the timeout
Dashboard with 4 charts, no AI — instant, well under a second Trigger that processes 200 inbox emails one at a time — sequential, will exceed 6 min
One Gmail draft of the weekly email — 1 AI call, ~5–10s AI explanation per anomaly when 50 are flagged — batch them all in one prompt instead
5-slide deck with chart slides + AI speaker notes — 5 AI calls, ~30–60s One AI summary per row of a 60-row sheet — 60 × 5s = 5 min, dangerously close
Anomaly detection over 60 rows + AI explainers for ~5 flags — under a minute A custom function called by hundreds of cells at once — each capped at 30s, recalc storms kill it
Rule of thumb: 1–10 Gemini calls per run is comfortable. 50+ needs batching. If you're tempted to loop =ASK() over rows, send all the rows in one prompt instead. One prompt that returns 50 lines beats 50 prompts that return one line each — every time.

Things that'll trip you up

The "unverified app" warning is normal

First time you click any menu item, Google shows a scary warning. Click Advanced → Go to project (unsafe) → Allow. It's your script asking permission to run on your doc.

Sheet name must match exactly

DATA_SHEET = 'Data' won't find a tab called data or Data (trailing space). Case sensitive, whitespace sensitive. The starter checks for this and tells you when it's wrong.

API key lives in Script Properties, never in code

Project Settings (gear) → Script Properties → Add. Name it GEMINI_API_KEY. Don't paste your key into a .gs file or a Sheet cell — it'll leak the moment you share.

Custom functions can only return primitives

Inside =ASK(...), you can return strings, numbers, or 2D arrays — not objects. If you need structured data, return JSON-stringified text and parse it where you use it.

First Gemini call is slow

5–10 seconds the first time. 2–4 seconds after that. If it's hanging longer than 30s, your key is wrong, you've hit a free-tier quota, or the API is having a moment. Check the troubleshooting card in Section 2.

onOpen builds menus but can't call APIs

Triggered onOpen runs with limited permissions — fine for adding menu items, but it can't call UrlFetchApp.fetch. Auth runs lazily on the first actual menu click. Don't try to do real work in onOpen itself.

That's it. Go build.

Last 10 minutes is for show-and-tell. Bring whatever you've got. The interesting question isn't completeness — it's what does your version do that nobody else's does?

Read the full brief ↗ Brand DNA template ↗
Hints used 0 / 16