Use caseCustom softwareAI product feature
Turning a 40-tab dispatch spreadsheet into an internal tool with rules, roles and history
How a planning spreadsheet with a dozen editors becomes an internal web tool with validation, roles, an audit log, ERP sync and an Excel export people use.
A blueprint, not a client story. The business described is illustrative; the architecture, integrations and trade-offs are real, and this is how I would build it. By Ergini, .
The short version
A custom internal web tool that replaces the spreadsheet an operation runs on, such as a haulier's dispatch plan or a manufacturer's production schedule. It keeps the workflow people know and adds validated forms, roles from the company's Microsoft login, a full history of who changed what, sync with the ERP and Outlook, and export to Excel for analysis. AI is optional and narrow, for migrating messy data and drafting bookings from emails; planners still make every assignment.
- Best for
- Logistics and manufacturing teams whose daily planning lives in a shared Excel or Google Sheets file with many tabs, many editors and one person who understands it.
- Connects to
- Excel and Google Sheets, Postgres, Microsoft 365 single sign-on, The ERP or TMS, Slack
- The AI does
- Optional and narrow: maps messy legacy rows into the new data model, drafts bookings from customer emails, and flags entries that look out of pattern.
- People do
- Plan and assign trucks, drivers and slots, check every AI-drafted booking before saving it, and set the rules the tool enforces.
- Built as
- Custom Software Development, quoted per scope
A Monday in DISPO_2026_v7_FINAL.xlsx
Picture a haulier and forwarder with 30 staff and about 40 trucks, own and subcontracted. Everything is planned in one Excel workbook on SharePoint: 40 tabs, a dozen editors, and a lead dispatcher, Marta, who built it over six years and calls it the Excel only she understands.
- 07:10. The workbook opens read-only because someone left it open on Friday, and the macro that builds driver sheets will not run in Excel for the web.
- 07:25. Weekend orders sit in the shared Outlook mailbox as PDFs, emails and one photo of a handwritten note, each typed into the Orders tab by hand.
- 08:00. Truck availability comes from the Fleet tab, where red means workshop; driver hours sit in the payroll clerk's separate file.
- 09:30. A customer moves a pickup. The date changes in Orders but not in the week plan, where a formula was pasted over with a value in March.
- 11:00. Someone sorts Orders by postcode with one column selected. Nobody notices until a driver reaches the wrong address with the right reference.
- 16:30. Confirmations are copied row by row into email templates, with prices from a Rates tab older than the last fuel surcharge change.
- Month end. Completed jobs are copied into the ERP for invoicing, and three turn up that were never marked delivered.
None of this is a disaster alone. Together it is hours of retyping a day, a trickle of errors that reach customers, and a workbook that works when Marta is there and that nobody else can run.
Why the workbook won, and where it stops working
The spreadsheet is not the mistake. It won because the people who know the work could build the system themselves and change it the same afternoon. Whatever replaces it must keep that speed for what genuinely changes, like a new customer's delivery rules, or people drift back to Excel.
It stops working at a recognizable point: several people editing the same rows, rules living in formulas and cell colors nobody wrote down, and mistakes nobody can trace. Excel keeps file versions, and recent versions show who edited a cell, but nothing stops an impossible entry: a truck in Lyon and Leipzig on the same morning, a delivery before its pickup, a driver planned past his hours.
So the build replicates the workflow, not the workbook. Tabs that hold things (orders, trucks, drivers, customers) become tables with rules; tabs that are really views, like the week plan, become screens built from those tables. Formulas become tested code, and colored cells become statuses with allowed transitions.
It is custom software on a deliberately boring stack, close to the one in my SaaS stack write-up: Postgres, a typed web app, single sign-on and a scheduled job or two. The company will run it for years, so nothing exotic goes in.
What moves into the tool, and what stays in Excel
Excel stays for pivot tables and one-off questions, which it is best at. It just stops being where the truth lives.
In the workbook
- Orders typed in from emails and PDFs, with no checks on postcodes, weights or dates
- Availability read from cell colors and a second file
- Anyone with the link can change anything, rates included
- Tracing a changed delivery date means comparing file versions
- Invoicing means copying completed jobs into the ERP by hand
In the tool
- Orders entered through a validated form, or drafted from the email for a person to check
- Availability computed from bookings, workshop dates and driving-time rules
- Roles from the Microsoft directory: dispatchers plan, sales maintain customers, others read
- Every change stored with who, when, the old value and the new one
- Delivered jobs pushed to the ERP once, with the job ID as the reference
From forty tabs to a data model with rules
The data model is agreed with the workbook's author before any screen is designed. A mapping like this comes out of those sessions.
| In the workbook | In the tool | Rule the tool enforces |
|---|---|---|
| Orders tab | Shipments with pickup and delivery stops | Delivery cannot precede pickup; weight and pallets must fit the vehicle |
| Fleet tab, red and green cells | Vehicles with workshop and inspection dates | A vehicle in the workshop or past inspection cannot be assigned |
| Driver hours in the payroll clerk's file | Drivers and shifts, imported daily | A warning before a plan breaks EU driving-time limits; the tachograph stays the legal record |
| Customers tab and rate sheet | Customers and rates, read from the ERP | Rates are read-only here; only the ERP changes them |
| Comments typed into cells | Notes on a shipment, with author and time | A note can never overwrite a data field |
| Week plan built from formulas | A planning board generated from assignments | One vehicle, one place at a time: double bookings are rejected |
| Cell colors for status | A status with allowed transitions | Delivered needs a proof of delivery or a named confirmer |
| A copy of the tabs per month | One history, filtered by date | Nothing is deleted; closed months turn read-only |
A dry run of the migration
Migration is where these projects most often go wrong, so it is a repeatable script, not a weekend of copy and paste. This dry run imports customers and orders; the model touches only free text.
Migration dry run, week three
- read_workbook("DISPO_2026_v7_FINAL.xlsx", tabs: ["Customers", "Orders 2020-2026"])1,318 customer rows / 41,207 order rows / 36 merged-cell blocks flattened / 2 hidden tabs found
- dedupe_customers(match_on: ["vat_id", "normalized_name", "postcode"])1,102 distinct customers / 216 duplicates merged / 19 conflicting pairs left for review
- parse_dates(column: "Pickup")40,861 parsed / 346 ambiguous, such as 03/04 / 331 resolved from neighboring rows / 15 left for review
- extract_notes(model: "small", schema: "handling", rows: 9480)time windows: 3,977 / tail lift: 1,204 / ADR: 212 / nothing structured: 4,087 / every value linked to its source text
- review_sample(field: "handling", size: 200, reviewer: "Marta")194 confirmed / 6 corrected, mostly vague windows such as 'mornings only'
- load(target: "staging", constraints: "on")41,207 orders / 212 rejected: delivery before pickup (97), unknown vehicle (115) / exported to review.xlsx
- Rejected rows go back in an Excel file to the people who know them, and the import reruns until it is clean. The real import happens once, the weekend before cutover.
A transport order, from inbox to assigned truck
Day to day, the tool is mostly forms, rules and a planning board. AI appears at one optional step, and what it produces is only ever a draft.
01 Trigger · Microsoft Graph
A transport order arrives
By email to the shared orders mailbox, read through Microsoft Graph with access scoped to that mailbox. Phone orders go straight to the dispatcher's form.
02 AI model · Structured output
Draft the booking
A model fills the booking form from the email (stops, dates, pallets, weight, handling notes) under a fixed schema, marking anything it cannot find as missing.
03 Plain code
Check against the rules
Postcodes are validated, the customer is matched to ERP master data by VAT ID, and weight and dates are checked against vehicle types and delivery windows.
04 Person
A dispatcher reviews and saves
The draft opens beside the original email with problems highlighted. Nothing becomes a booking until a person saves it.
05 Decision
Can it be planned as it stands?
Worked out by rules in code, offered to the dispatcher as options.
- A suitable vehicle and driver are free in the window then the board offers them, nearest previous drop first
- Every suitable vehicle is booked or in the workshop then the order goes to the subcontractor queue at the agreed rate
- The customer, an address or the rate cannot be matched then it stays a draft until sales creates the master data in the ERP
06 System · Microsoft Graph
Confirm to customer and driver
The confirmation goes out from the orders mailbox with the booking reference, and the driver's run sheet is generated from the assignment.
07 Plain code · Postgres, Slack
Record every change
Each save writes the old value, the new value, the user and the time to an audit table; a change after the driver has left alerts the dispatcher on shift.
08 Result · ERP API, .xlsx
Billing and exports
Confirmed deliveries go to the ERP for invoicing with the job ID as reference, so a retry cannot bill twice. Any view exports to Excel with real dates and numbers.
Where spreadsheet replacements usually fail
The code is rarely what sinks these projects. These six risks shape the plan from week one.
Rules that only one person knows
The author carries dozens of rules that exist nowhere else, like the customer who needs a call before every delivery. Discovery starts with long, screen-shared sessions walking through real weeks, and every rule found goes into a list they sign off. Their time is the scarcest input, so it is booked in advance.
Data messier than anyone admits
Merged cells, dates typed as text, one customer spelled five ways. The migration script runs again and again against fresh copies of the workbook, with rejected rows sent back, so the final import is a rehearsed step rather than a leap.
A shadow spreadsheet
If the tool is slower than Excel for any common task, someone keeps a private copy and the data splits again. So the first release covers the daily planning loop completely, with keyboard shortcuts and bulk edits, and the workbook turns read-only on cutover day instead of vanishing.
Scope that grows once people see it
Every department will want its tab turned into a module. Requests are ranked with the operations lead, version one ships what the daily loop needs, and nothing is built just because it was in the spreadsheet.
An audit log is also a monitoring tool
A record of who changed what also shows how each employee works. In Germany, section 87(1) no. 6 of the Works Constitution Act gives the works council a say over technical systems that can monitor behavior or performance, so where there is one, it hears about the log early, with a written statement of what the log is used for and what it is not.
Exports nobody can use
People build pivot tables on exports, so exports open in Excel with real dates and numbers, not text, and keep stable column names between releases. The export is a tested feature, because it is how the tool wins over the people who liked the spreadsheet.
Would Airtable, Retool or Power Apps do the job?
Often, yes. If the work is mostly a shared list with a few forms and statuses, Airtable, SmartSuite, Glide or Softr can replace the spreadsheet in days, and operations can keep changing it themselves. A Microsoft 365 company should try Power Apps first, since identity, data and admin rights are already there, and Retool suits a technical person who wants screens over an existing database. For a small team with simple rules, any of these beats a custom build, and I would say so.
Custom wins when the data model has real structure (vehicles, drivers, stops, time windows, rate agreements) with rules across all of it; when row counts and concurrent editors make low-code tools slow or costly per seat; when permissions go past internal roles, like subcontractors seeing only their own jobs; and when the tool must write reliably into an ERP, with the duplicate protection a no-code connector lacks, the same gap that trips up inherited automations.
A middle path works well: prototype the screens in Airtable or Retool for a few weeks, then build the permanent version once the rules stop moving, with the prototype as the specification. The trade-offs are in no-code or custom, and once the data sits in one database, asking it questions in plain language is a natural next step.
How you would know it is working
A blueprint has no results to report, so here is what I would measure from the first week instead, on your own data.
- Orders entered once
- Share of orders that reach a booking, and later an ERP invoice, without being typed twice, read from the audit log.
- Entries stopped by rules
- Counted per rule. Each is a mistake that would have reached the spreadsheet; a rule that never fires may not be needed.
- Full exports per person
- Repeated full-dataset exports by one person usually mean they still plan in Excel, and the tool lacks something they need.
- Time to plan the next day
- Timed with the dispatchers against the old workbook. The question is whether the daily loop got faster, not whether the feature list grew.
- Invoicing gaps
- Delivered jobs with no ERP invoice after two days, checked daily instead of discovered at month end.
What a build like this costs
This is built as Custom Software Development, which is quoted per scope after a free 30-minute call rather than from a fixed table, because the range of what counts as custom software is too wide for one honest number.
What it costs to run
Hosting is small: a managed Postgres database and an app server in an EU region, typically tens to low hundreds of euros a month. There is no per-seat fee, and the optional email drafting costs a few cents per order.
What moves the price
- How many kinds of things the data model holds and how many rules connect them
- The state of the old data: merged cells, free text and duplicates need a migration script and review rounds
- Integration depth: reading ERP master data is modest; writing completed jobs back needs error handling and reconciliation
- Permissions beyond internal roles, such as subcontractors or customers who see only their own records
- Whether AI features ship in the first release or after the core tool is in daily use
Who this is for
- Hauliers and forwarders planning trucks, drivers and subcontractors in a shared workbook
- Manufacturers whose production schedule or capacity plan lives in Excel, edited by planners, sales and the shop floor
- Operations that slow down when the one person who understands the spreadsheet is away
- Teams that tried Airtable or Power Apps and hit limits on data model, permissions or ERP integration
- Companies on Microsoft 365 that want internal tools behind the same login as everything else
Questions people ask about this
How do I replace Excel with custom software without disrupting operations?
Run the two side by side. Migrate the data with a repeatable script, then give the team a week or two planning in the tool while the workbook stays read-only for reference, with Excel export kept for analysis. Cut over on a quiet day, after the spreadsheet's author has walked through real weeks in the tool and signed off the rules.
How do we get out of spreadsheet hell without buying a bloated ERP?
Usually by leaving the ERP alone. The spreadsheet exists because the ERP covers one workflow badly, such as dispatch or production planning. A small tool for exactly that workflow, reading master data from the ERP and pushing finished jobs back, is simpler than a new ERP and shaped like the work. If the gap is only a shared list, Airtable may be all it takes.
Is Airtable or Power Apps enough, or do we need a custom internal tool?
For shared lists, simple forms and a small team, Airtable, SmartSuite or Power Apps are usually enough and far quicker to set up. Custom makes sense when the data model has many connected rules, permissions go beyond internal roles, the tool must write reliably to an ERP, or per-seat pricing and performance start to hurt.
Can AI turn our spreadsheet into an app?
It can speed up parts of the job: reading free-text notes into fields during migration, spotting duplicate customers, drafting bookings from emails. It cannot know the rules in your dispatcher's head, which is where most of the value sits. AI app builders produce a convincing first screen fast; the data model, permissions and ERP link still need designing.
How much does it cost to build a custom internal tool?
It is quoted per scope, because three screens over one table is a different job from a planning system with rules, roles and an ERP link. The drivers are the data model, the state of the old data, the integrations and the permissions. A free 30-minute call with the spreadsheet on screen is usually enough for a realistic range.
Sources