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

Building a Transportation Management System (TMS) in Google Sheets is perfect for small trucking companies that need organization without monthly software fees. This guide walks you through creating a complete TMS from scratch.

Not sure if you should build your own or use a template? Check out our guide to the best Google Sheets TMS templates for ready-made solutions.

Time to complete: 2-3 hours
Skill level: Beginner to intermediate
Best for: 2-15 truck fleets processing 10-50 loads monthly

Why Build Your TMS in Google Sheets?

Advantages Over Excel

• Real-time collaboration - Multiple users can edit simultaneously
• Automatic saving - Never lose data again
• Mobile access - Check loads from anywhere
• Free forever - No licensing costs
• Cloud backup - Built-in data protection

Advantages Over TMS Software

• Zero monthly fees - One-time setup effort
• Complete customization - Match your exact workflow
• Familiar interface - Team already knows Sheets
• No vendor lock-in - Your data stays with you
• Instant setup - Start using immediately

Step 1: Setting Up Your Google Sheets TMS Structure

Create Your Master Spreadsheet

1. Go to Google Sheets (sheets.google.com)
2. Create new spreadsheet
3. Rename to "YourCompany TMS System"
4. Create these sheets (tabs at bottom):
   • Company Info
   • Customers
   • Loads
   • Invoices
   • Fleet
   • Drivers
   • Financial Dashboard

Sheet Structure Overview

Each sheet serves a specific purpose:

• Company Info: Business details and settings
• Customers: Client database with billing info
• Loads: Core load tracking and management
• Invoices: Invoice generation and tracking
• Fleet: Truck and trailer management
• Drivers: Driver information and assignments
• Financial Dashboard: Reporting and analytics

Step 2: Company Information Sheet

Basic Company Setup

Column A: Field Names

• Company Name
• Address Line 1
• Address Line 2
• City, State, ZIP
• Phone Number
• Email Address
• DOT Number
• MC Number
• Tax ID

Column B: Your Information Enter your actual company details in Column B.

Invoice Settings

Starting at Row 12:

• Default Payment Terms (e.g., "Net 30")
• Invoice Prefix (e.g., "INV-")
• Next Invoice Number
• Default Fuel Surcharge %
• Standard Rate Per Mile

Pro Tip: These settings will be referenced by formulas throughout your TMS.

Step 3: Customer Database Sheet

Column Headers (Row 1)

Advanced Customer Fields (Columns K-P)

Customer ID Formula

In cell A2, use this formula to auto-generate customer IDs:

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

This creates customer IDs like "CUST-001", "CUST-002", etc.

Data Validation Setup

1. Select Payment Terms column (J)
2. Data → Data validation
3. Criteria: List of items
4. Values: Net 15, Net 30, Net 45, COD, Prepaid
5. Click Done

This creates a dropdown for consistent data entry.

Step 4: Core Loads Sheet

Essential Load Columns (A-T)

Advanced Load Columns (U-Z)

Load Number Auto-Generation

In cell A2:

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

This creates load numbers like "20241201-001".

Status Dropdown Setup

1. Select Status column (T)
2. Data → Data validation
3. List of items: Booked, Dispatched, In Transit, Delivered, Invoiced, Paid

Customer Lookup Formula

In cell B2 (Customer column):

=IF(A2<>"",ARRAYFORMULA(IF(ISBLANK(A2:A),"",VLOOKUP(LEFT(A2:A,8),Customers!A:B,2,FALSE))),"")

This auto-populates customer names based on customer ID.

Step 5: Invoice Generation System

Invoice Sheet Structure

Columns A-J: Basic Invoice Info

Invoice Number Formula

In cell A2:

='Company Info'!B13&TEXT(ROW()-1,"0000")

This uses your invoice prefix from Company Info sheet.

Automatic Invoice Creation

In the Loads sheet, add this formula to column X (Invoice Number):

=IF(T2="Delivered","AUTO-"&A2,"")

