Automatisation des rapports et de la facturation
The whole chain was manual. It now fits in a single checkbox.
At this agency, closing a case file triggered a run of manual steps: produce the report, send it to the right client, then archive it. Repeated file after file, that chain consumed administrative time without adding anything to the investigation work itself.
- Category
- Business automation
- Platforms
- Google Workspace
Where it started
Some details of this project are confidential and are not shown here.
The agency has two distinct user groups: investigators, who record the case files, and administrative staff, who send the reports and track billing. A tool already existed, built by a previous contractor, but it had grown fragile — and it copied every report to an address outside the agency, a real confidentiality problem on investigation files.
The whole chain had to be automated without changing the tool the teams use daily, and without ever exposing client pricing to the investigators — an explicit business constraint. On top of that sat an invisible problem: reports were reaching no client hosted on Microsoft 365, and nobody knew why.
What was built
I took over the existing tool and rebuilt its core around the spreadsheet the agency already uses. Marking a file “complete” is now enough: the PDF report is produced from a template the agency can edit itself, sent to the right recipient with the attachment, and the row is archived. A second part, isolated in a separate workbook only management can open, picks up archived files with their category, price and billing status already filled in — no re-keying.
What the product does
Report, dispatch and archive in one action
Marking a file complete produces the PDF, emails it to the client and archives the row. What took several steps now takes one.
Batch processing
A selection or every completed file at once: an administrative backlog clears in one go rather than file by file.
Checks before sending
An incomplete file is refused and the missing fields listed back. No malformed report ever reaches a client.
Stalled files flagged
A file untouched for more than eight days is flagged in red when the file opens. Nothing stalls silently.
Billing kept away from investigators
Prices live in a separate workbook only management can open. Not a hidden tab — a file investigators have no access to.
Categories and prices pre-filled
Category is derived from the client, price from the research types — summed when there are several. Management gets its data without re-typing it.
Steering dashboard
Revenue by category, by status and by month, with a year selector, refreshed on every change.
Automatic year rollover
The new financial year’s tab is created with its headers, formatting and dropdown lists already in place.
The decisions that mattered
Reports that never reached certain clients
No client hosted at Microsoft was receiving their reports. The code ran without error, the sending account was correct, and no rejection was reported — so the problem was invisible from inside the tool. Tracing it back to the headers of the received messages revealed the cause: mail was being signed with a generic key instead of the agency’s own domain key, because domain authentication had never been configured. Three DNS records fixed a problem nobody had been able to name.
Isolating prices physically rather than hiding them
The constraint was that investigators must never see prices. A hidden tab would have given the illusion of security — a hidden tab is two clicks from being unhidden. Billing data therefore lives in a separate file with its own access rights: write for the account running the process, read for management, nothing for investigators. The separation is real, not cosmetic.
An architecture dictated by the client’s rules, not by technique
The agency’s domain is governed by a policy that blocks unverified applications with no way to override it — and the agency is not the administrator of its own domain. Some functions would have required permissions that policy refuses. The answer was to split the process into two scripts so it uses only native triggers and lightweight permissions. It is not the most elegant design on paper; it is the one that actually runs at this client.
Absorbing years of free-text entry
The history contained composite values, typed by hand over the years. Rather than a mass clean-up — risky and irreversible — the processing was made tolerant: non-blocking validation and splitting on several separators. The tool adapts to the data that exists instead of demanding that it be perfect.
Making the tool editable without a developer
Everything liable to change — identifiers, columns, value lists, colours, statuses — sits in one place, and columns are located by their letter, never by their position. That is the direct answer to the previous tool’s main flaw: it broke as soon as a column was reordered. The report template is a document the agency edits itself, without touching any code.
How it fits together
Tableur de suivi
Investigators record their case files there, as before
Traitement automatique
A file marked complete triggers report, dispatch and archiving
Classeur confidentiel
Billing data, accessible to management only
The technologies used
Frontend
- Google Sheets
Backend
- Google Apps Script
- JavaScript
Infrastructure
- Google Workspace
- Google Drive
Services
- Google Docs
- DKIM / SPF / DMARC
What was delivered
The “file complete → report → dispatch → archive” chain, previously entirely manual, now runs from a single action. Billing tracking exists for management without any price being visible to investigators, and the deliverability problem that stopped some clients receiving their reports is resolved. The project shipped with an audit of the previous tool, an installation guide and a handover document. No time-saving figure is published: it was not measured.
- Google Workspace
Need a similar product?
Let’s talk about what you want to build. I reply personally, usually within 24 hours.