A few months ago a friend who runs a small intercity transport service asked me for help. His business takes phone and WhatsApp bookings for trips between cities, and he was tracking everything manually - one spreadsheet for bookings, a notebook for driver assignments, and constant back-and-forth on WhatsApp to confirm pickup times. Nothing was connected, so double-bookings and missed pickups happened more often than he'd like to admit.
Since he didn't want to pay for a full booking platform yet, I built him a lightweight system using Google Sheets and Apps Script. Here's how it works, in case you're facing a similar problem for a client or your own side project.
The Setup
The core of the system is a single Google Sheet with three tabs:
- Bookings - every incoming trip request, with columns for customer name, phone, pickup location, drop-off location, date, time, and status
- Drivers - a list of available drivers with their vehicle type and current status (available, on trip, off duty)
- Dispatch Log - an automatically generated log of which driver was assigned to which booking, and when
The Automation Logic
I wrote an Apps Script bound to the sheet that runs on form submission (bookings come in through a Google Form linked to the sheet, which the business owner shares as a link in WhatsApp replies). When a new row is added:
- The script checks the Drivers tab for anyone marked "available"
- It assigns the first available driver to the booking and updates their status to "on trip"
- It sends an automatic confirmation email to both the driver and the customer
- It logs the assignment with a timestamp in the Dispatch Log tab
Here's a simplified version of the assignment function:
function assignDriver(row) {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Drivers");
const data = sheet.getDataRange().getValues();
for (let i = 1; i < data.length; i++) {
if (data[i][2] === "available") {
sheet.getRange(i + 1, 3).setValue("on trip");
return data[i][0];
}
}
return null;
}
Why This Matters for Small Transport Businesses
Most small operators running intercity routes don't need, or can't yet justify the cost of, an enterprise dispatch platform. A well-structured spreadsheet with a bit of scripting can handle a genuinely useful volume of bookings, enough to remove the manual back-and-forth and the double-booking risk, without adding a monthly software bill.
For reference, when I was researching how established operators handle pricing and booking transparency for exactly this kind of route, this breakdown of car lift dubai to abu dhabi options was a useful example of how the customer-facing side, clear pricing and pickup/drop-off terms, pairs with the operational side I was building.
What I'd Add Next
If I were to extend this system, the next steps would be:
- A simple dashboard tab with charts for bookings per day and per week
- Automated reminders sent a set number of hours before pickup
- A basic driver rating field to track service quality over time
None of this requires a "real" backend. Apps Script triggers, a Google Form, and a bit of planning go a long way for a business that's still validating its processes before investing in dedicated software.
If you've built something similar for a small operations-heavy business, I'd be curious to hear how you structured it.