In the Invoices sheet, use this formula to pull invoice data:

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

Due Date Calculation

In column E (Due Date):

=IF(D2<>"",D2+30,"")

Adjust "30" to match your payment terms.

Step 6: Fleet Management Sheet

Truck Information Columns

Maintenance Tracking

Availability Status

In column O, add this formula:

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

This shows if each truck is currently assigned to a load.

Step 7: Driver Management Sheet

Driver Information

Performance Tracking

Driver Performance Formula

In column I (Loads This Month):

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

Step 8: Financial Dashboard

Revenue Summary

Create these sections on your Financial Dashboard:

Monthly Revenue (Cells A1:B12)

Month | Revenue
Jan   | =SUMIFS(Loads!S:S,Loads!C:C,">="&DATE(YEAR(TODAY()),1,1),Loads!C:C,"<"&DATE(YEAR(TODAY()),2,1))
Feb   | =SUMIFS(Loads!S:S,Loads!C:C,">="&DATE(YEAR(TODAY()),2,1),Loads!C:C,"<"&DATE(YEAR(TODAY()),3,1))

Continue for all 12 months.

Key Performance Indicators

Revenue Per Mile (Cell E1):

=SUMIF(Loads!T:T,"Delivered",Loads!S:S)/SUMIF(Loads!T:T,"Delivered",Loads!R:R)

Average Load Value (Cell E2):

=AVERAGE(FILTER(Loads!S:S,Loads!T:T="Delivered"))

Loads Delivered This Month (Cell E3):

=COUNTIFS(Loads!T:T,"Delivered",Loads!K:K,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1))

Step 9: Automation with Google Apps Script

Basic Invoice Email Automation

1. Extensions → Apps Script
2. Replace default code with:

function emailInvoice() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getActiveRange();
  var row = range.getRow();
  
  if (sheet.getName() === "Invoices" && row > 1) {
    var invoiceNum = sheet.getRange(row, 1).getValue();
    var customer = sheet.getRange(row, 3).getValue();
    var total = sheet.getRange(row, 8).getValue();
    
    var subject = "Invoice " + invoiceNum + " - " + customer;
    var body = "Please find attached invoice " + invoiceNum + " for $" + total;
    
    // This is a basic example - you'd need to add PDF generation
    console.log("Would email: " + subject);
  }
}

1. Save and name your project

Load Status Updates

function updateLoadStatus() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Loads");
  var data = sheet.getDataRange().getValues();
  
  for (var i = 1; i < data.length; i++) {
    var deliveryDate = data[i][10]; // Column K (Delivery Date)
    var currentStatus = data[i][19]; // Column T (Status)
    
    if (deliveryDate && deliveryDate < new Date() && currentStatus === "In Transit") {
      sheet.getRange(i + 1, 20).setValue("Delivered"); // Update status
    }
  }
}

Step 10: Data Validation and Error Prevention

Required Fields Validation

For critical fields like Customer (Column B in Loads):

1. Select entire column
2. Data → Data validation
3. Criteria: Custom formula is
4. Formula: =NOT(ISBLANK(B2))
5. Show warning for invalid data

Duplicate Prevention

For load numbers:

1. Select Load Number column
2. Data → Data validation
3. Criteria: Custom formula is
4. Formula: =COUNTIF($A$2:$A,A2)<=1

Step 11: Mobile Optimization

Mobile-Friendly Layout Tips

1. Keep important columns left (A-G) - visible on phone screens
2. Use data validation dropdowns - easier than typing
3. Freeze header rows - View → Freeze → 1 row
4. Use conditional formatting - visual status indicators

Mobile Data Entry Form

Create a separate "Data Entry" sheet with:

• Large input cells
• Clear labels
• Validation dropdowns
• Submit button (using Apps Script)

Advanced Features and Formulas

Customer Balance Tracking

In Customers sheet, Column L (Current Balance):

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

Aging Analysis

Create an "Aging" sheet with formulas like:

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

