A8 Scoping Tool

The Problem

Aptitude 8, a growing HubSpot Partner Agency, had a scoping problem. Solutions Architects were spending hours on every proposal -- not on scoping the work itself, but on wrestling with Google Sheets formatting, copying data into documents, and praying no one accidentally exposed hidden pricing tabs to clients.

The reality was messy: 15+ Google Sheets files with complex formulas, hidden rows containing internal calculations, and constant permission anxiety. Every proposal sent risked exposing internal data. Every scope change during delivery meant more spreadsheet gymnastics.

They needed something that felt like a real CPQ tool, but built on infrastructure they already had -- no new subscriptions, no migration headaches.

The Solution

I led the development of a custom CPQ platform that transforms Google Sheets data into three distinct client-facing applications, all powered by Google Apps Script.

Key design decisions:

• Google Sheets as the source of truth. SAs continue working where they're comfortable -- the tool just presents it beautifully.
• Zero-permission sharing. Clients see branded proposals via URL, never the underlying spreadsheet.
• Three-domain architecture. Sales proposals, delivery change orders, and internal dashboards -- each optimized for its audience.
• Flexible display modes. Move from Draft to Hide Financials to Show Ranges to Final Presentation as conversations progress.

System Architecture

The platform uses a multi-page web app architecture with three distinct domains:

1. Sales Page (CPQ Tool)

Client-facing proposal generator that transforms the SalesInput sheet into branded, interactive proposals.

• 6 display modes from Draft through Archived
• Two-tier hierarchy: Customer Stories grouped by Workstreams
• Collapsible sections with hours and investment rollups
• PDF generation via Google Docs API (zero external costs)
• Rich text support including hyperlinks in assumptions

2. Delivery Page (Change Order Tracker)

Client-facing scope evolution tracker using a ledger-based approach to hours.

• Ledger accounting: Each row is a transaction (positive or negative hours)
• Visual badges: "Sold", "Story Revision", "New Story" indicators
• Change order summary: OG scope vs. Current scope with cumulative tracking
• Automated workflows: Prep sheets, formula application, URL generation

3. Dashboard Page (Internal Metrics)

Internal delivery dashboard for project managers and leadership.

• Change order tracking and health metrics
• Project resource visibility
• Performance analytics

Features at a Glance

• Proposal Generator -- Transform spreadsheet data into branded proposals with one click
• Display Mode Control -- 6 modes from Draft to Archived for different sales stages
• Change Order Tracking -- Ledger-based system shows exactly how scope evolved
• PDF Export -- Native Google Docs generation, no third-party APIs
• Automated Prep -- One-click transition from sales to delivery sheets
• AI Research Assistant -- Claude-powered scope research for customer stories
• Custom Menu System -- All operations accessible from Google Sheets toolbar

Tech Stack

Layer	Technology
Frontend	HTML, CSS, JavaScript (native web app)
Backend	Google Apps Script
Database	Google Sheets (SalesInput, DeliveryInput, Config sheets)
Hosting	Google Apps Script Web App
PDF	Google Docs API
AI	Claude API (Scope Research Assistant)

The beauty of this stack: zero server costs, zero new subscriptions, and deployments that take 30 seconds. Everything runs on infrastructure the agency already pays for -- Google Workspace.

Development Approach

This project grew organically over several months of focused iteration. Three things made that possible:

1. Domain expertise. Working inside an agency meant I understood exactly what Solutions Architects and delivery teams needed -- no external requirements gathering required.

2. AI-assisted development. Using Claude for rapid iteration let me move from idea to working feature fast. The "vibe coding" approach -- describing intent and refining output -- dramatically accelerated development.

3. Incremental architecture. Started with basic proposal display, then added display modes, then delivery tracking, then automated workflows. Each layer built on proven foundations.

The build progressed iteratively: core proposal rendering first, then configurable display modes, then PDF generation, then the complete delivery page rebuild with ledger-based tracking and automated prep workflows.

Results

For Solutions Architects:

• Proposals that took 60+ minutes of formatting now generate instantly
• No more copy-pasting between Sheets and Docs
• Confident sharing without permission anxiety

For Delivery Teams:

• Clear change order history visible to clients
• Automated transitions from sales to delivery
• Formulas that just work, even when columns move

For Clients:

• Professional, branded proposals instead of spreadsheet exports
• Transparent scope evolution during delivery
• Mobile-friendly viewing anywhere

For Leadership:

• Structured data enables retrospective analysis
• Consistent proposal quality across all SAs
• Foundation for service library improvements

Benefits to Aptitude 8

• Time savings -- SAs focus on scoping, not document formatting
• Risk reduction -- No hidden sheet exposure, no permission issues
• Client experience -- Professional proposals increase deal confidence
• Transparency -- Change order tracking builds client trust
• Scalability -- System handles team growth without additional tooling costs
• Data quality -- Structured input enables future analytics and optimization

What I'd Do Differently

If I were starting over, I'd consider a few refinements:

Earlier abstraction of the orchestration layer. The multi-page routing pattern that now cleanly separates Sales/Delivery/Dashboard domains would have been valuable from day one.

More aggressive formula validation. The header-based column detection system that makes everything resilient to reordering was a late addition -- building that pattern in from the start would have prevented several debugging sessions.

Push notifications for change orders. Currently clients revisit the delivery URL to see updates. A lightweight email notification when change orders are processed would close the loop.

But honestly? The iterative approach worked. Building what was needed when it was needed, rather than over-engineering upfront, kept the project moving and the feedback loops tight. Sometimes the best architecture is the one that emerges from real use.