Spreadsheet Automation and Reporting Workflows
Weekly reporting meant hours of copy-paste between spreadsheets, and formulas broke whenever someone added a column. Numbers arrived late, and nobody fully trusted them.
Industry
Professional services business with a lean operations team
Role
Systems consultant — workflow audit, Apps Script automation, documentation and handover
Impact
What took a full afternoon of copy-paste now takes a few minutes of review, and the report ships at the same time every week.
01 - Problem
The problem
The team's weekly numbers lived across several Google Sheets, one per area, each owned by a different person. Every week someone spent an afternoon copy-pasting ranges into a master report. That master referenced fixed column positions, so the moment anyone inserted a column, renamed a header, or reordered a tab, totals broke visibly or — worse — shifted silently and reported wrong numbers. Errors surfaced days later, in a meeting, in front of the people the report was meant to inform.
When I audited the workflow, the problem wasn't effort — it was structure. There was no agreed shape for the source data, so no automation could survive contact with it. Before writing a line of Apps Script, I defined what the data should look like and made the system enforce it. Structure first, workflow automation on top.
02 - System Flow
How the system moves
Weekly trigger fires
Time-driven Apps Script trigger
Source data pulled
Script reads each source sheet
Structure validated
Headers checked against schema
Data consolidated
Rows normalized into staging
Summaries rebuilt
Script regenerates summary views
Owner notified
Status email with any flags
Quick human review
Minutes, not an afternoon
03 - Build
What I built
Scheduled data refresh
A time-driven Apps Script trigger pulls each source sheet into a staging area on schedule, so nobody copy-pastes. The script maps data by header name, not column position — the one change that made the workflow survivable. Each run writes to staging first and replaces live data only after a clean pull, so a failed refresh never leaves the report half-updated.
Structure validation that fails loudly
Before any data moves, the script checks each source sheet against the expected schema: required headers present, no unknown columns, values in the right shape. If a column was added or a header renamed, the refresh halts and emails the owner exactly what changed — instead of producing a quietly wrong report. This is the step most people skip, and it's what makes the rest trustworthy.
Auto-generated summaries and a handover runbook
Summary tabs that were hand-assembled now rebuild by script every run, so the weekly job is reading the report, not building it. I left short docs: how the workflow runs, how to add a column to a source sheet, and what to do when validation flags something. The team operates and modifies these internal tools without me — the real finish line.
04 - Impact
Business impact
Weekly reporting dropped from a full afternoon of copy-paste to a few minutes of review.
Broken-formula surprises stopped appearing in meetings — structure changes get caught at refresh time with a clear message, not discovered in front of the team.
Trust in the numbers came back: the report is assembled the same way and ships at the same time every week.
The team can safely change their own source sheets, with a documented process and a validator that catches mistakes.
05 - Edge Cases
What had to be handled
A column is added, renamed, or reordered in a source sheet — the validator matches by header name, flags anything missing or unexpected, and halts before a wrong report is built.
A source sheet is empty or mid-edit when the trigger fires — the script checks for a minimum of rows before replacing anything, then skips that source with an alert rather than overwriting good data with blanks.
A refresh runs twice in one day — data lands in staging and is deduplicated by key before summaries rebuild, so reruns never double-count.
The trigger fails silently, e.g. a quota or auth hiccup — every run logs to a status tab, and a separate check emails the owner if no successful refresh has happened by a set time.
06 - Improvements
What I would improve next
Move consolidated data out of Sheets into a small proper data store as volume grows. Sheets is right at this scale but has a ceiling, and the staging structure was built to make that migration straightforward.
Add a reporting dashboard on top of the cleaned data so leadership reads one operations dashboard instead of raw tabs.
Push validation upstream — data validation rules and protected ranges in the source sheets — so bad data is harder to type in the first place, not just caught later.