work / automation / payroll-based invoicing engine

Headcount billing, built straight from payroll.

Clients were billed by headcount from a sheet rebuilt by hand each cycle. I wrote a script that builds it from payroll data, matching the manual version exactly.

role
Sole automation engineer
client
Canadian accounting firm
status
Validated

Languages

JavaScript (Apps Script)

Tools & platforms

Google Apps ScriptGoogle Sheets

AI & LLM

to build it
ClaudeChatGPTPerplexity

Skills

ScriptingData validationDiagnosticsMatching legacy output
207 × 44

rows × columns, identical to the manual sheet

01// how I approached it

The thinking behind it

  1. 01 · What I noticed

    Billing was rebuilt from payroll by hand, and any change risked a wrong invoice.

  2. 02 · What I decided

    Script it, then prove it: match the manual sheet exactly before anyone relies on it.

  3. 03 · What I delivered

    Output identical to the manual original: 207 rows by 44 columns.

02// old vs new

Old system vs new system

Old systempeople as the glue
  1. Export payroll
  2. Count heads per client
  3. Apply rates by hand
  4. Check totals twice
New systemthe system does the work
  1. Payroll runs
  2. Script builds the sheet
  3. Diagnostics explain any difference
  4. Ready for invoicing
03// how it works

Watch it run

Click through the four steps. In step 3, click any box to see what it does.

synthetic data · no client screens
Reconstruction with synthetic data · no client screens
04// the hard part

Matching the manual sheet exactly.

Billing errors cost trust, so close was not good enough. The script had to reproduce the manual sheet exactly (207 rows by 44 columns) before anyone relied on it, with diagnostics that explain any difference.

05// key decisions

Why it's built this way

decision 1

Prove it with an exact match

The new output was compared cell by cell with the manual version.

Trade-off: more time spent validating.
decision 2

Diagnostics, not guesses

When numbers differ, the script explains why.

Trade-off: extra code to maintain.
decision 3

Payroll as the single source

Billing is built from payroll data, not retyped.

Trade-off: payroll data has to be right.
06// lessons

What made it work

  1. 01

    Trust comes from matching the old way

    People adopt automation when it agrees with what they know.

  2. 02

    Explain differences automatically

    Diagnostics turn "it looks wrong" into a quick fix.

  3. 03

    Build from the source

    Copying numbers between sheets is where errors start.