The invoice came to £2,442. The VAT line said £917,785.
Nothing had crashed and nothing had logged an error. Every step of the code had done exactly what it was designed to do. The template wanted a VAT rate, the spreadsheet had no column called that, and my column matcher chose the best candidate it could find: a column of unit prices. It multiplied, and produced a neat, well-formatted PDF that would have been sent to somebody's customer.
That's the kind of bug this post is about. Parsing a spreadsheet is the easy part. The hard part is that when it goes wrong, the parser doesn't fail. It succeeds, and a wrong number ends up in a document that looks fine.
What I'm building
BroadPaper Cloud turns a spreadsheet into branded PDFs: a monthly client report, an invoice, or forty statements at once. The user uploads a file, picks a design and downloads the result. Nobody checks row 37 of the output, so row 37 has to be right.
Here's what went wrong on the way, and the rules that came out of it.
1. A close second is still a question, however high it scores
The VAT bug came from fuzzy matching. Each spreadsheet column gets a score against each field in the template, and if the best score is high enough, the matcher goes ahead without asking.
The problem was that several money columns scored almost the same. When three candidates are within a hair of each other, picking one without asking for confirmation is a guess.
The fix: confidence depends on how far the winner is ahead, not just on its score. If the best match leads the runner-up by less than 0.15, the user is asked, however well the best one scored.
The second fix was that no choice is ever locked in. Every match keeps its runners-up, so a wrong choice can be changed later. A choice saved last month that doesn't fit this month's file carries a warning.
I then over cooked it, and the screen started asking about everything, even for a spreadsheet whose headings matched the template's field names exactly. That's a failure too. People who are asked ten questions stop reading them, which is worse than never being asked. The question has to be saved for a genuine tie.
2. INV-10241 is not −10241
My first number parser removed everything that wasn't part of a number, so it could handle £1,234.56 and 1,234 USD. It also turned INV-10241 into -10241.
The whole column was then typed as negative integers and printed, right-aligned, under the heading "Invoice number".
The rule now is that a currency symbol or a currency code is decoration. Any other letter means the value isn't a number.
Digits alone aren't proof either:
// `0044` is a code, and a number would lose the zeros in the document.
// `07700 900123` parses once the space goes, and prints as 7700900123.
const leadingZeros = strings.some((s) => /^0\d[\d ]*$/.test(s));
Headings help, with care. "VAT" on its own is money. "VAT number" is a registration number that has to print exactly as it was written.
3. The date that changed month halfway down a column
Invoice date
03/04/2026
15/04/2026
28/04/2026
If you parse each cell on its own with a flexible date parser, the first row can come out as 4 March and the others as April. Every value parses and the result looks plausible, but one date is a month out.
So a column's type is decided once, by a vote across every value in it. It's only a date column if one date format explains at least 95% of the values. Then every row is read the same way. The 15/04 in row two proves the whole column is day-first. If no value goes above 12, the column really is ambiguous, and the user is asked.
Once the question is settled, the dates are converted to ISO format (2026-04-03) on the way in. Nothing later in the pipeline gets a chance to decide differently.
4. 12,5 became 125
The next version of the parser removed commas so that 1,234 would parse. But in much of Europe, 12,5 means twelve and a half. It became 125: ten times too large, in a money column, with nothing on screen to show it.
A comma now counts as a thousands separator only between groups of three digits:
"1,234" // 1234
"£1,234.56" // 1234.56
"12,5" // not a number
"1.234,56" // not a number
The last two could be handled with a locale setting. I decided not to guess. A column that can't be read with confidence comes in as text, and a comment in the parser explains why:
// Text is the only type that cannot make a document wrong.
A column wrongly typed as a number prints a wrong figure. A column left as text prints exactly what the person typed. The worst that can happen is that the user has to tell you it's money.
The pattern
Every one of these bugs had the same cause: a guess at the data type or format, which nothing downstream could see. The fixes all follow from that:
- Decide each column's type once, across every value, never cell by cell.
- When the evidence is weak, fall back to the type that can't print a wrong figure, which is text.
- When two answers are close, ask the user, and ask only then.
- Keep every automatic decision reversible.
One architecture decision made all this easier: files are parsed in the user's browser, in a Web Worker, and the server only ever receives typed JSON. The server has no workbook parser, so there's no zip bomb or XML exploit to worry about. It still checks every value against its column's type, because a client that can send JSON can send anything.
When you don't need any of this
If your data comes from your own database, it already has types, so use them. This is for data a person typed into a spreadsheet, which is still where most small businesses keep their numbers.
Try it
The free document pages let you fill in and download a single invoice, receipt or delivery note without an account:
broadpaper.com/templates.
BroadPaper Cloud does the same for a whole spreadsheet:
broadpaper.com.
What's the worst thing a spreadsheet has done to your code? I'm fairly sure I haven't found them all ..... yet
Top comments (4)
"The parser doesn't fail, it succeeds" is the scariest class of CSV bug, and the margin-over-runner-up rule is a smart fix for it.
One more guard that would have caught the VAT case even if matching went wrong: a sanity bound per field type, checked after mapping. A VAT rate outside 0-30% (or a VAT amount bigger than the net total) is almost never real. Same idea for dates outside a sane range, or quantities with decimals in a "units" column. These checks don't care how the column was chosen, so they catch mistakes from the matcher, from a saved mapping that went stale, and from a user who clicked the wrong option.
When a bound trips, showing the rows that broke it ("row 12: VAT 917,785.00 on net 2,442.00") tends to get a faster fix than a generic warning.
Thanks, this is a good point, and something that I went back and forth on.
The difficulty is that the service has no idea what's in somebody's spreadsheet. All it knows is what the template's fields are called. Generic bounds would trip on things that are perfectly real, like 7.5 hours in a quantity column, a zero-rated invoice, or a date years ahead on a contract. And a warning that fires on real data trains people to dismiss warnings, so the one that actually matters is dismissed. That's the same trap as asking too many questions.
Where I think it does work is when the template knows what a field means and declares a range for it, like a VAT rate on an invoice template. Then it isn't a guess, and when it trips, your "row 12: VAT 917,785 on net 2,442" is exactly the right way to say it. So: not a general check as such, but something a template author can opt into. Definitely worth investigating though 🙂
Fair. 7.5 hours and a zero-rated invoice are exactly the false alarms that teach people to click past warnings. Template-declared ranges are the right home for absolute bounds.
There's one kind that doesn't need the service to know anything, though: checks between fields rather than against fixed limits. "VAT amount ≤ net amount" or "line total ≈ qty × unit price (within rounding)" hold for any invoice template that has those fields, zero-rated included. They only fire when two mapped columns disagree with each other. That was the actual shape of the £917,785 bug: the numbers were fine alone and impossible together.
The other thing that stays generic is drift against the user's own last good run. If a column mapped to "VAT rate" was 0-20 last month and is 45-900 this month, that's worth one question. You're not claiming it's wrong, just that the file changed shape under a saved mapping. It also covers the stale-mapping case you mentioned without any domain knowledge.
How do you preserve the original cell text alongside the typed JSON when a user needs to fix one ambiguous value without re-uploading the file?