Delivery Management System

ACS Future School ran a course-purchase gift campaign: buy a course, receive a T-shirt, a diary, or both, with the T-shirt further split by size depending on what a student had bought. It reads like a small operational detail. At a few thousand orders a month, it wasn't.

This is the story of how three separate systems, the sales platform, an internal admin panel, and a courier partner's own API, ended up talking to each other through one Google Sheet, and how that sheet started catching the gaps that used to break shipments before they ever reached a courier.

---

The Problem

Order data reached the fulfilment process from the sales platform with frequent gaps, a missing delivery address, a missing or invalid T-shirt size, or an unclear gift combination. A single missing field was enough to fail a shipment once it reached the courier.

The sales platform, the internal admin panel and multiple courier providers each held only part of an order's history. There was no single view of which orders were incomplete, which were in transit, and which had gone missing, arrived late, or been returned, so the first signal something had gone wrong was usually a customer asking where their gift was.

---

The Approach

Three Google Apps Script integrations connect the sales platform, the internal admin backend and a courier partner's API into a single Google Sheet acting as the operational hub.

One script pages through the sales platform's API to pull every paid order together with its gift selection, T-shirt size, delivery address and contact details, de-duplicating by payment ID and excluding failed or pending transactions. A second authenticates against the admin backend and writes delivery-status updates back for each order directly from the sheet. A third pulls the courier's own order list, including its recorded delivery address and status, for cross-checking against the internal record.

Google Sheets formulas cross-reference the three sources to flag any order missing an address or a T-shirt size, resolve the gap automatically wherever the missing value exists in another source, and surface only the genuine exceptions that still need a manual look.

---

The Outcome

• 8,850 completed orders managed end to end across the campaign
• Validated before dispatch, gift and size combinations are checked before a courier ever sees the order, instead of failing on arrival
• One view, delivery status across multiple courier providers tracked in a single sheet
• Self-diagnosing, missed, late and returned deliveries are identified automatically rather than through a customer complaint

---

What I'd Do Differently

If I started this today, I'd design the formula-based validation in before the first batch went out, rather than after the first run of failed shipments made the gaps obvious. Three independent systems reconciled through one sheet turned out to be the right pattern, it was just discovered under pressure rather than chosen deliberately from day one.