Manual Changes Automation System

Overview

This project delivers an end-to-end automation system that replaces manual logistics changes with a structured, controlled workflow.

Customer service teams submit “manual changes” through a Google Sheet, which are automatically validated, processed, and executed via the Route4Me API. Each request becomes a deterministic action with clear outcomes, audit logging, and real-time Slack feedback.

The system addresses a core operational challenge:

How do you allow non-technical staff to perform complex logistics updates without introducing risk, inconsistency, or ambiguity?

The problem

Previously, logistics changes were handled manually inside Route4Me. This introduced several issues:

• High risk of human error when modifying routes and orders
• Inconsistent handling of similar requests across staff
• Lack of visibility into what changes were made and why
• No reliable audit trail for debugging or accountability
• Operational bottlenecks as volume increased

Additionally, requests from customer service often contained ambiguity, requiring interpretation rather than execution.

What I built

1) Input layer — structured Google Sheet

Customer service interacts with a controlled spreadsheet where:

• Each row represents a single action
• Inputs are standardised using dropdowns and enforced formats
• A “Ready for Submission” flag triggers processing

The sheet enforces:

• Strict field structure (no free-form ambiguity)
• Separation between operational data and driver-facing notes
• One action per row (no mixed intent)

Google Apps Script enhances usability by:

• Automatically stamping timestamps
• Tagging entries with AM/PM operational context
• Preventing invalid submissions (e.g. missing change type)

2) Processing layer — Python automation engine

A modular Python system runs on a schedule and processes new rows.

Core responsibilities:

• Read new submissions from the sheet
• Validate required fields (fail fast on invalid input)
• Route logic based on change type
• Execute API actions
• Return structured results

Design approach:

• Deterministic logic (no assumptions or guesswork)
• Config-driven mappings for flexibility
• Fail-safe behaviour (NEEDS_REVIEW for invalid cases)
• Single-responsibility handlers per change type

3) Execution layer — Route4Me integration

The system integrates directly with Route4Me to:

• Create new orders
• Modify existing stops
• Move stops between routes
• Insert stops in optimal sequence positions

Key behaviours include:

• Route lookup using email + delivery date
• Fallback order creation when a destination is not found
• Sequence placement using standard logic or do_at_drop
• Marketplace-specific handling (CMP, DMP, Wine, fruit logic)

4) Output layer — Slack + audit logging

Every action produces two outputs:

Slack notifications

• Real-time operational feedback
• Clean, human-readable summaries
• Includes change type, route, and key updates

Outcomes are categorised as:

• Success
• Needs Review
• Failed

Audit log

A persistent record stored in Google Sheets, including:

• Input data
• Execution results
• Route IDs and sequence
• Timestamps for full traceability

Operational workflow

Core features

Change type system

Supports a wide range of operational actions:

• Add / cancel / move orders
• Change delivery day
• Add / remove meals
• Add / remove marketplace items
• Redelivery and box collection
• OTHER (manual fallback)

Each change type:

• Has its own handler
• Uses strict field allowlists
• Applies only intended updates

Marketplace logic

Handles nuanced business rules including:

• CMP / DMP / Wine combinations
• Fruit handling (SFR / LFR)
• Conditional consolidation into DMP
• Safe removal logic without affecting unrelated data

Baseline update layer

A shared layer ensures consistent updates across all actions:

• Name and phone updates (if provided)
• Phone normalisation to +61 format
• Delivery notes mapped to driver-facing comments
• Blank values never overwrite existing data

Validation and guardrails

The system is intentionally defensive:

• Invalid or missing inputs → NEEDS_REVIEW
• Missing routes are blocked
• Date mismatches are prevented
• Strict field filtering avoids unintended changes

Slack presentation layer

Slack output is designed for clarity, not technical detail:

• Clean route names
• Clickable links
• Before/after change summaries
• Structured result blocks

Testing & validation

The system was validated through:

• Two weeks of simulated operational runs
• Real-world scenario testing (success, failure, edge cases)
• Unit testing for parsing and mapping logic
• Live verification against Route4Me API

Results

• Eliminated manual route editing
• Reduced human error significantly
• Standardised operational workflows
• Introduced full audit visibility
• Improved confidence across customer service and logistics teams

Impact

• Enabled non-technical staff to perform complex operations safely
• Reduced operational overhead without increasing headcount
• Improved coordination between departments
• Created a scalable foundation for further automation

Next steps

• Move from polling to event-driven execution
• Introduce role-based permissions
• Expand validation coverage
• Build reporting dashboards for operational insights
• Integrate upstream systems (order ingestion)

Key takeaway

This system demonstrates how structured inputs, strict validation, and modular automation can transform a high-risk manual workflow into a reliable, scalable operational system.