How to Build a TMS in Google Sheets (Step-by-Step Guide 2026)

I build trucking systems in Google Sheets for a living. This is the skeleton I'd start from on a Saturday: seven tabs, a few dozen formulas and one script. You get load tracking, invoices, fleet and driver records and a revenue dashboard, with no monthly fee.

Time: 2-3 hours Skill level: Beginner to intermediate Best for: 2-15 trucks running 10-50 loads a month

If you'd rather start from something finished, read the best Google Sheets TMS templates first.

Why Google Sheets

Over Excel: real-time collaboration, automatic saving, phone access and cloud backup, free. Over TMS software: no monthly fees, a layout that matches how you work, and no vendor lock-in.

Step 1: Create the workbook

Create a new spreadsheet at sheets.google.com, name it "YourCompany TMS", and add seven tabs:

Step 2: Company Info

Put field names in column A and your details in column B. Rows 1-9 hold company name, two address lines, city/state/ZIP, phone, email, DOT number, MC number and tax ID.

Starting at row 12, add invoice settings: default payment terms (row 12), invoice prefix such as "INV-" (row 13), next invoice number, default fuel surcharge % and standard rate per mile. Formulas elsewhere point at these cells, so a change here updates everything.

Step 3: Customers

Add columns K-P for credit limit, current balance, preferred rate, special instructions, status and date added.

Auto-number customers in A2 (copy it down):

=IF(B2<>"","CUST-"&TEXT(ROW()-1,"000"),"")

For payment terms, select column J, go to Data → Data validation, choose Dropdown, and enter Net 15, Net 30, Net 45, COD and Prepaid. Every dropdown saves a cleanup job later.

Step 4: Loads

This is the tab you'll live in.

Then U-Z: Driver, Truck, Trailer, Invoice Number, Invoice Date, Payment Status.

Load numbers from the load date, in A2:

=IF(C2<>"",TEXT(C2,"YYYYMMDD")&"-"&TEXT(ROW()-1,"000"),"")

That gives you numbers like "20260923-014": the load date plus the row.

These IDs come from the row number, so don't sort or delete rows on Loads, Customers or Invoices. To see the Loads tab in another order, use a filter view (Data → Filter views → Create new filter view). Once a row is final, you can freeze its ID with Copy → Paste special → Values only.

Make Customer (B) a dropdown that reads from the customer list: Data validation → Dropdown (from a range) → Customers!B2:B. Make Status (T) a dropdown with Booked, Dispatched, In Transit, Delivered, Invoiced, Paid. Make Driver (U) a dropdown from Drivers!A2:A, and Truck (V) from Fleet!A2:A, so every load stores the same IDs the other tabs look up.

To see a customer's payment terms on the load, add them in column AA (optional):

=IFERROR(INDEX(Customers!J:J,MATCH(B2,Customers!B:B,0)),"")

Step 5: Invoices

Invoice number from your prefix, in A2:

=IF(B2<>"",'Company Info'!$B$13&TEXT(ROW()-1,"0000"),"")

Type the load number in B, and let the customer fill in (C2):

=IF(B2<>"",INDEX(Loads!B:B,MATCH(B2,Loads!A:A,0)),"")

Due date (E2), from the customer's terms. "Net 30" adds 30 days; COD and Prepaid come out due on the invoice date:

=IF(D2="","",D2+IFERROR(VALUE(REGEXEXTRACT(INDEX(Customers!J:J,MATCH(C2,Customers!B:B,0)),"\d+")),0))

Step 6: Fleet and drivers

Fleet: Truck #, Make, Model, Year, VIN, License, DOT Expiry, Insurance Expiry (A-H), then Mileage, Last Service, Next Service, Service Type, Cost, Notes (I-N). In O2, show whether each truck is out:

=IF(COUNTIFS(Loads!V:V,A2,Loads!T:T,"Dispatched")+COUNTIFS(Loads!V:V,A2,Loads!T:T,"In Transit")>0,"On Load","Available")

Drivers: Driver ID, Name, Phone, Email, CDL #, CDL Expiry, Medical Expiry, Hire Date (A-H), then Loads This Month, Miles This Month, Revenue, Status, Notes (I-M). Loads this month, in I2:

=COUNTIFS(Loads!U:U,A2,Loads!C:C,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1))

Turn expiry dates red with conditional formatting. A lapsed medical card costs more than any spreadsheet.

Step 7: Dashboard

Monthly revenue: put the first day of each month in A2:A13 (1/1/2026, 2/1/2026 and so on), then in B2, copied down:

=SUMIFS(Loads!S:S,Loads!C:C,">="&A2,Loads!C:C,"<"&EDATE(A2,1))

The next three count every load that has delivered, so they include loads already marked Invoiced or Paid.

Revenue per mile:

=IFERROR(ARRAYFORMULA(SUM(SUMIF(Loads!T:T,{"Delivered","Invoiced","Paid"},Loads!S:S))/SUM(SUMIF(Loads!T:T,{"Delivered","Invoiced","Paid"},Loads!R:R))),0)

Average load value:

=IFERROR(AVERAGE(FILTER(Loads!S2:S,REGEXMATCH(Loads!T2:T,"^(Delivered|Invoiced|Paid)$"))),0)

Loads delivered this month:

=ARRAYFORMULA(SUM(COUNTIFS(Loads!T:T,{"Delivered","Invoiced","Paid"},Loads!K:K,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1))))

Unpaid balance per customer (Customers, column L):

=SUMIFS(Invoices!H:H,Invoices!C:C,B2,Invoices!I:I,"<>Paid")

Unpaid more than 30 days past due:

=SUMIFS(Invoices!H:H,Invoices!E:E,"<"&TODAY()-30,Invoices!I:I,"<>Paid")

Revenue per mile by driver (Drivers tab):

=IFERROR(SUMIFS(Loads!S:S,Loads!U:U,A2)/SUMIFS(Loads!R:R,Loads!U:U,A2),0)

Step 8: One useful script

Go to Extensions → Apps Script and paste this. It highlights any load still "In Transit" after its delivery date, so nothing sits unbilled.

function flagLateLoads() {
  const sheet = SpreadsheetApp.getActive().getSheetByName("Loads");
  const last = sheet.getLastRow();
  if (last < 2) return;
  const rows = sheet.getRange(2, 1, last - 1, 20).getValues();
  const today = new Date();
  today.setHours(0, 0, 0, 0); // due today is not late
  const colors = rows.map((r) => [
    r[10] instanceof Date && r[10] < today && r[19] === "In Transit" ? "#f4cccc" : null,
  ]);
  sheet.getRange(2, 20, colors.length, 1).setBackgrounds(colors);
}

Run it once from the editor to approve access. Then click the clock icon (Triggers) → Add Trigger → Time-driven → Day timer. It repaints column T each run, so don't use manual fill colors there. I don't let a script mark a load Delivered on its own. It isn't delivered until the driver says so.

Step 9: Lock it down

Stop bad data at the door. To flag a missing customer, select Loads!B2:B, go to Format → Conditional formatting → Custom formula, and enter =AND($C2<>"",$B2="") with a red fill. To block duplicate reference numbers, add Data validation → Custom formula =COUNTIF($J$2:$J,J2)<=1 to column J.

Make it usable on a phone. Keep the columns you check most in A-G, freeze the header row (View → Freeze → 1 row), and use dropdowns instead of typing.

Back it up. Version history is automatic (File → Version history). Also make a copy weekly and download an .xlsx every month.

Control access. Share with named people only. Give "Can comment" instead of "Can edit" where you can, turn on two-factor authentication, and never store SSNs in the sheet.

Avoid the usual mistakes. Validate dates, because mixed formats break every SUMIFS. Move self-referencing math to a helper column. Limit ranges on big sheets. Agree on who edits which tab.

When DIY stops paying

This workbook carries a small fleet a long way. If you're weighing it against TMS software, the small trucking company TMS guide compares the options. You've outgrown the workbook when:

• Data entry takes 2+ hours a day, or you juggle 10+ active loads.
• Copy-paste errors reach customers, or invoices go out late.
• Customers want their own invoice formats.
• Drivers call for load details all day.

Past that point, the fix is code, not more formulas. That's what I build: a custom Google Sheets TMS. The system I built for Winetoast Carrier reads rate cons, handles dispatch, and gives each driver a phone web page per load to sign the BOL and upload photos. It saves paperwork to Drive, sends maintenance reminders by mileage, and shows profit per load and a P&L. Entering a load went from 10 minutes to 30 seconds. They've run on it every day since spring 2026. For your own build, I also do setup help at $150/hour.

Pick your starting point

• Skip the build. Request my free template, which already has this structure and the formulas. I email it within one business day.
• Have it built. Ask for a quote on a custom system on top of Google Sheets. It's a one-time project fee, quoted after a walkthrough, with no monthly subscription. It's usually live in 1-2 weeks, and you own every file and script.