Build log: Spreadsheet to system

This is a demo project. Fernhill Home Cleaning, its owner, and its customers are fictional. We invented them so we could show the work without showing anyone’s real data.

Almost every small business has one: the workbook that started as a customer list and is now the whole business. Our first sample had to start there.

The problem

We wrote a script that produces nine months of a cleaning company’s records the way a busy owner types them, between cleans. The file is called Fernhill jobs & invoices 2026 FINAL (2).xlsx. It has about sixty households, 1,042 rows on the Jobs tab, 616 on Invoices, subtotal rows in the middle of the tables, a hidden column, and a tab called “OLD - dont use”.

The mess is the realistic kind. Dates arrive as 3/4, March 4th, 4-Mar-26, and 03.04.2026. Amounts arrive as numbers, as 120-, as TBD, and as see note. Payments live only in a free-text “Paid?” column: Y, pd, pd cash 3/2, check bounced!!, comp'd, a note about a partial Venmo payment, or nothing at all. Yellow fill means “partly paid”, and only the owner knows that.

Nobody can say who owes what without reading every row. Reminders go out when the owner remembers.

What we built

A small web app that the workbook moves into, once:

An import report that says exactly what happened to the file: 60 customers (two duplicates merged), 1,038 jobs, 615 invoices, and 590 payments.
A “Needs your review” list for the few things a person should decide, each with the row it came from and two buttons.
Searchable, sortable tables for customers, jobs, and invoices, with partial payments recorded properly. An invoice that was partly paid by Venmo now shows what was paid, how, and what is still owed.
A Today screen for the morning: the day’s cleans, and who owes what.
Reminders that send themselves. Every morning the app finds invoices 3, 10, and 21 days late and writes a friendly, a firmer, and a final reminder, once each. In the demo they land in an Outbox, so you can read exactly what the customer would get.

Decisions and trade-offs

Never block, and never change anything silently. We set this rule before writing any import code. Every problem goes in one of three piles, and every pile is counted on the report.

Fix it, and count it. If there is only one sensible reading, we just do it. 4-Mar-26 is March 4th. 331.555.0194 and +1 331 555 0194 are the same phone number. On this import that pile holds 114 dates, 253 amounts, 40 phone numbers, and 369 invoice numbers.
Make a best guess, and flag it. When a person really should decide, we save our best guess so the app still works, and put it on the review list. The Customers tab has “Jennifer Park”, “Jen Park”, and further down “PARK, JENNIFER”. Jennifer Park and PARK, JENNIFER share a phone number (typed two different ways) and an email address, so we merged them and counted it. Jen Park has a different phone and the same address. Probably the same customer with a work cell, but “probably” isn’t good enough to merge someone’s billing history. So the list says: “Jennifer Park (row 10) and Jen Park (row 50) may be the same person,” with Merge and Keep separate.
Skip it, and say so. Subtotal rows, blank rows, the “OLD - dont use” tab, a scratch “Sheet3”. The report lists each one.

The import ends with 15 items to review out of more than 1,700 rows. That is a morning’s coffee, not a weekend.

An invoice with an open question gets no reminders. Left to the plain rules, the app would have sent a final reminder for invoice 1287, 118 days late. Its “Paid?” cell says comp'd, and its note says “redo - missed upstairs bath”. The owner gave that clean away. An automatic email demanding payment for an apology clean is how software loses a business its customers. So the app doesn’t chase money the owner hasn’t confirmed. Once she clicks Looks right, or records a payment, the next morning’s check treats it normally. The same rule covers the bounced check: that should be a phone call, not an email.

What surprised us

Our design said to flag every invoice we linked to its jobs by date alone. On this workbook that would have been 207 review items, and nobody reads 207 items.

When we looked closer, we found that when the cleans in the window add up to exactly the invoice amount, the link was right in every case we checked. So those became a counted fix: 248 jobs “matched to their invoice by date and amount”. Only links that don’t add up stay on the list, like invoice 1332, where the cleans total more than the amount billed.

Fewer flags turned out to be better flags. A short list gets read; a long one gets ignored.

What an owner gets

One place for customers, jobs, invoices, and payments, which several people can use at once.
An import she can check, with every fix counted and every guess shown.
Reminders that go out on time, and stay quiet when they should.
The workbook as an export, not the source of truth.
Two guides: one for her, in plain words, and one for whoever maintains the app next.

With a real business, a few things would change, because the decisions would be the owner’s, not ours:

We would read the real file together first. An hour with the owner explains things no import can: that yellow means partly paid, that “comp’d” means a free redo, which customers have an arrangement.
We would do a trial import before the switch. Run it on a copy, walk through the report and the review list together, adjust the rules, then run it for real on a quiet day.
We would turn on real email last. Reminders start in an Outbox the owner can read. Only when the wording and timing feel right do we connect a real email service.

You can see the screens on the sample page. If your business runs on a workbook like this one, that’s exactly the project we like, and you can book a free call to talk it through.