# MUST OBEY — Mobile-first build requirements This app's PRIMARY surface is a mobile phone. Build it impeccably on mobile FIRST, then verify on tablet and desktop. Treat the rules below as non-negotiable hard constraints, not suggestions. ## Viewports to verify (every screen, every state) - 320 px, 360 px, 375 px, 390 px, 414 px, 480 px - 768 px, 834 px (iPad portrait / Pro 11) - 1024 px, 1280 px, 1440 px, 1920 px, 2560 px - Plus: 200% browser zoom, landscape orientation on every mobile width, iPhone with safe-area insets visible ## Hard layout rules - Mobile-first CSS. Default styles target mobile; `@media (min-width: ...)` for larger viewports. - Use `dvh` and `svh` instead of `vh` for full-height surfaces (iOS Safari URL-bar bug). - Use `clamp()` for fluid typography across all viewports. - Prefer container queries (`@container`) over media queries for component-level responsiveness. - Use `min(100%, ...)` widths so content never overflows. Zero horizontal overflow at any viewport. - Add `` to every page. - Apply `padding: max(safe-area-inset-X, fallback)` on every edge-bleeding container so notched iPhones in landscape never clip content. - Wide tables and code blocks scroll INSIDE their container (`overflow-x: auto`), never push the body. - Use `background-attachment: scroll` on mobile, not `fixed` (iOS Safari repaint bug). - Avoid `backdrop-filter` on animated elements. Use it sparingly on static surfaces only. - **Canvas Scaling**: Canvases must dynamically scale with window resize events and properly handle high-DPI screens (`window.devicePixelRatio`). Set physical dimensions (`canvas.width`/`canvas.height`) using pixel ratio and render relative to this grid, using CSS to control responsive viewport scaling. - **Robust Storage**: Every access to `localStorage`/`sessionStorage` (especially `JSON.parse` of loaded state or writes) MUST be wrapped in a `try-catch` block to handle disabled storage, private browsing mode, quota limits, or corrupted JSON gracefully. Fall back to a robust in-memory object store. ## Touch & accessibility - Tap targets ≥ 44 × 44 px on touch (Apple HIG). Increase to 48 px under `@media (hover: none) and (pointer: coarse)`. - All interactive controls reachable by keyboard with a visible focus ring; respect `:focus-visible`. - Color contrast ≥ 4.5:1 for body text, 3:1 for UI components. - All images have meaningful `alt`. Decorative images use `alt=""`. - Respect `prefers-reduced-motion: reduce` — zero animation durations under that query. - Forms validate inline; error messages are specific, not "Invalid input". - Modals: focus trap, `Esc` closes, `role="dialog"`, `aria-modal="true"`, focus restored on close. ## Performance bar (Lighthouse mobile, throttled 3G/4G) - LCP < 2.5 s · INP < 200 ms · CLS < 0.1 - JS bundle gzip < 200 KB mobile-first; lazy-load non-critical screens via `React.lazy` / dynamic imports. - No render-blocking resources above the fold. - Images: WebP/AVIF preferred, `loading="lazy"`, explicit `width`/`height` attributes (zero CLS), `srcset` for retina. - Videos: `preload="metadata"`, low-resolution poster, max 720p mobile fallback. Never autoplay with audio. - Fonts: `font-display: swap`; preload only the one used above the fold. - Smooth scroll honoured via CSS `scroll-behavior: smooth` with reduced-motion fallback. ## Pre-ship mobile checklist (the deployer MUST verify before declaring done) 1. Open at 375 px in DevTools — every screen scrolls vertically only; zero horizontal scroll. 2. Browser zoom 200% — layout reflows without overlap. 3. iPhone Safari with the URL bar visible AND landscape — no content under the home indicator; no notch clipping. 4. iPad portrait (768 px) and landscape (1024 px) — no awkward gaps; tablet-specific breakpoints land cleanly. 5. Tap every interactive element with a thumb at real-device size — every target is easy to hit. 6. `prefers-reduced-motion: reduce` — every transition / animation skips cleanly, scroll-behavior becomes instant. 7. Lighthouse mobile score ≥ 90 across all 4 categories. 8. Zero `console.error` and zero CLS shift in real-device testing on a mid-tier Android (e.g. Pixel 6a) and an iPhone SE. --- The original template starts below. All rules above apply on TOP of whatever this template specifies. --- # Spreadsheet Whisperer ## 1. Project **Spreadsheet Whisperer** turns a messy Google Sheet into a live dashboard in twenty-five seconds. Paste the Sheets URL, sign in, and the app reads the sheet directly — no OAuth dance, no API key to wire up, no Looker Studio setup — using AI Studio Build's post-I/O 2026 Workspace integration. Gemini 3.5 Flash infers what the data is about, picks the three numbers that matter most, renders two charts that suit the shape of the data, computes the month-over-month delta, and proposes the next question to ask. The single demo that proves the magic: a user pastes a Google Sheets URL to her expense log — three columns, six months of rows, dates in two different formats, amounts in mixed currencies, "Category" written sometimes lowercase, sometimes title-case, sometimes blank. In under thirty seconds the dashboard renders: **three KPI cards** ("$4,213 spent in May", "Top category: Groceries — $1,184", "Subscriptions up 27 % vs April"); **two visualisations** (a stacked-bar of category spend per month, and a sparkline of weekly total spend); **one month-over-month delta callout** ("You spent $740 more in May than in April — the gap is mostly Subscriptions and Dining"); and **one suggested next question** ("Show me by week instead of month, only Subscriptions."). She taps the suggestion. The dashboard rebuilds in five seconds. Tap any number → the source cells highlight in the embedded sheet view. The dashboard refreshes when the underlying sheet changes — the user adds a row in Google Sheets, the dashboard updates in the next minute without a reload. The source sheet is **never mutated**. Every number is traceable. Ambiguous columns are flagged once, asked about once, remembered forever. This works on any sheet that has shape: a subscriber list, an expense log, an inventory, a time-tracking log, a survey-results export, a CRM mini-pipeline, a sports scoring table. The app does not require the user to know the words "pivot table", "VLOOKUP", or "XLOOKUP". The user pastes a URL. **Tagline:** *Paste a Google Sheets URL. Get a live dashboard in twenty-five seconds — without writing a single pivot table.* ## 2. Target audience The job is universal — anyone with a sheet that has outgrown "just look at the rows" but hasn't earned a Looker Studio setup. - The freelancer with an expense sheet who needs to know where the money is going before the quarterly tax estimate - The newsletter author with a subscriber export who wants to know which referral source actually converts to paid - The small e-commerce operator with an inventory sheet who wants to know which SKU runs out fastest - The hobby project lead tracking volunteer hours across a team spreadsheet - The teacher with a survey-results export who needs the top three patterns by Monday morning - The board treasurer of a small non-profit who has six months of donations in a sheet and needs a one-page picture - The startup operator pasting a CRM export from a tool that doesn't have a dashboard - The student with a Google Form's response sheet who wants charts for the project deck on Thursday - The household with a shared budget sheet — "where did May go?" - The product manager with a sheet of NPS survey responses - The book club running a reading log across twenty members - The amateur sports league with weekly scoring data - Anyone who looked at Looker Studio for ten minutes, closed the tab, and went back to scrolling rows ## 3. Core value propositions - **Paste a URL — that's it.** No OAuth handshake, no service- account JSON, no "share with this email". Post-I/O 2026 AI Studio Build apps can read a signed-in user's Google Sheets directly via the new Workspace integration. The user signs in once with Google, pastes a sheet URL, and the app reads. - **Twenty-five seconds to a dashboard.** Gemini 3.5 Flash with `medium` thinking is fast enough that the user does not get a coffee — they watch the dashboard appear. Three KPI cards, two charts, one delta callout, one next-question prompt. - **The dashboard answers the question the user came with.** Not every column gets a chart. The model infers the most-likely question the user has ("where is the money going?", "which source converts?", "which SKU is selling?") and picks the three metrics that answer it. - **Every number is traceable.** Tap any KPI, any bar, any sparkline point — the corresponding source cells highlight in the embedded sheet view. No black-box numbers. The model is required to emit a `source_range` (A1 notation) for every metric in the structured response; the server validates that the range exists in the parsed sheet. - **Month-over-month, not just "totals".** A flat "$4,213 in May" tells you nothing on its own. The dashboard surfaces the delta against the previous comparable period — last month for monthly data, last week for weekly, last quarter for quarterly. The model chooses the comparable period from the data's natural cadence; if there isn't enough history to compare, it says so. - **Iterate in plain English.** "Show me by week instead of month." "Only Subscriptions." "Group by referral source." The user types a follow-up; the dashboard rebuilds. No formula syntax, no SQL, no UI for filters. - **The sheet is read-only.** The app never writes to the source. Every dashboard is materialised in the app's own Firestore; the sheet is the source of truth and is preserved. - **Ambiguity surfaced once, not every refresh.** "Date" appearing in three formats is asked about exactly once — the user picks the format, the choice is remembered for that sheet forever. Same for currency symbols, category casing, blank cells, header row position. - **Live, not snapshotted.** A change in the source sheet appears in the dashboard within ~60 seconds, via Drive change notifications. The user does not need to "refresh" anything. ## 4. Features to build - One-line URL paste with the Google sign-in button right next to it — the "30-second demo" entry point - Workspace-integrated sheet read (post-I/O 2026 OAuth-free read) — fetch a range, fetch a tab, list tabs, list named ranges, get sheet metadata (last edit time, owner) - Sheet schema inference — column header detection, type inference (date / number / currency / category / free text / boolean), per-column ambiguity flag - "Ask once" prompt for ambiguous columns — date format picker, currency picker, header-row picker, blank-cell handling rule - Dashboard generator: 3 KPI cards + 2 visualisations + 1 month-over-month callout + 1 next-question prompt - Chart picker — the model chooses the chart type (bar, stacked bar, line, sparkline, donut, scatter) from a fixed set based on the data's shape; the chart type is part of the structured response, not the model's free text - Source-cell traceback — every metric stores its `source_range`; tapping the KPI / bar / point highlights it in the embedded sheet view - Natural-language follow-up box — "show me by week", "group by source", "exclude Costco" — rebuilds the dashboard using the same structured response shape - Auto-refresh — Drive `changes.watch` push notification (or 60-second polling fallback) triggers a re-read of the sheet and a delta-only dashboard refresh - Saved dashboards — every sheet the user pastes becomes a named dashboard the user can return to; each dashboard remembers the user's "ask once" answers and the follow-up history - Multi-tab support — if a sheet has more than one tab, the user picks which tab to dashboard - "Show me the data" toggle — flip from chart view to a clean paginated table of the underlying rows (the parsed view, not the raw sheet) - Embedded sheet panel — a read-only inline iframe of the source Google Sheet, scrolled and highlighted by the traceback action - Export — the rendered dashboard as a PDF or as a PNG of the three KPI cards plus the two charts (Nano Banana Pro composes the export layout with crisp typography) - Share — read-only link to a dashboard, optional passcode, the source sheet permissions are NOT changed (the share link shows the dashboard, not the underlying sheet) - Refresh history — the last ten refreshes of the dashboard, each one labelled with what changed in the source sheet - "Why this number?" — tap a KPI's info icon to see the one-paragraph rationale the model wrote for choosing this metric - Honest fallbacks — if the sheet has fewer than 10 rows, the dashboard says "this is a small sample; here are the rows" and shows the table only; if there's no time column, no MoM callout appears - Data type override — per column the user can correct the model's type inference ("treat this as currency, not number") - The "next question" prompt — exactly one suggested follow-up, worded as a question the user might actually type - A privacy panel showing exactly what the app reads, where it is stored, and a one-tap "forget this sheet" that wipes the cached parse and dashboard ## 4b. Required Gemini capabilities + backend services **This template's intelligence comes from the Gemini capabilities below. Wire them up explicitly — don't substitute generic LLM calls.** ### Gemini capabilities (the load-bearing intelligence) - **Sheet shape inference + dashboard plan (Gemini 3.5 Flash, `medium` thinking)** — receives the parsed sheet (first 200 rows of every tab, all column headers, type-inference results, ambiguity flags, last edit time) and emits a `DashboardPlan` JSON object: three `KpiCard`s, two `Visualisation`s, one `MonthOverMonthCallout`, one `NextQuestion`. Every metric carries its `source_range` (A1 notation) so the server can validate the range exists in the sheet. - **Column ambiguity resolver (Gemini 3.5 Flash, `low` thinking)** — given a column the model flagged as ambiguous ("Date" with three formats), emit a `ColumnAmbiguity` object listing the format candidates ranked by frequency, with one example value per candidate the UI can show the user as a chooser. - **Natural-language follow-up parser (Gemini 3.5 Flash, `medium` thinking)** — takes the user's plain-English question ("show me by week instead of month, only Subscriptions") plus the current `DashboardPlan` and the parsed sheet, emits a revised `DashboardPlan`. The follow-up is interpreted in the context of the current view, not the original sheet. - **Why-this-number rationale (Gemini 3.5 Flash, `low` thinking)** — one paragraph (≤ 60 words) explaining why the model chose this specific metric, citing the column names and the row count it considered. Called on-demand when the user taps the info icon on a KPI. - **PDF / PNG export composition (Nano Banana Pro, `gemini-3-pro-image`)** — given the three KPI numbers, the two chart SVGs (rendered server-side from the structured response), and the sheet's display title, generate a 4K-legible one-page poster suitable for emailing. Nano Banana Pro's post-I/O typography rendering means the numbers are crisp at print size. - **Structured output / JSON Schema** — every Gemini call in this template returns structured JSON. The schemas are seeded verbatim in the system instruction and as `responseSchema`. No free-text outputs that the app has to parse heuristically. - **Thinking levels** — `medium` for the dashboard plan and the follow-up parser (they pick metrics and chart types, which is the load-bearing reasoning). `low` for ambiguity resolver and rationale generation. `high` is not used; the dashboards do not require chain-of-thought reasoning depth beyond `medium`. - **Long context (Gemini 3.5 Flash, 1M tokens)** — used when a sheet has more than 200 rows. Send the first 50 rows, the last 50 rows, plus 50 random samples, plus the full type-inference result. **Guardrail:** never exceed 500k tokens of payload per call — chunk and summarise older rows for very large sheets (>50k rows). The shape of the data, not every row, is what the dashboard plan needs. ### Backend services - **Auth — Required.** Firebase Auth with Google sign-in (auto- provisioned by AI Studio Build). Google sign-in is the only supported auth path for v1, because the Workspace integration reads the user's sheets and needs the same identity. Apple sign-in is intentionally **not** offered in v1 — the user cannot read a Google Sheet from an Apple identity without an additional Google sign-in step, so the simpler model is Google-only. - **Database — Required.** Firestore for `users`, `connected_sheets`, `dashboards`, `dashboard_versions`, `column_ambiguity_resolutions`, `follow_up_history`, `refresh_history`, `shared_links`. - **File storage — Optional.** Firebase Storage for the exported PDF / PNG. NOT auto-provisioned by AIS Build today — enable it before exporting. The cached parse of a sheet lives in Firestore, not Storage. - **Workspace integration — Required (the hero capability).** Post-I/O 2026 AI Studio Build apps can read a signed-in user's Google Sheets directly via the new Workspace tool, without an OAuth handshake. **Status: preview, still rolling out to AIS Build accounts as of 2026-06-01** — surface a clear error and a CSV-paste fallback if the capability isn't enabled on the user's account yet. Wire it via the `workspace.sheets` capability declared in the AIS Build config; AIS Build issues the short-lived per-call credential, never exposes a refresh token, and scopes the read to a single sheet at a time. **The app never asks for Drive-wide scope.** A sheet's URL must be pasted by the signed-in user; the app does not list all sheets in the user's Drive. - **Drive change watch — Required for live refresh.** A Cloud Run function subscribes to `drive.changes.watch` for each connected sheet's file ID. On change, push a re-read job; enqueue a dashboard refresh. **Fallback:** if the watch channel cannot be established (some Workspace tenants restrict it), poll the sheet's `modifiedTime` every 60 seconds while the dashboard is open in the browser. - **Email — Required (transactional).** Magic-link share emails for read-only dashboard sharing. Sender domain must be authorised in Firebase Auth. - **Payments — Not needed for v1.** Free for personal use. A future "team" tier (shared dashboards across an organisation with role-based access) might charge via Stripe; not built in v1. - **Free Cloud Run deploy** — post-I/O 2026 AIS Build deploys the first 2 apps to Cloud Run with no credit card. This template is one of those 2; the deploy step ships the Cloud Run functions for the Workspace reader, the dashboard generator, and the Drive change-watch handler. **Environment variables:** every secret (Gemini API key, Firebase service-account JSON, Drive change-watch webhook verification token, Stripe key if team tier added) lives in environment variables — never in the client bundle. Include a `.env.example`. **Auth + data privacy reminders:** never log secrets · never store passwords in plain text · use HTTPS everywhere · honour 'delete my account' inside the UI · explicit opt-in for any analytics · the user's sheet contents are never sent to Gemini for model training (use the Gemini API on the paid tier, where Google does not use your content for model training, per the Gemini API Additional Terms) · the cached parse is per-user, per-sheet, and wipeable with one tap. **Read this first — prompt-craft rules that apply to every call in this template:** 1. **Name the model variant explicitly** in every Gemini API call. Do not let the agent pick the model. See the per-call matrix below. 2. **Pin `thinkingLevel` explicitly** per call. See the matrix. 3. **Seed the JSON Schema as a fenced TypeScript / Zod block** in the system instruction or `responseSchema` field. The literal schemas are below. **Convert the Zod schema to Gemini's `Schema` type via the SDK helper** before passing to `responseSchema` — do NOT pass raw Zod. **Numeric `min`/`max` constraints are documentation only inside `responseSchema`; clamp on the server after the response arrives.** 4. **Pin the system instruction separately** from user input. Use the `systemInstruction` field for persona + behavioural rules; use `contents` for the parsed sheet and the user's question. Never concatenate. 5. **Pre-declare tools as an enable/disable list** per call. The matrix below names which tools are enabled per call. Tools NOT listed for a call should be disabled. 6. **State negative constraints explicitly** — they are listed below. They are NOT "be careful" suggestions; they are hard rules the model must follow. 7. **Strip unsupported Zod modifiers before passing to `responseSchema`** — Gemini's OpenAPI subset rejects `.regex()` / `pattern`, fixed-length `z.tuple()`, and other custom validators. Use a sanitizer that flattens tuples to arrays and removes regex patterns before serializing. Validate those constraints in middleware AFTER parsing. 8. **Files API uses `files/*` resource names, not `gs://` URIs.** The AI Studio Build runtime uses the Gemini Developer API (`@google/genai` SDK). Files API `upload` returns a resource name like `files/abc123xyz`, passed via `fileData: { fileUri, mimeType }`. `gs://` URIs belong to Vertex AI / Cloud Storage. ### Per-call model + tools matrix | Call | Model | thinkingLevel | Tools enabled | |------|-------|---------------|---------------| | Sheet shape inference + DashboardPlan | `gemini-3.5-flash` | medium | (none) | | Column ambiguity resolver → ColumnAmbiguity | `gemini-3.5-flash` | low | (none) | | Natural-language follow-up → revised DashboardPlan | `gemini-3.5-flash` | medium | (none) | | Why-this-number rationale (one paragraph) | `gemini-3.5-flash` | low | (none) | | PDF / PNG dashboard export composition | `gemini-3-pro-image` | n/a | n/a | | Onboarding empty-state illustration | `gemini-3.1-flash-image` | n/a | n/a | *Note for builders:* on image-generation calls, omit `thinkingConfig` entirely — the field is not supported on those models. The `n/a` cells in this matrix are documentation only; do not serialise them into the request body. No call in this template uses `google_search` grounding — every answer is grounded in the user's own sheet, not the public web. ### Primary structured-output schemas (seed verbatim in the prompt) ```typescript import { z } from "zod"; const ColumnType = z.enum([ "date", "datetime", "number", "currency", "percent", "category", "free_text", "boolean", "url", "email", "unknown", ]); const ColumnSchema = z.object({ header: z.string(), // verbatim header text header_normalised: z.string(), // lowercased, trimmed letter: z.string(), // "A", "B", ..., "AA" inferred_type: ColumnType, ambiguity_flag: z.boolean(), // true if the resolver should ask ambiguity_reason: z.string().nullable(), // e.g. "3 date formats observed" example_values: z.array(z.string()), // up to 5, verbatim from sheet non_blank_row_count: z.number(), total_row_count: z.number(), }); const ColumnAmbiguity = z.object({ column_letter: z.string(), column_header: z.string(), candidates: z.array(z.object({ candidate_label: z.string(), // "DD/MM/YYYY", "MM/DD/YYYY" example_value_from_sheet: z.string(), // verbatim row value frequency_in_sample: z.number(), // 0..1 })), recommended_candidate_index: z.number(), // model's best guess user_must_confirm: z.boolean(), // true unless one candidate >= 0.95 }); const ChartType = z.enum([ "single_bar", "stacked_bar", "grouped_bar", "line", "sparkline", "donut", "scatter", "table_only", ]); const Visualisation = z.object({ chart_type: ChartType, title: z.string(), // "Spend by category, last 6 months" x_axis_label: z.string(), y_axis_label: z.string(), source_range: z.string(), // A1 notation, e.g. "Sheet1!A2:D200" group_by_column_letter: z.string().nullable(), measure_column_letter: z.string(), measure_aggregation: z.enum([ "sum", "count", "avg", "min", "max", "median", ]), filter_clauses: z.array(z.object({ column_letter: z.string(), operator: z.enum([ "equals", "not_equals", "contains", "not_contains", "greater_than", "less_than", "in_range", ]), value: z.string(), })), rationale_one_line: z.string(), // why this chart, ≤ 24 words }); const KpiCard = z.object({ label: z.string(), // "Spent in May" value_display: z.string(), // "$4,213" value_numeric: z.number(), // 4213.00 unit_or_currency: z.string(), // "USD", "subs", "hours" source_range: z.string(), // A1 notation comparison_label: z.string().nullable(), // "vs April" comparison_delta_pct: z.number().nullable(), comparison_delta_direction: z.enum([ "up", "down", "flat", "not_applicable", ]), rationale_one_line: z.string(), // ≤ 24 words }); const MonthOverMonthCallout = z.object({ applicable: z.boolean(), // false if no time column not_applicable_reason: z.string().nullable(), comparison_period_label: z.string(), // "May vs April" headline_one_line: z.string(), // "May was $740 heavier than April" detail_two_lines: z.string(), // the two-line explanation top_contributor_label: z.string().nullable(), top_contributor_delta_pct: z.number().nullable(), source_range: z.string(), }); const NextQuestion = z.object({ question_text: z.string(), // worded as a question reasoning_short: z.string(), // why this is a good next question estimated_seconds_to_answer: z.number(), // server's estimate }); const DashboardPlan = z.object({ dashboard_id: z.string(), sheet_file_id: z.string(), sheet_tab_name: z.string(), generated_at_iso: z.string(), inferred_user_intent: z.string(), // model's read on what the user wants kpi_cards: z.array(KpiCard).length(3), visualisations: z.array(Visualisation).length(2), month_over_month: MonthOverMonthCallout, next_question: NextQuestion, columns_used: z.array(z.string()), // column letters touched rows_considered: z.number(), small_sample_warning: z.boolean(), // true if < 10 rows confidence: z.number().min(0).max(1), read_only_notice: z.literal( "The source sheet was not modified." ), }); type DashboardPlan = z.infer; type ColumnSchema = z.infer; type ColumnAmbiguity = z.infer; ``` ### Common failure modes (and how to avoid them) - Agent picks `gemini-3.5-flash` for the dashboard plan call — that model is **not GA** until June 2026 and will resolve to a deprecated endpoint. Pin `gemini-3.5-flash` explicitly. The Flash-tier model in 3.5 beats the prior Pro tier on the reasoning benchmarks this template needs. - Model invents a `source_range` that does not exist in the sheet — the server validates every `source_range` against the parsed sheet's actual extent (last non-blank row, last non-blank column per tab); on mismatch, the call is rejected and re-requested with an error message listing the valid extent. **Do not silently render an invalid range.** - Model returns more than 3 KPI cards or fewer than 2 charts — the schema's `.length(3)` and `.length(2)` clauses are documentation only inside `responseSchema`. Enforce on the server: drop extras, reject and re-request if there are too few. - Date format silently chosen — if a "Date" column has three formats, the ambiguity resolver MUST set `user_must_confirm: true` unless one format dominates ≥ 95 %. If the model sets it to false on a 60/40 split, the server overrides. - Currency conflation — if the "Amount" column has "$12.00" and "€8.50" and "£5", flag as ambiguity. Do not silently sum them as if they were the same currency. - Header row at row 3 — many real sheets have a title row, a blank row, then headers at row 3. The shape-inference call must detect this and emit `header_row_letter: "3"`. The server re-parses from the detected header row before generating the dashboard plan. - Long sheet truncation — the model receives only the first 200 rows by default. If the sheet has 5,000 rows, the server augments the payload with the last 50 rows plus 50 random samples and an explicit row-count in the system instruction so the model does not assume the sample is the whole sheet. - Free-text "Category" column with 800 distinct values — the model should not group by it without clustering. Detect cardinality; if `distinct_count / total_count > 0.5`, treat as `free_text`, not `category`. - Month-over-month called on a sheet with one month of data — the model must set `month_over_month.applicable: false` and populate `not_applicable_reason` honestly. - Workspace integration short-lived credential expired mid-call — wrap the sheet-read in a retry that requests a fresh per-call credential from AIS Build's Workspace API; do not cache the credential beyond its expiry. - Sheet read times out on a sheet with 1M cells — degrade gracefully: read the first tab, the first 5000 rows, surface a "we sampled the first 5,000 of 47,210 rows" notice in the dashboard footer. - Drive change-watch webhook arrives for a sheet the user has unconnected — silently drop with `200 OK` to keep Drive from retrying; do not 404. ### Negative constraints (hard rules) - Do NOT mutate the source sheet. The Workspace integration's read-only mode is the only mode used. Any attempt by the model to suggest a write (e.g. "I'll add a Totals column") is rejected at the server boundary; the response is re-requested with a stricter system instruction. - Do NOT invent source ranges. Every `source_range` in the structured response must reference a range that exists in the parsed sheet's actual extent. The server validates and rejects. - Do NOT exceed three KPI cards, two visualisations, one MoM callout, one next-question. The dashboard's discipline is load-bearing; more numbers do not help. - Do NOT silently convert units. Currencies are not coerced to USD. Dates are not coerced to a single format. Mixed units in a column raise an ambiguity flag. - Do NOT request sensitive fields the dashboard does not need. If a sheet has columns "Email", "SSN", "DOB", the dashboard plan must not pull them unless they are the load-bearing signal; PII columns get a "PII detected — confirm you want this on the dashboard" prompt. - Do NOT cache the sheet's full contents server-side beyond the active session. The cached parse lives in Firestore scoped to the user, and is wiped on one tap of "forget this sheet". - Do NOT use the user's sheet data to train any model. Use the Gemini API on the paid tier, where Google does not use your content for model training, per the Gemini API Additional Terms. The capabilities-info panel says this in plain English. - Do NOT label uncertain inferences as certain. A 60/40 date format split is uncertain; the UI surfaces a chooser, not a silent guess. - Do NOT propose follow-ups that require writing to the sheet ("Add a column for category totals"). The next-question is always a question, not an action. - Do NOT show "Powered by AI" badges. The intelligence is in the dashboard, not in the marketing chrome. The capabilities- info panel is honest about what runs where. ### Per-call `systemInstruction` strings Use these as the literal `systemInstruction` field for each Gemini API call the built app makes. They complement the series-wide rules already uploaded as the global instructions file (`00-series-instructions.txt`). ### Call: Sheet shape inference → DashboardPlan Model: `gemini-3.5-flash` · thinkingLevel: medium · Tools: (none) ``` You receive a parsed Google Sheet and must emit a DashboardPlan JSON object that answers the most likely question the sheet's owner has about their own data. You receive (in the user contents): - sheet_metadata: file_id, file_title, tab_name, owner_email, last_modified_iso, total_row_count, total_column_count - columns[]: an array of ColumnSchema objects with header, letter, inferred_type, ambiguity_flag, example_values, non_blank_row_count - rows_sample[]: a sampled array of rows (first 50 + last 50 + 50 random samples; up to 200 rows total). Each row is an array of cell strings indexed by column letter. - user_resolutions: any prior ambiguity resolutions the user has confirmed for this sheet (date format, currency, header row). Your task: 1. Infer the most likely question the sheet's owner has. Write it as one short sentence in `inferred_user_intent`. Examples: "Where is the money going?", "Which referral source converts to paid?", "Which SKU is running low?". 2. Choose EXACTLY THREE KPI cards. Each KPI is a single number that answers part of the question. Pick KPIs that: - Are headline-worthy (the user would say "ah" when seeing them) - Use different columns or aggregations (don't show three variants of the same total) - Each carry a source_range pointing at the source cells - Each carry a one-line rationale ≤ 24 words explaining the choice 3. Choose EXACTLY TWO visualisations. Each chart is a Visualisation object with chart_type, source_range, group_by_column_letter, measure_column_letter, measure_aggregation, filter_clauses. Pick chart types from the ChartType enum based on the data shape: - time series with one measure → line or sparkline - categorical breakdown of one measure → single_bar or donut - categorical breakdown over time → stacked_bar or grouped_bar - two numeric columns → scatter - if no chart makes sense, set chart_type to "table_only" for that slot 4. Compute the month-over-month callout. If the sheet has a time column with at least two distinct months of data, fill the MonthOverMonthCallout with the comparison. Identify the single largest delta contributor (category, source, SKU, whatever the breakdown column is). If the sheet has no time column or fewer than two periods, set month_over_month.applicable: false and explain why. 5. Propose EXACTLY ONE next question worth asking. Phrase it as a question the user might actually type ("Show me by week instead of month", "Which source has the highest LTV?", "Exclude the test rows from January"). The question must be answerable from the same sheet — do not propose questions that would require pulling another data source. Hard rules: - Every source_range you emit must be a real A1-notation range inside the parsed sheet's actual extent. The server will validate. Do not invent ranges. - Do NOT propose any action that would write to the sheet. The app is read-only. - If the sheet has fewer than 10 non-blank rows, set small_sample_warning: true. Pick KPIs that make sense for a small sample (don't compute a "median" of 4 numbers). - If a column is flagged ambiguity_flag: true in the columns array, do NOT use it in a KPI or chart's measure_column. You can use it in a filter only if the user has already resolved the ambiguity (look in user_resolutions). - If the sheet has PII columns (Email, SSN, DOB, Phone, full Name combined with other identifying fields), do NOT include them in KPI values, axis labels, or filters unless the user has explicitly confirmed in user_resolutions. - read_only_notice MUST be the literal string "The source sheet was not modified." — the server checks this. - Set confidence honestly. 0.9+ when the data shape is obvious (an expense log with date + amount + category columns). 0.5-0.7 when the data could plausibly be about several things. Below 0.5 when you are guessing. Output ONLY the DashboardPlan JSON matching the provided schema. No commentary. JSON only. ``` --- ### Call: Column ambiguity resolver → ColumnAmbiguity Model: `gemini-3.5-flash` · thinkingLevel: low · Tools: (none) ``` You receive one column that the shape-inference call flagged as ambiguous, along with up to 30 example values from that column. Your job is to enumerate the candidate interpretations and rank them. Common ambiguity types: - date formats: DD/MM/YYYY vs MM/DD/YYYY vs YYYY-MM-DD vs "May 12, 2026" - currencies: "$12.00" (USD), "€8.50" (EUR), "£5" (GBP), "12,00" (locale comma-as-decimal) - casing in a category column: "Groceries", "groceries", "GROCERIES" all meaning the same value - boolean: "Yes/No", "Y/N", "TRUE/FALSE", "1/0" - percentage: "27%", "0.27", "27" For the column you receive, emit a ColumnAmbiguity object: - column_letter, column_header (copy from input) - candidates: an array, each with candidate_label, example_value_from_sheet (verbatim from the sample), frequency_in_sample (0..1) - recommended_candidate_index: the index in candidates of the one you think is most likely correct - user_must_confirm: true unless one candidate has frequency_in_sample >= 0.95 AND is unambiguous in isolation Hard rules: - Every example_value_from_sheet must be copied verbatim from the input. Do not invent example values. - Do NOT auto-resolve a 60/40 split. user_must_confirm: true when the top candidate is below 0.95. - If the column is genuinely free_text (a "Notes" column), return a single candidate with label "free_text" and user_must_confirm: false. Output ONLY the ColumnAmbiguity JSON. No commentary. ``` --- ### Call: Natural-language follow-up → revised DashboardPlan Model: `gemini-3.5-flash` · thinkingLevel: medium · Tools: (none) ``` You receive the current DashboardPlan, the user's plain-English follow-up question, and the parsed sheet. Your job is to emit a revised DashboardPlan that answers the follow-up. Examples of follow-ups you will receive: - "Show me by week instead of month." - "Group by referral source." - "Only Subscriptions." - "Exclude Costco from the breakdown." - "What's the trend on the smallest category?" - "Compare Q1 to Q2 instead of May to April." - "Show me just the top 5 customers." How to interpret follow-ups: - Time bucket changes ("by week", "by quarter") → adjust the visualisations' group_by + the MonthOverMonthCallout's comparison_period_label. - Filters ("only X", "exclude Y") → add to every relevant Visualisation's filter_clauses and recompute the KPIs to match. - New grouping ("group by source") → swap the breakdown column on the visualisations. - A new question entirely ("what's the trend on X") → propose three new KPIs and two new charts focused on X; preserve resolutions the user has already confirmed. - "Show me the rows" / "I want to see the data" → set both visualisations' chart_type to "table_only". Hard rules: - The revised DashboardPlan keeps the same shape: 3 KPI cards, 2 visualisations, 1 MoM callout, 1 next-question. The follow-up does not change the dashboard's discipline. - The next_question MUST be different from the user's just-typed follow-up (do not echo it back). - If the follow-up requires data not in the sheet, return the unchanged plan with inferred_user_intent appended: "(follow-up could not be answered from this sheet alone)". - If the follow-up asks for a write or a sheet edit, refuse — return the unchanged plan with inferred_user_intent appended: "(this app is read-only)". Output ONLY the revised DashboardPlan. No commentary. ``` --- ### Call: Why-this-number rationale Model: `gemini-3.5-flash` · thinkingLevel: low · Tools: (none) ``` You receive one KPI card (label, value_display, source_range, columns used, aggregation) plus the parsed sheet's metadata. Write one paragraph (≤ 60 words) explaining why this metric was chosen. Voice: direct, specific, plain English. No marketing language. Examples of good rationales: - "May spend topped the chart for the year. Sum of column D across rows 81-114 (May 2026 entries). Up from April mainly because of two new annual subscription charges." - "Top category by spend is the headline because Groceries outpaces every other category by more than 2×. Sum of column D where column C equals 'Groceries'." Hard rules: - ≤ 60 words. One paragraph. - Cite the columns by header name (not just letter) and the row count if relevant. - Do not editorialise ("which is impressive", "as expected"). - Do not recommend an action ("you should cut subscriptions"). Output: a single string. No JSON wrapper. No commentary. ``` --- ### Call: PDF / PNG dashboard export composition Model: `gemini-3-pro-image` · n/a · n/a ``` You generate a single 4K-legible one-page poster summarising a dashboard. The poster is suitable for emailing or printing on A4. You receive: - sheet_display_title (for the poster header) - the three KPI numbers + labels - the two chart SVGs (server-rendered from the structured response — you do NOT redraw them, you compose them into the layout) - the MoM callout text - the next-question text (set as a subtle footer aside) - the generation timestamp Composition rules: - A4 landscape, 4K resolution, white background, generous margins. - Header: sheet_display_title in a clean grotesque (Inter or similar), 36pt. Generation date in 14pt muted-grey beside it. - KPI cards: three columns across the top third. Each card has the value in 56pt, the label in 18pt, the comparison delta in 16pt with an up/down arrow. - Charts: two side-by-side in the middle third, embedded as the provided SVGs (you compose them, not redraw them; Nano Banana Pro renders the surrounding typography crisply at 4K). - MoM callout: bottom third, single sentence in 22pt, with the detail in 16pt below. - Footer: the next-question text in 12pt muted-grey, italicised. - Typography is the load-bearing element. Use Nano Banana Pro's post-I/O 4K-legible-text capability — the numbers must be crisp at print size. - No decorative imagery. No background gradients. No emoji. The data is the design. Hard rules: - Do not invent numbers. Render exactly what you were given. - Do not add a "Powered by" footer or any marketing chrome. - Aspect ratio: 16:9 for landscape A4-like output. ``` --- ### Call: Onboarding empty-state illustration Model: `gemini-3.1-flash-image` · n/a · n/a ``` You generate one photographic-looking image for the welcome screen. Subject: a desk at work hours with a laptop showing a Google Sheet, a coffee, a notebook with handwritten figures beside it. Warm afternoon light, no people in frame, no recognisable brand logos on the laptop or notebook. Prompt anchors that work well: - "warm afternoon light through a window, a laptop on a wooden desk showing a spreadsheet with rows of numbers, a small ceramic coffee cup beside it, a paper notebook open with handwritten figures, real worn paper texture, no people in frame, no brand logos visible" Hard rules: - Photographic, not cartoon, not illustration-style. - No people in frame. - No commercial branding visible. - Warm lighting, slight imperfection, real paper texture for the notebook; avoid the glossy AI-render look. - Aspect ratios: 3:2 for hero, 1:1 for empty states. ``` ## 5. Use cases & content to include Build dedicated UI sections or flows for each of these — they tell you what content the app must support. - **The expense log.** Three columns — Date, Amount, Category. Six months of rows. Dates in DD/MM/YYYY and MM/DD/YYYY mixed. Amounts in USD. Categories with inconsistent casing. The dashboard generator infers "where is the money going?" and surfaces: total spend this month, top category, biggest month-over-month mover. The chart picker chooses a stacked- bar of category × month and a sparkline of weekly total. - **The subscriber list.** Six columns — Email, Signup Date, Source, Plan, Country, Lifetime Value. Two thousand rows. The dashboard infers "which source converts to paid?" and surfaces: total subscribers, paid conversion rate, top referral source by paid count. Chart picker chooses a donut of source share + a grouped bar of plan × source. PII detection flags the Email column; the user confirms once that Email is fine to use as a count (not as a display value). - **The inventory sheet.** Five columns — SKU, Name, Units in Stock, Reorder Threshold, Last Sold. Two hundred rows. The dashboard infers "which SKU is running low?" and surfaces: count of SKUs below threshold, units-sold-per-day on the fastest mover, days-of-cover for the closest-to-out SKU. - **The time-tracking log.** Four columns — Date, Project, Hours, Notes. Three months. The dashboard infers "where is the time going?" and surfaces: total hours this week, top project, biggest week-over-week mover. - **The survey-results export.** Twelve columns of mixed question types. The dashboard infers "what did people actually say?" — picks the top three rated dimensions, shows a donut of overall NPS distribution, surfaces the comment column's most common keywords as a small ranked list. - **The donations sheet.** Three columns — Date, Donor, Amount. Eighteen months. Dashboard infers "is giving growing?" and surfaces: total this quarter, repeat-donor share, biggest quarter-over-quarter mover. - **The book-club reading log.** Four columns — Member, Book, Pages Read, Date. Six months across twenty members. The dashboard infers "who's reading the most?" and surfaces: top reader, average pages per member per month, biggest month- over-month jump. - **The CRM mini-pipeline.** Columns — Deal, Stage, Value, Owner, Close Date. The dashboard infers "what's stuck?" and surfaces: pipeline-weighted value, win rate, longest-stuck deal. - **The follow-up flow.** User asks "show me by week instead of month". Dashboard rebuilds with weekly buckets. User asks "only Subscriptions". Dashboard rebuilds with the filter applied. User asks "what's the trend on Dining?". A new view appears with Dining-focused KPIs. - **The ambiguity flow.** A "Date" column has three formats — the resolver surfaces a chooser with one example per format ("2026-05-21", "21/05/2026", "May 21, 2026"). The user picks one. The choice is remembered. - **The "tap a number" traceback.** The user taps "$1,184" in the Top Category KPI. The embedded sheet panel scrolls to the relevant rows; the cells highlight in soft yellow for 4 seconds. - **The auto-refresh.** The user adds a new row in Google Sheets in another tab of the browser. Within 60 seconds the dashboard's KPI cards animate to their new values and the charts redraw. A small "refreshed at 14:02" line appears in the footer. - **The small-sample fallback.** A sheet with 6 rows triggers `small_sample_warning: true`. The dashboard renders a clean table view with a one-line callout: "Six rows is a small sample. Here's what's in the sheet." No chart is forced. - **The PII confirmation.** A sheet with an "Email" column surfaces a one-time prompt: "This sheet has PII. We'll use Email for counting only, not as a displayed value. Confirm to continue." The user confirms; the choice is remembered. - **The share link.** The user shares a read-only dashboard with a colleague via magic link. The colleague sees the dashboard but cannot access the underlying sheet unless the sheet's owner has separately shared it in Google Sheets. ## 6. Page structure Build the following screens / sections in this order. Adjust copy to fit the voice, but keep the structural intent. 1. **Welcome / paste-URL hero.** A photographed-looking shot of a desk with a laptop showing a Google Sheet. One paragraph: "Paste a Google Sheets URL. We'll build the dashboard in twenty-five seconds — without writing a single pivot table." The URL input is the centerpiece. Google sign-in button next to it. Below: "Try with a sample sheet" → loads the demo expense log in section 8a. 2. **Reading-your-sheet state.** A live progress card with five sub-steps: "Connecting to your sheet…", "Reading the first 200 rows…", "Inferring column types…", "Picking the three numbers that matter…", "Drawing the charts…". Each step completes with a check; the whole flow takes ≤ 25 seconds. 3. **Ambiguity flow (one-time).** If any columns are flagged ambiguous, a single modal asks: "Before we draw, two quick questions." Each question shows one example per candidate with a radio selector. "Remember for this sheet" is checked by default. 4. **Dashboard.** Three KPI cards across the top, two visualisations in the middle, the MoM callout below them, the next-question prompt as a single tappable chip at the bottom. Each KPI has an info `(i)` icon → why-this-number rationale. 5. **Follow-up input.** A persistent input at the bottom of the dashboard: "Ask anything about this sheet." Examples cycle in the placeholder ("Show me by week instead of month", "Only Subscriptions", "What's the trend on the smallest category?"). 6. **Embedded sheet panel.** A read-only inline iframe of the source Google Sheet on the right (desktop) or bottom (mobile), toggleable. Tapping a KPI / bar / point highlights the corresponding cells. 7. **Saved dashboards.** Sidebar listing every sheet the user has connected. Each entry shows the sheet title, the tab name, last-refreshed timestamp, and a small sparkline of the first KPI. 8. **Refresh history.** A timeline of the last ten refreshes for the active dashboard. Each entry names what changed in the source sheet ("3 new rows added", "Amount in row 47 edited from $12 to $120", "no detected changes"). 9. **Export view.** Modal with three options: "Download PDF", "Download PNG", "Copy share link". The PDF is composed by Nano Banana Pro for crisp 4K typography. 10. **Settings & privacy.** A panel with: "Connected sheets" (list with one-tap forget); "Ambiguity resolutions" (per-sheet remembered choices, individually editable); "Delete this account" with a 60-second cool-off. Privacy panel restates the not-trained-on policy in plain English. 11. **Footer.** "Read-only. We never modify your sheet." Privacy: "Your sheet data is yours. We never train on it." Capabilities `(i)` icon in header. ## 6b. First-visit onboarding Show a **first-visit onboarding** the first time a visitor lands on the app (detect via `localStorage` flag; do not show on return visits). Three slides, dismissible at any time. Persistent re-entry: a `?` icon in the header reopens it. **Slide 1 — What this is.** - Headline: "Welcome to Spreadsheet Whisperer." - Subhead: "Paste a Google Sheets URL. Get a live dashboard in twenty-five seconds — without writing a single pivot table." - One paragraph (≤ 60 words) explaining the workflow: sign in with Google once, paste a sheet URL, watch the dashboard appear. Three KPIs, two charts, a month-over-month callout, a next question to ask. The sheet is never modified. - Visual: a small annotated illustration of a dashboard with the three sections labelled (KPI cards, visualisations, MoM callout) — not a generic spreadsheet icon. **Slide 2 — Try it now.** - One short prompt: "Try with the sample expense log". - A live demo input pre-loaded with the seed sheet URL from section 8a (a public sample sheet the user can read without signing in). - 1-2 sentences pointing at *the specific page elements* where the Gemini magic happens (the 25-second dashboard build, the follow-up input, the source-cell traceback). **Slide 3 — How to remix this.** - Headline: "Make this yours." - Three short bullets: - "Connect your own Google Sheet — paste any URL after signing in." - "Adjust the prompts in `/server/prompts/` to fit the kind of dashboards your domain needs." - "Wire up your Gemini API key and Firebase project via the env-var list in the capabilities panel." - Primary CTA: "Use this template" → links to AI Studio Build remix entry point. - Secondary: "Just exploring — close" (sets localStorage flag, never auto-shows again). **Accessibility:** focus trap, `Esc` closes, `role="dialog"`, `aria-modal="true"`, `aria-labelledby`, focus restored to trigger on close. Respect `prefers-reduced-motion`. **Don't:** - Don't gate content behind the modal. The page beneath must be fully usable. - Don't auto-reshow on return visits. Use `localStorage['onboarding-seen-v1']`. - Don't include unrelated CTAs (newsletter signup, social follow). Keep it about the template only. ## 6c. Capabilities info button (persistent in header) Add a persistent `(i)` icon in the top-right of the header (next to the primary nav). Click → opens a modal/panel titled **"What powers this app"**. **Panel contents (in this order):** **Gemini capabilities used (the hero list):** - **Gemini 3.5 Flash (post-I/O 2026 default flagship)** — does the load-bearing work: infers the question your sheet is about, picks the three KPIs, picks the chart types, computes the month-over-month callout, suggests the next question. The Flash-tier model in 3.5 beats the previous-generation Pro tier on the benchmarks this template cares about, at ~4× the speed. - **Gemini 3.5 Flash (structured output)** — every Gemini call in this template returns typed JSON conforming to the `DashboardPlan`, `ColumnAmbiguity`, or revised-`DashboardPlan` schemas. The schemas live in the repo. - **Gemini 3.5 Flash (long context, 1M tokens)** — handles sheets up to ~50,000 rows by sampling intelligently. For very large sheets the server samples and summarises older rows to stay under 500k tokens of payload. - **Nano Banana Pro (`gemini-3-pro-image`)** — composes the exported PDF / PNG with 4K-legible typography. The post-I/O Pro image model renders numbers crisply at print size, which the previous-generation image models could not. - **Nano Banana 2 (`gemini-3.1-flash-image`)** — generates the welcome and empty-state imagery. - **AI Studio Build Workspace integration (new at I/O 2026)** — reads your Google Sheets without an OAuth handshake. AI Studio Build issues a short-lived per-call credential when you paste a sheet URL; the app never holds a Drive-wide refresh token, and the scope is one sheet at a time. - **Drive change-watch** — subscribes to file change events on the sheets you connect, so the dashboard refreshes within 60 seconds of an edit. Falls back to 60-second polling on tenants that restrict the watch channel. - **Firebase Auth** — Google sign-in; magic-link email for read-only dashboard share links. - **Firestore** — stores your connected sheets, dashboards, remembered ambiguity resolutions, and follow-up history. Syncs across devices in real time. - **Cost note** — see the detailed breakdown in 6d. A typical dashboard build costs about $0.011 of Gemini API spend; a follow-up costs about $0.009. A heavy-use month (50 dashboards, 200 follow-ups) is well under $2. - **Privacy note** — your sheet contents are private to you and any user you explicitly share a dashboard with. This app uses the Gemini API on the paid tier, where Google does not use your content for model training, per the Gemini API Additional Terms. The cached parse lives in your Firestore project and is wiped on one tap of "Forget this sheet". **Backend services this app depends on:** - Auth: see section 4b (Google sign-in only in v1) - Database: see section 4b - Storage: see section 4b (optional, for PDF / PNG exports) - Workspace integration: see section 4b — the post-I/O 2026 hero capability - Email: see section 4b — magic-link sharing requires the sender domain to be authorised in Firebase Auth. - Payments: see section 4b (not used in v1) - External APIs: see section 4b **Environment variables you'll need to configure:** - `GEMINI_API_KEY` — your Google AI Studio API key - `FIREBASE_PROJECT_ID` — your Firebase project id - `FIREBASE_SERVICE_ACCOUNT` — service-account JSON (server-side only) - `DRIVE_WATCH_WEBHOOK_TOKEN` — a random secret used to verify Drive change-watch callbacks - `MAGIC_LINK_SENDER_DOMAIN` — the authorised domain for share emails **Cost + privacy notes:** - Dashboard build calls average ~7,000 input tokens and ~900 output tokens on Gemini 3.5 Flash → ~$0.019 per build (at $1.50/M input, $9.00/M output). Cached input ($0.15/M) brings refreshes after the first build down to ~$0.011. - Follow-up calls average ~4,500 input tokens and ~700 output tokens → ~$0.013 per follow-up; ~$0.009 with cached input. - One short paragraph on privacy: where the data lives (your Firebase project), how to delete it (Settings → "Forget this sheet" — gone in 60 seconds; "Delete this account" — gone in 60 seconds), what is never sent for training. **Documentation links:** - AI Studio Build docs - Gemini 3.5 Flash multimodal + structured output + long- context docs - Nano Banana Pro (Gemini 3 Pro Image) docs - Workspace integration without OAuth (AIS Build, I/O 2026) - Drive `changes.watch` docs - Firebase Auth, Firestore docs **Accessibility:** same standards as the onboarding modal — focus trap, `Esc`, ARIA, restored focus. **Behaviour:** - Always available — single click from anywhere in the app. - Tooltip on the `(i)` icon: "How this app is built". - Mobile: opens as a full-screen sheet that slides up. - Should be the most honest part of the app — never hand-wave service requirements; never say "AI" without naming the specific Gemini model and capability. ## 6d. Detailed cost breakdown (deployer reads this BEFORE shipping) - **Sheet shape inference → DashboardPlan (Gemini 3.5 Flash, medium thinking)** — a 200-row sheet sample plus the column schema plus the system instruction averages ~7,000 input tokens and ~900 output tokens. At Gemini 3.5 Flash pricing ($1.50/M input, $9.00/M output) that is ~$0.019 per first build. With cached input ($0.15/M), refreshes after the first build come in around ~$0.011 each. - **Column ambiguity resolver (Gemini 3.5 Flash, low thinking)** — small input (one column's example values), ~1,200 input tokens and ~300 output tokens → ~$0.0049 per resolution. Called only once per ambiguous column, then remembered. - **Natural-language follow-up parser (Gemini 3.5 Flash, medium thinking)** — ~4,500 input tokens (sheet sample + current DashboardPlan + follow-up) and ~700 output tokens → ~$0.013 per follow-up. ~$0.009 with cached input. - **Why-this-number rationale (Gemini 3.5 Flash, low thinking)** — small input, ~800 input + ~150 output → ~$0.002 per call. User-driven; called only when the info icon is tapped. - **PDF / PNG export (Nano Banana Pro, `gemini-3-pro-image`)** — ~$2 input / $12 output per 1M tokens; one A4-landscape export ≈ ~$0.04 per file. - **Welcome / empty-state image (Nano Banana 2)** — ~$0.025/image. Generated once per app install (cached client-side), or once per regenerate-empty-state action. - **Expected per-dashboard cost (across full lifecycle):** ~$0.05 — one initial build at ~$0.019, two ambiguity resolutions at ~$0.010, three follow-ups at ~$0.027, two why-this-number taps at ~$0.004, one PDF export at ~$0.04 if exported. - **Heavy-use month (50 dashboards, 200 follow-ups, 20 PDF exports):** ~$0.95 + ~$2.60 + ~$0.80 ≈ ~$4.40 total. - **Firestore parse storage:** ~$0.18/GB/month for stored docs; a parsed sheet (first 200 rows + column schema + plan history) averages ~25 KB ≈ ~$0.0000045/sheet/month — effectively free for personal use. - **Drive change-watch:** free; subject to Drive's quota of 10,000 push notifications per user per day, which is far in excess of what this app needs. ## 7. Design language - **Mood:** A clean, fast working surface. Not a spreadsheet clone. Not a SaaS dashboard with "by the numbers" tiles. The desk at 14:02 on a Wednesday when the user just wants to know where the money went in May before getting back to the rest of the afternoon. - **Typography:** A clean grotesque for app chrome and data labels (Inter or Geist). A display grotesque slightly heavier for KPI numbers (Geist Display or Inter Display) — the numbers are the headline, and they should be readable at small sizes. A monospace (Geist Mono or JetBrains Mono) only for the embedded sheet panel's cell values. - **Palette:** Paper-white background `#FAFAF8` for the dashboard surface, deep ink `#0F0F12` for body text, KPI- accent violet `#5B4FD9` for the primary KPI value, soft mint `#3CA17A` for "up" deltas, soft coral `#D04A5A` for "down" deltas, neutral grey `#8C8C94` for muted labels, highlight yellow `#FFF1B8` for the source-cell flash. Avoid bright primary blues that pull the eye away from the data. - **Imagery:** Photographic for the welcome screen only. Inside the app, no decorative imagery — the data is the design. - **Hand-feel touches:** The dashboard appears one element at a time during the build (KPI 1, KPI 2, KPI 3, chart 1, chart 2, MoM callout, next-question chip) with a 100 ms stagger — the user reads the numbers as they arrive, like a Polaroid developing. The source-cell highlight fades in over 200 ms and fades out after 4 seconds with a soft yellow wash. - **Spacing:** consistent 4-px base. Generous whitespace — the KPI cards need air to feel like headlines. - **Radius:** consistent token set (e.g. 8 / 14 / 22 px). KPI cards use 14; the source-cell highlight uses 6; the welcome card uses 22. - **Shadows:** subtle, layered. Avoid heavy drop shadows. - **Motion:** purposeful — entrance fades, hover lifts, the dashboard build stagger. Respect `prefers-reduced-motion`. The "numbers animate to new values" on auto-refresh is the canonical reduced-motion check — verify it jumps instead of tweening. - **States:** every interactive element has hover, focus, active, disabled. Loading uses skeletons that match the eventual layout, not spinners. Empty states have helpful next-action guidance ("Paste a Google Sheets URL above to begin"). ## 8. Content generation rules - Write **realistic, specific copy**. NO Lorem Ipsum. NO generic placeholders like 'Your tagline here'. - Invent plausible sample sheets and KPI values that fit the job (use the seed content in section 8a as a starting point). When inventing, lean on realistic spreadsheet patterns — expense categories, signup sources, SKU codes — but never invent fake brand names or fictional companies that suggest endorsement. - Tone: direct, useful, free of corporate language. This template is for a working person, not a SaaS prospect. - Headlines: punchy and concrete. No 'Empower your X' filler. No 'Revolutionize'. No 'Seamless'. No 'AI-powered'. - Body copy: short paragraphs (2-4 sentences). Use lists where appropriate. - Plain language. Avoid jargon — except where the user already speaks the jargon ("MoM", "YoY", "NPS", "LTV" in the right contexts). - Where the app outputs AI-generated content, never label it as "AI says" — let it speak naturally. Use small uncertainty cues only where epistemic honesty requires them (a small- sample warning, a low-confidence chip on a chart). ## 8a. Seed content (use these specific examples) Anchor every generated copy + sample data point in the concrete content below. **Sample sheets the demo lineage exposes:** - **"Personal Expenses 2026"** — Date, Amount, Category. 187 rows across Jan–May 2026. Categories: Groceries, Dining, Subscriptions, Transport, Utilities, Health, Misc. Dates in mixed DD/MM/YYYY and MM/DD/YYYY (this triggers the ambiguity flow). Amounts in USD. - **"Newsletter Signups Q1"** — Email, Signup Date, Source, Plan, Country. 1,420 rows. Sources include Twitter, Newsletter Referral, Direct, Google, ProductHunt, Reddit. - **"Workshop Inventory"** — SKU, Name, Units in Stock, Reorder Threshold, Last Sold. 142 rows of small-batch workshop items. - **"Volunteer Hours — Spring"** — Date, Volunteer, Project, Hours. 412 rows from a community garden's spring season. - **"Donations 2024-2026"** — Date, Donor, Amount, Recurring Y/N. 720 rows across two and a half years. **Sample dashboard in detail view (the demo expense log):** - Sheet display title: "Personal Expenses 2026" - Inferred user intent: "Where is the money going this year?" - **KPI 1:** "May spend — $4,213" · comparison "vs April" delta "+$740" direction "up" · source_range "Sheet1!B81:B114" · rationale "Sum of column B for rows where column A is in May 2026." - **KPI 2:** "Top category — Groceries — $1,184" · comparison "vs April" delta "+$112" direction "up" · source_range "Sheet1!A2:C187" · rationale "Sum of column B where column C equals 'Groceries' across the full sheet." - **KPI 3:** "Subscriptions up 27 % vs April — $612" · source_range "Sheet1!A2:C187" · rationale "Largest month- over-month mover by percent change among the seven categories." - **Chart 1:** Stacked bar — "Spend by category, last 6 months" · x = month · y = sum of Amount · group_by = Category · source_range "Sheet1!A2:C187" · rationale "Category share per month surfaces where the budget actually goes." - **Chart 2:** Sparkline — "Weekly spend, last 12 weeks" · x = week · y = sum of Amount · source_range "Sheet1!A2:C187" · rationale "Pace check — are weekly spending bursts speeding up or slowing down?" - **MoM callout:** "May was $740 heavier than April. The gap is mostly Subscriptions and Dining — two new annual subscription charges and four extra dinners out." · top contributor "Subscriptions" · delta_pct "+27 %" - **Next question:** "Show me by week instead of month, only Subscriptions." **Sample ambiguity resolution (the date column flow):** - Column letter: "A" · header: "Date" · ambiguity_reason: "two date formats observed (DD/MM/YYYY and MM/DD/YYYY)" - Candidates: "DD/MM/YYYY" (example "21/05/2026", frequency 0.62) · "MM/DD/YYYY" (example "05/21/2026", frequency 0.38) - recommended_candidate_index: 0 · user_must_confirm: true **Sample follow-up flow (rebuild after follow-up):** - User types: "show me by week instead of month, only Subscriptions" - Revised inferred_user_intent: "How is Subscriptions spend trending week over week?" - New KPI 1: "This week — $148"; KPI 2: "Last 4 weeks — $612"; KPI 3: "Active subscriptions this month — 9". - New Chart 1: Line of weekly Subscriptions spend, last 12 weeks. New Chart 2: Donut of which specific subscriptions contributed in the latest week. - New MoM callout: "This week was $42 heavier than last week. Two annual charges landed on the same day." - New next_question: "What's the trend on the smallest subscription line?" **Sample voice copy:** - Welcome: "Paste a Google Sheets URL. We'll build the dashboard in twenty-five seconds — without writing a single pivot table." - URL placeholder: "https://docs.google.com/spreadsheets/d/…" - Reading-your-sheet steps: "Connecting to your sheet…" / "Reading the first 200 rows…" / "Inferring column types…" / "Picking the three numbers that matter…" / "Drawing the charts…" - Ambiguity prompt heading: "Before we draw, two quick questions." - Ambiguity prompt body: "Your 'Date' column has two formats. Which one is yours?" - KPI info tooltip placeholder: "Why this number?" - Source-cell flash caption: "Cells in your sheet that contributed to this number." - Follow-up placeholder: "Ask anything about this sheet." - Small-sample warning: "Six rows is a small sample. Here's what's in your sheet." - PII confirmation: "This sheet has an Email column. We'll use it for counting only, not as a displayed value. Confirm to continue." - Read-only assurance (in the footer): "Read-only. We never modify your sheet." - Auto-refresh toast: "Refreshed — 3 new rows added in your sheet." ## 9. Media & assets - **Hero image (landing screen):** A photographed-looking shot of a wooden desk at golden hour, a laptop in the centre showing a Google Sheet with rows of numbers, a small ceramic coffee cup beside it, a paper notebook open with handwritten figures. Generate via Nano Banana 2 with a prompt emphasising "warm afternoon light through a window, a laptop on a wooden desk showing a spreadsheet with rows of numbers, no people in frame, no brand logos visible, real worn paper texture, soft shadow under the laptop". - **App icon / wordmark:** Set in the display grotesque. Slightly worn paper texture behind it. No icon — just type. - **Empty-state illustration:** A simple line drawing of a spreadsheet grid with a sparkline emerging from it. Hand- drawn aesthetic, not a flat icon. Generate once at build time via Nano Banana 2 (`gemini-3.1-flash-image`), 1:1 WebP at 1024×1024, prompt: "single hand-drawn ink line illustration of a small grid of empty rectangular cells (six by four), with a thin hand-drawn sparkline curve rising out of the top-right corner of the grid, off-white paper background, slight pen imperfection, no shading, no colour fill, no text, no commercial branding". Ship as a seed asset at `/public/samples/empty-state-grid.webp`. - **Chart visuals:** Server-rendered SVGs from the structured response, NOT model-generated images. The chart_type field drives a deterministic renderer. - **Exported PDF / PNG:** Composed by Nano Banana Pro per the system instruction. A4 landscape, 4K-legible typography. - **Stock fallbacks:** If image generation fails, fall back to the photographed sample desk from `/public/samples/sample- desk.jpg` (3:2 WebP, 2048×1365 — ship as a seed asset; recreate via Nano Banana 2 (`gemini-3.1-flash-image`) with the prompt: "photographic wooden desk at golden hour, a laptop in the centre showing a generic spreadsheet UI with rows of numbers — no real brand logos, no real company name — a small ceramic coffee cup beside it, a paper notebook open with handwritten figures, soft warm window light, no people, real worn paper texture, soft shadow under the laptop"). Never to a generic spreadsheet emoji. - **Generated imagery:** prefer Nano Banana 2 over stock photography. Prompt for warmth and slight imperfection — avoid the glossy 'AI render' look. - **Optimisation:** WebP/AVIF, `loading="lazy"`, explicit `width`/`height` to prevent layout shift. - **Icons:** `lucide-react` for UI. Use sparingly — never decorative-only. ## 10. Interactivity & states - Every interactive element has hover, focus, active, and disabled states. - Forms validate inline and show specific error messages (not "Invalid input"). "That URL doesn't look like a Google Sheets link — paste the URL from your browser's address bar when the sheet is open." is the right shape. - Loading states use skeletons that match the eventual layout, not spinners. The dashboard's KPI cards and chart frames appear as skeletons during the 25-second build. - Empty states explain the next action with a button whose label fits THIS app's domain: "Paste a Google Sheets URL", "Connect a sheet", "Ask a follow-up" — never a generic "Get started". - Smooth scroll for in-page anchors. - All AI-generated content streams in element-by-element where supported (the KPI cards arrive one at a time as the DashboardPlan parses), with a clear "thinking…" indicator before content starts arriving. - If an AI call fails, show a calm, specific error ("The sheet's first column has values we couldn't classify. Want to pick a column type, or send us the sheet ID for a closer look?") and offer retry. - Source-cell traceback: tapping a KPI / bar / point fades a soft yellow wash onto the corresponding cells in the embedded sheet panel for 4 seconds, then fades out. - Auto-refresh: when a Drive change arrives, the KPI numbers animate to their new values over 400 ms (or jump under `prefers-reduced-motion`). The chart redraws with a fade. - A small inline notice appears for any small-sample, insufficient-history, or PII-detected case, not a modal that blocks the dashboard. - The follow-up input is debounced — typing does not fire a call; pressing Enter does. ## 11. Tech & responsive requirements - **Stack:** React + TypeScript + Tailwind CSS. Functional components + hooks. Use Shadcn UI primitives where appropriate. Chart rendering via Recharts or Visx (both work; Visx gives more control over the source-cell traceback highlight animation). - **Build runtime:** AI Studio Build — full-stack with Cloud Run server-side functions. All Gemini API calls happen server-side; API key lives in Secrets Manager, never in the client bundle. Free Cloud Run deploy from AIS Build (one of the two free deploys allotted per account post-I/O 2026). - **Model selection:** explicitly pin `gemini-3.5-flash` for dashboard plan / follow-up / ambiguity / rationale calls; `gemini-3-pro-image` (Nano Banana Pro) for the PDF / PNG export; `gemini-3.1-flash-image` (Nano Banana 2) for the welcome / empty-state imagery. Set `thinkingLevel` explicitly per call. - **Database:** Firestore (auto-provisioned by AI Studio Build). Show the seed lineage on first launch. - **Auth:** Firebase Auth — Google sign-in only in v1, because the Workspace integration uses the same identity to read sheets. - **Workspace integration:** Wire via the AIS Build Workspace Sheets capability (declared in the AIS Build config; the exact capability identifier — e.g. `workspace.sheets` — should be verified against the live AIS Build reference, as the integration is still rolling out post-I/O 2026). AIS Build issues a short-lived per-call credential when the user pastes a sheet URL; the app never holds a Drive-wide refresh token. The scope is one sheet at a time. - **Drive change-watch:** A Cloud Run function subscribes to `drive.changes.watch` for each connected sheet's file ID. The function validates incoming push notifications against `DRIVE_WATCH_WEBHOOK_TOKEN`. Falls back to 60-second polling on tenants that restrict the watch channel. - **Auto-refresh transport:** Firestore real-time listeners on the active dashboard view, so when the Cloud Run refresh job writes new KPI values, the browser updates without a manual refresh. - **Embedded sheet panel:** Google Sheets's official embed iframe URL, scoped to a single tab. Source-cell highlighting uses Google Sheets's `range` parameter to scroll to the highlighted range; the highlight wash is overlaid by the app's own SVG since the embed doesn't expose cell-level DOM. - **Mobile-first.** Verify layouts at 375 px (iPhone SE), 768 px (iPad), 1024 px, 1440 px+. On mobile, the embedded sheet panel becomes a bottom sheet that the user can drag up. - Use `clamp()` for fluid typography. Prefer container queries over media queries for component-level responsiveness. - Use `dvh` / `svh` instead of `vh`. Respect safe-area insets on iOS. - Zero horizontal overflow at any width. Zero layout shift on load. - Persist user data in Firestore. Use real-time listeners on the active dashboard view (so a refresh in the Cloud Run job appears in the browser instantly). - Optimistic UI on follow-ups; reconcile on response. ## 12. Accessibility (WCAG 2.2 AA) - Semantic HTML — `header`, `nav`, `main`, `section`, `article`, `footer`. - All interactive controls reachable by keyboard with a visible focus ring. - Color contrast ≥ 4.5:1 for body, 3:1 for large text and UI components. The mint "up" and coral "down" delta chips both exceed 4.5:1 on the paper-white background. - All images have meaningful `alt` text. The welcome image alt: "A wooden desk at golden hour with a laptop showing a spreadsheet and a small ceramic coffee cup beside it." - Form fields have associated ``s; errors announced via `aria-describedby`. - All modals: focus trap, `Esc` to close, `role="dialog"`, `aria-modal="true"`, `aria-labelledby`, restore focus to trigger on close. - Respect `prefers-reduced-motion`. The dashboard-build stagger and the "numbers animate on auto-refresh" are the canonical reduced-motion checks — verify they jump instead of tweening. - Tap targets ≥ 44 × 44 px on touch. - The dashboard exposes a "read this dashboard as a list" alternative view for screen-reader users: each KPI, each visualisation's data points, and the MoM callout are rendered as semantic lists with proper headings. - The embedded sheet panel exposes the source range as `aria-label` ("Source cells: A2 to D114") on tap, so screen-reader users hear which cells are highlighted without needing to see the wash. - The follow-up input announces "Updated dashboard" via `role="status"` and `aria-live="polite"` when the revised DashboardPlan arrives. - The ambiguity resolver modal uses radio groups with proper `fieldset` / `legend`, so screen-reader users hear "Date format — DD/MM/YYYY, example 21/05/2026 — 62 % of values" as one announcement. - The PII confirmation prompt uses `role="alert"` so it is not dismissable until acknowledged. ## 13. Quality bar — avoid AI clichés Do **not** ship any of the following unless the design explicitly calls for it: - Hero with a gradient background and floating geometric / orb shapes. - A row of 4 'by the numbers' stat cards below the hero ("47 sheets connected, 1,420 dashboards, 0 OAuth handshakes"). - Emoji bullets in body copy. No 📊. No 📈. No 🔍. - 'Lorem ipsum' or 'Your headline here' placeholders. - A pricing table with three identical tiers when the product has no real pricing. - Faux testimonials with stock-photo headshots. - Hero CTA labelled 'Get started' with no second action. - "AI" or "Powered by AI" badges plastered everywhere. The intelligence is in the dashboard, not the marketing. - Loading spinners on AI calls — show meaningful skeletons + the labelled five-step build progress instead. - Toast notifications for routine actions (save, delete) — use inline confirmation instead. - Decorative spreadsheet iconography in app chrome — no grid emoji, no chart-stack wallpaper. The user's data is the decoration. - A "Powered by Gemini" badge — the capabilities-info panel names the models honestly; the app chrome stays plain. - Animated number tickers on the KPI cards — the numbers animate only when their actual value changes (on auto- refresh). No vanity counting-up on first render. - Any feature that gamifies dashboards — no streaks, no points, no badges. The user wants the answer, not a reward. Aim for the polish of a hand-crafted production app — specific, considered, with real domain knowledge in the copy. A reviewer should not be able to tell this came from a template. ## 14. Deliverables A single working app I can preview immediately, with: - All sections populated with realistic seed data (see section 8a) — the demo lineage includes Personal Expenses 2026 (187 rows), Newsletter Signups Q1 (1,420 rows), Workshop Inventory (142 rows), Volunteer Hours — Spring (412 rows), Donations 2024-2026 (720 rows). - All AI features wired to real Gemini API calls (server-side) with the per-call matrix respected exactly — `gemini-3.5- flash` for plan / follow-up / ambiguity / rationale, `gemini-3-pro-image` for export, `gemini-3.1-flash-image` for welcome imagery. - Workspace integration wired via the AIS Build `workspace.sheets` capability — no OAuth handshake required. - Drive change-watch wired via a Cloud Run function with webhook-token verification; 60-second polling fallback. - All interactive states implemented (loading, empty, error, small-sample warning, PII confirmation prompt, source-cell highlight wash). - Onboarding modal working on first visit (and accessible via `?` icon thereafter). - Capabilities info button working in header (`(i)` icon → panel) — fully populated with post-I/O 2026 model names, costs, privacy notes, read-only assurance. - Auth flow working with Google sign-in (only path in v1). - Firestore persistence wired up with security rules that scope dashboards and connected-sheet metadata to owner + invited share-link recipients. - Sheet-read end-to-end from a pasted URL, with the parsed result feeding the DashboardPlan call. - Source-range validation on the server: every `source_range` in the structured response is checked against the parsed sheet's actual extent; invalid ranges trigger a re-request. - Follow-up loop working end-to-end — typing a question rebuilds the dashboard via the revised-DashboardPlan call. - Source-cell traceback working: tapping a KPI / bar / sparkline point scrolls and highlights the corresponding cells in the embedded sheet panel. - PDF / PNG export working via Nano Banana Pro composition. - Share-link flow working: read-only dashboard sharing without changing the source sheet's permissions. - Responsive across the four viewport breakpoints (375, 768, 1024, 1440+). - Zero TODOs, zero console errors, zero placeholders.