Picksheet Automation
Modernising weekly production planning from manual workbook refreshes to automated cloud delivery. As order volumes, meal variations, and operational rules grew, I rebuilt the workflow around cloud execution, central database-driven data, automated validation, Google Drive delivery, and Slack visibility — giving the entire team one reliable source of truth every week.
The problem
The workflow had outgrown spreadsheets.
What started as an effective workbook-based process became increasingly difficult to maintain as the business expanded. The challenge was no longer just generating a PickSheet; it was producing consistent weekly outputs without depending on specific staff members or manual intervention.
Workbook refreshes depended on people
Weekly production planning relied on Excel workbooks, Power Query refreshes, local machine state, and staff completing early-morning steps correctly before the business day began.
Multiple paths to the same output
More people needed access to the weekly PickSheet, but outputs could vary depending on who refreshed which workbook, when it was refreshed, and whether the underlying data had already updated.
Growth created new rule complexity
More customers, meal variations, ordering combinations, and production rules had emerged over time. The workbook process had simply outgrown the assumptions it was originally designed around.
Business rules moved out of operational workarounds
The original workbook process was created when the business was operating at a different scale. Over time, additional ordering patterns, production requirements, and operational rules were introduced. To keep outputs accurate, manual handling and additional checks gradually accumulated around the existing workflow.
The result was a process that relied increasingly on operational knowledge rather than a single, centralised system.
The modernisation project moved those rules into a cloud-based workflow where they are applied consistently, validated automatically, and maintained in code rather than through manual intervention.
Today, the workflow generates the weekly PickSheet from operational database data, applies production rules automatically, validates the results, and publishes a single output set for the entire business.
Validation examples
| Scenario | Rule | Outcome |
|---|---|---|
| Unsupported delivery day | Stop before output generation | Blocked |
| Missing SKU reference | Prevent partial output generation | Blocked |
| Duplicate SKU mapping | Prevent incorrect quantity allocation | Blocked |
| Invalid date window | Stop before database processing | Blocked |
From individual refreshes to one cloud-run workflow
The previous workflow relied on Power Query connections inside Excel workbooks. That meant the quality of the output depended on people refreshing the right files, at the right time, on the right machine, with the source data already available.
The rebuild removes local workbook refreshes from the critical path. Cloud Scheduler starts Cloud Run, the job reads Azure SQL through read-only queries, applies rules in code, runs validations, generates the weekly PickSheet, uploads the output set to Google Drive, and notifies Slack.
The team no longer needs to refresh workbooks. They select the week's data and work from the same generated output set.
✅ Picksheet Automation — 2026-05-22 to 2026-05-28
Status: success
Counts
Drive
- •Google Drive run folder ↗
- •Generated PickSheet ↗
- •Data Summary ↗
- •Missing SKU report ↗
- •Run manifest ↗
One generated output set for the whole team
Each run produces a timestamped set of files in Google Drive. The PickSheet is the primary output, with supporting summaries and validation reports alongside it. The format stays familiar for buying, production, packing, and operations teams, but generation is now centralised and repeatable.
Supporting files show the run summary, validation results, missing SKU checks, and generation details. If a question comes up later, the team can inspect the exact output set for that week instead of guessing which workbook was refreshed.
Every run creates a new timestamped subfolder inside the configured Drive location. Previous runs are never overwritten — so if a question comes up about a particular week, the original output is still there.
Solution overview
Five steps. One source of truth. No local refresh dependency.
Schedule run
Cloud Scheduler starts the weekly generation process before the team needs the files for production planning, buying, packing, and operations.
Read operational data
Cloud Run uses parameterised read-only queries against Azure SQL so the PickSheet starts from the operational database, not from locally refreshed workbooks.
Apply business rules
Production rules, date-window handling, validation checks, and edge-case logic run in code so the workflow can evolve without increasing workbook complexity.
Validate outputs
The job checks for invalid date windows, unsupported days, duplicate SKU rows, missing SKU references, and other conditions before publishing files.
Publish and notify
Generated files are uploaded to a weekly Google Drive folder and Slack posts the run outcome, counts, and direct links. Everyone works from the same output set.
Business impact
From manual administration to a reliable business system.
Time savings
Before
Staff prepared weekly outputs manually through workbook refreshes, often early in the morning before production planning could move.
After
The scheduled cloud workflow generates the output set automatically, reducing recurring administration and reliance on individual staff members.
Operational reliability
Before
Outputs depended on workbook state, refresh timing, and whether each person was working from the latest available data.
After
One database-driven generation process creates one consistent PickSheet output for the whole business.
Scalability
Before
New ordering combinations and production rules made workbook logic harder to maintain over time.
After
Rules live in code where they are visible, testable, and easier to extend without adding more workbook complexity.
Business continuity
Before
Weekly production planning depended on local files, manual intervention, and knowledge held by a small number of people.
After
Cloud execution, retained historical runs, Slack visibility, and Drive delivery make the workflow easier to operate and audit.
Built-in safeguards
Safe by design. Not by assumption.
Multiple independent layers — not a single point of trust.
Read-only enforced in code, not just permissions
A word-boundary regex blocks INSERT, UPDATE, DELETE, DROP, EXEC, MERGE against any query string before it executes — independent of what the database login is permitted to do.
Date window guard fires before the DB connection opens
Any window wider than seven days raises an exception before a single SQL query is attempted. The diagnostic override flag is not wired into the Cloud Run image.
Four validation gates before output is written
Duplicate SKU rows, unsupported delivery days, invalid output days, and missing SKU references all exit with code 2 before any file is touched.
Output generation is SKU-strict
If the base PickSheet contains duplicate SKU rows — itself a data quality signal — the workflow raises an error immediately rather than silently applying values to the wrong row.
Zero retries — intentional
Cloud Run is configured with zero automatic retries. Retrying a failed run before investigation could double the database query load on a read-heavy job. Failures are investigated first.
Query logging never surfaces customer data
Every SQL loader logs dataset name, row count, column list, date window, and duration. Order-level or customer-level data is never printed at any log level.
Technologies used
Have a spreadsheet process the business has outgrown?
Let's modernise it into a workflow with central data, reliable outputs, and visibility from day one.