Google Sheets to SQL in One Transaction: A Full E-Commerce Migration Demo
1,686 records, 12 planted data defects, one all-or-nothing import: how a 10-tab retail workbook becomes a clean relational database. Explore the live demo.
A fictional specialty-coffee retailer — “Roast & Ritual Coffee Co.” — runs its entire operation in a 10-tab Google Sheets workbook: catalog, variants, inventory, orders, order items, customers, suppliers, purchase orders, stock counts, activity log. The whole migration of that workbook into a normalized SQL database, defects included, is public and explorable at demo.kamensky.dev.
TL;DR
| The problem | Retail operations in Google Sheets rot predictably: duplicated customers, drifting stock counts, overwritten historical prices, no audit trail. |
| What the demo is | A deterministic, 100% reproducible migration: 10 spreadsheet tabs → 12 clean, linked tables, with a report accounting for every cleaning decision. |
| The key numbers | 1,686 source records → 1,634 imported · 16 duplicates resolved · 18 values normalized · 3 rows quarantined · 8 stock corrections · 435 audit events. |
| The hard parts | Merging the same customer entered two ways (12-order history reunited); keeping a $54 promo price while the catalog says $59; recomputing stock from movements when the sheet says 45 but the math says 35. |
| The pattern | Analyze → Map → Clean → Validate → Import, executed as one all-or-nothing import — it either fully succeeds or fully rolls back. |
| Key takeaway | A spreadsheet migration is not a copy-paste job; it’s an audit. If your migration report can’t account for every row, you didn’t migrate — you moved the mess. |
You’ll see what actually breaks when a business outgrows Sheets, how each defect class is detected and resolved with exact counts, and what the resulting operations app looks like. If you’re earlier in the journey, start with When Google Sheets Stops Scaling; if you’re planning a production database migration, pair this with the case study: AppSheet to SQL.
The Problem
The demo workbook is deliberately realistic, because every spreadsheet-run business converges on the same four failure modes:
- Customer fragmentation. The same customer gets entered under different
spellings across orders —
Marcus Vancein one tab,Marcus V.in another. Lifetime value and order history split into two identities, and neither is right. - Inventory recount drift. A static “Inventory” tab is a photograph, not a ledger. Sales and restocks land in disconnected tabs; someone forgets to update the count; the sheet drifts from reality one missed edit at a time.
- Historical facts get overwritten. When a catalog price changes, a naive sync “fixes” old order lines to match — silently rewriting your accounting.
- Zero audit visibility. Sheets can’t answer who changed this price, when did the last recount happen, and why.
The Migration, Stage by Stage
The pipeline runs five stages, and — this matters — the import runs as one all-or-nothing step. There is no state where half the workbook is migrated:
| Stage | What it does |
|---|---|
| 1. Analyze | Inspects sheet shapes, nullability, and detects dirty anomalies before touching anything. |
| 2. Map | Applies explicit column-to-entity mappings — nothing is inferred by vibes. |
| 3. Clean | Executes the cleaning rules with exact count tracking: every duplicate, normalization, and quarantine is counted. |
| 4. Validate | Asserts record-count contracts, link integrity, and six “hero facts” that must survive the migration verbatim. |
| 5. Import | Single-transaction load; builds the inventory-movements ledger and activity log. |
The migration report it emits is the part most home-grown transfer scripts never produce:
| Metric | Count |
|---|---|
| Source records | 1,686 |
| Imported successfully | 1,634 |
| Duplicates resolved | 16 (14 customers, 1 supplier, 1 order item) |
| Values normalized | 18 (7 SKUs, 6 variants, 5 emails) |
| Quarantined for review | 3 (ambiguous orders — never silently dropped) |
| Stock corrections | 8 (manual sheet ≠ movements ledger) |
| Historical prices preserved | 1 |
| Activity events imported | 435 |
The 12 Defects Every Migration Hits
The workbook ships with twelve planted defects (E1–E12), each one drawn from a real migration. A sample of what the pipeline has to catch:
| ID | Legacy state | Rule applied | Result |
|---|---|---|---|
| E1 | Marcus Vance (C-023) and Marcus V. (C-071), same email | Merge by normalized email; keep longest legal name; re-link orders | One customer with a 12-order lifetime history |
| E3 | ORD-1042 line says $54, catalog says $59 | Preserve order_items.unit_price as a historical fact | Promo price kept; catalog untouched — two numbers, both true |
| E4 | Inventory sheet says 45 units of the flagship SKU | Recompute stock from movements: +120 purchased, −85 sold | Live stock is 35; the sheet had drifted +10 |
| E9 | Supplier typed as “Horzion Coffee Importers” | Alias/fuzzy resolution to the canonical supplier | Restock linked to the right entity |
| E12 | Blank phones, empty reserved cells | Empty values stay truly empty, never dummy zeros | Honest data, no fake placeholders |
The remaining seven cover SKU typos (ETH-yir-wb-1000), retyped product names where
a SKU reference should be, missing walk-in customer emails, casing chaos
(WHOLE BEAN / 1KG vs ground / 250g), a double-pasted line item, and a
discontinued SKU ordered years later — imported and flagged, because deleting
history is worse than keeping it.
The E4 case deserves a note, because it’s the one that surprises people: the spreadsheet wasn’t wrong the day it was written. It drifted. Three purchase orders in (+120 bags) and fifteen orders out (−85), the true stock is 35 — the sheet’s 45 is the residue of missed manual updates. That’s why the target structure stores movements, not counts: stock is always computed, never hand-typed.
Explore the Live Demo
Everything above is browsable at demo.kamensky.dev — a full-featured operations app running on the migrated database (not read-only):
- Dashboard & Analytics — KPIs computed from the clean, linked tables, not pasted from a sheet.
- Data Entry & Permissions — authenticated user roles with granular permission limits and live data input.
- Inventory Ledger — stock computed from the movements ledger, with the purchased/sold/computed math visible per SKU.
- Customer 360 — open Marcus Vance: 12 orders across both legacy identities, merged into one timeline.
- Orders, products, suppliers, purchases — full directories with detail pages per order, SKU, and supplier.
- Notifications & Integrations — automated operational alerts and ready for external integrations.
- Migration report — the exact audit table above, rendered from the run.
The data is fictional (it’s a demo), but deterministic: every regeneration starts from the same fixed source data — the same 12 defects, the same 1,686 records, every time. That’s what makes the migration provable rather than “probably fine”.
Is Your Workbook at Risk?
The demo defects are planted; yours accumulated organically. This calculator scores how close your workbook is to the failure modes above — plug in your own numbers:
Interactive Google Sheets Risk & ROI Assessor
Assess your spreadsheet health score and estimated weekly wasted time.
Running your store out of a spreadsheet?
The demo uses SQLite (an embedded database) to stay self-contained — the same pipeline pattern is how I migrate production businesses to structured relational databases, with shadow runs and zero-downtime cutover. If your workbook shows the signs, that’s the conversation to have.
More on this: the related Google Sheets articles and the AppSheet to SQL migration case study, and When Google Sheets Stops Scaling.