PUZZLE #147

CleanFlow · n8n Chaotic Spreadsheet Cleaner

💡

Architectural Clue & Core Principle

"Treat the spreadsheet like a damaged data source." Do not attempt to fix or balance corrupted intern formulas in-place. First inspect table boundaries, extract only the three valid transactional streams (Fleet Fuel, Office Morale, Operational Expenses), normalize fields into canonical schemas, and write a fresh master ledger to Google Sheets.

Cleaned Transactions
29
Across 3 normalized domains
Reconciled Spend
£2,785.48
Exact net financial balance
Input VAT Recovered
£471.36
Zero-rated confectionery isolated
Junk Rows Purged
51
Kettles, ghost rows & 'vibes'
Data Integrity
100%
Audit-compliant schema
Interactive n8n Canvas Visualizer (Click any node to inspect code & output)
✉️
Gmail Trigger
gmailTrigger
→
📄
Extract from File
extractFromFile (xlsx)
→
{ }
Code in JavaScript4
Boundary Inspector
→
🔀
If / Router
n8n-nodes-base.if
→
⛽
Code in JavaScript
Fuel & Fleet Cleaner
🍪
Code in JavaScript1
Cookie & Morale Cleaner
💳
Code in JavaScript2
Expense Normalizer
→
🔀
Merge
append (3 inputs)
→
{ }
Code in JavaScript3
Deduplicate & Schema
→
📊
Create sheet
Google Sheets (append)

Gmail Trigger

Listens for new emails with spreadsheet attachment

n8n-nodes-base.gmailTrigger
// Polls inbox for unread messages containing chaotic Excel file { "filters": { "q": "has:attachment filename:xlsx" }, "pollTimes": { "item": [{ "mode": "everyMinute" }] } }
Corrupted Workbook: 01KVYZT1AZJYC35BAX1M61AHDY.xlsx
Spreadsheet Crime Scene (Damaged formulas, negative typos, kettle in fleet)
Valid Fleet Stream Data
Valid Cookie / Morale Stream Data
1

Raw Ingestion & Fault-Tolerant Parsing

The original workbook contains invalid XML characters (such as ? in sheet titles) that break standard spreadsheet libraries. The n8n extraction node reads raw binary buffers directly without failing on workbook metadata corruption.

- Sheet Title: "OUTPUTS??? maybe" (Crashes openpyxl/Excel XML)
+ Raw Buffer Extraction: Ingests all sheets into JSON rows
2

Boundary & Category Detection (Code4)

Rather than repairing broken formulas across 5 overlapping tables, Code in JavaScript4 scans rows and extracts 3 dedicated slices: Fuel (Rows 6–16), Cookies (Rows 9–18), and Expenses (Rows 38–48). Non-transactional noise (Panic Scores, Toaster outputs) is rejected.

- Noise Rejected: "Machine: toaster -> Output: banana"
+ Categorized Streams: { _stream: 'fuel' | 'cookies' | 'expenses' }
3

Parallel Stream Normalization (Code, Code1, Code2)

Each category executes domain-specific cleaning rules: date standardizer parses Tue? 17 to 2026-06-17, negative costs (-2.49) are rectified using known fuel tank capacities, snack names are cleansed, and UK VAT rules (20% for fuel/ops, 0% for confectionery) are applied.

- Raw Date: "Tue? 17" | Cost: "£-2.49" | Cookie: "Jaffa (not cookie fight me)"
+ Clean Date: "2026-06-17" | Amount: £76.50 | Description: "Jaffa Cakes (20 pk)"
4

Deduplication & Schema Harmonization (Merge + Code3)

The 3 streams converge into an append Merge node. Code in JavaScript3 purges flagged duplicate entries (such as Row 15's "duplicate maybe"), sorts all items chronologically, and maps every record into a consistent 11-column tabular structure.

- Fragmented Schemas with missing headers & duplicate rows
+ Unified Schema: [Transaction ID, Date, Category, Amount, VAT, ...]
5

Production Google Sheet Export

Writes the 29 verified transactions into the Google Sheet master ledger. Reconciles exact spend to £2,785.48 and recovered VAT to £471.36.

- Original State: Unbalanced disaster ("Grand Disaster diff: -£5,101.37")
+ Destination: Google Sheets "Cleaned Master Ledger" (100% Reconciled)
ID Date Category Description Qty / Vol Amount (£) VAT (£) Paid By Payment Receipt Notes
Action completed