How I Automated Booking and Dispatch for a Small Transport Business with Google Sheets + Apps Script

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:

1. The script checks the Drivers tab for anyone marked "available"
2. It assigns the first available driver to the booking and updates their status to "on trip"
3. It sends an automatic confirmation email to both the driver and the customer
4. 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.