Driver Performance Metrics

Average revenue per mile by driver:

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

Common Mistakes and How to Avoid Them

1. Inconsistent Data Entry

Problem: Different formats for dates, names, addresses
Solution: Use data validation dropdowns and input masks

2. Circular References

Problem: Formulas referencing themselves
Solution: Use helper columns and INDIRECT functions

3. Performance Issues

Problem: Slow calculations with large datasets
Solution: Use ARRAYFORMULA and limit ranges

4. Version Control

Problem: Multiple people editing simultaneously
Solution: Use Sheet protection and shared editing protocols

When to Upgrade from DIY to Professional Automation

Signs You've Outgrown Google Sheets

Time-Based Indicators:

• Spending 2+ hours daily on data entry
• Creating 20+ invoices monthly
• Managing 10+ active loads simultaneously

Error-Based Indicators:

• Frequent copy/paste mistakes
• Customer complaints about invoice delays
• Data inconsistencies affecting operations

Growth-Based Indicators:

• Adding trucks faster than office staff
• Customer requests for different invoice formats
• Need for real-time driver communication

Professional Automation Benefits

What Changes:

• Invoice creation: 30 minutes → 10 seconds
• Driver communication: 20 calls/day → 1 call/week
• Customer formats: Generic → Exact replicas
• Error rate: 5-10% → Near zero

ROI Example:

• Current time: 15 hours/week @ $20/hour = $300/week
• Automated time: 1 hour/week @ $20/hour = $20/week
• Weekly savings: $280 = $14,560/year
• Automation cost: $2,500 one-time
• Payback: 9 weeks

Backup and Security Best Practices

Automatic Backups

1. File → Version history → See version history
2. Make copy weekly - File → Make a copy
3. Export monthly - File → Download → Excel (.xlsx)

Access Control

1. Share → Advanced → Change
2. Set specific permissions per user
3. Disable download/print for sensitive data
4. Use "Can comment" instead of "Can edit" when possible

Data Protection

• Never store SSNs or sensitive personal data
• Use customer codes instead of names in formulas
• Regular password updates for Google accounts
• Two-factor authentication enabled

Getting Started Checklist

Week 1: Basic Setup

• [ ] Create master spreadsheet with all sheets
• [ ] Enter company information and settings
• [ ] Import existing customer data
• [ ] Set up data validation dropdowns
• [ ] Test basic formulas

Week 2: Data Entry and Testing

• [ ] Enter historical load data (last 30 days)
• [ ] Create first invoice using new system
• [ ] Train team on data entry procedures
• [ ] Set up mobile access for drivers
• [ ] Establish backup procedures

Week 3: Advanced Features

• [ ] Implement Apps Script automation
• [ ] Create financial dashboard
• [ ] Set up performance tracking
• [ ] Test all formulas with real data
• [ ] Document procedures for team

Week 4: Optimization

• [ ] Identify time-consuming manual tasks
• [ ] Calculate ROI for potential automation
• [ ] Request quotes for professional automation
• [ ] Plan transition strategy if needed

Free Resources and Next Steps

Download Our Template

Instead of building from scratch, download our free Google Sheets TMS template that includes all formulas and setup.

Professional Automation

When you're ready to eliminate manual work entirely, get a custom automation quote that builds on your Google Sheets foundation.

Additional Learning

• Google Sheets vs Excel for TMS
• Signs You Need TMS Automation
• Small Trucking Company TMS Guide

Conclusion

Building a TMS in Google Sheets gives you complete control over your trucking operations without monthly software fees. While it requires initial setup effort, the ongoing benefits make it worthwhile for most small fleets.

The system you build will serve you well as you grow, and provides an excellent foundation when you're ready to upgrade to full automation.

Ready to get started? Download our complete Google Sheets TMS template and modify it to fit your exact needs.

---

Need help with implementation? We offer setup assistance at $150/hour, or you can upgrade to full automation that eliminates manual work entirely.