FleetTracker for NGOs

Open-Source Fleet Incident Management System

Production Deployment Google Apps Script Workflow Automation Open Source

Deployed at: Mid-sized behavioral health nonprofit (250+ employees)
Status: Production system since 2024
Repository: GitHub
License: MIT (Free for nonprofit use)

The Problem

Most small-to-medium nonprofits manage vehicle fleets using fragmented workflows:

For organizations with 10-30 vehicles serving vulnerable populations, this creates compliance risks, financial waste, and operational chaos.

The Solution

FleetTracker is a zero-cost automation system built entirely on Google Workspace (which most nonprofits already use). It transforms incident reporting from a manual nightmare into a structured, automated workflow.

System Architecture

┌─────────────────┐
│  Google Form    │  ← Driver submits incident report (mobile-friendly)
│  (Public Link)  │
└────────┬────────┘
         │ Form Submit Trigger
         ▼
┌─────────────────────────────────────────────────────────────┐
│  Google Apps Script (Automated Processing)                  │
│  ───────────────────────────────────────────────────────────│
│  1. Extract license plate → Lookup vehicle in Master Sheet  │
│  2. Find maintenance folder → Parse service history         │
│  3. Calculate next service date (quarterly schedule)        │
│  4. Create timestamped incident folder                      │
│  5. Move uploaded photos to permanent archive               │
│  6. Generate formatted incident summary (Google Doc)        │
│  7. Email stakeholders with context + links                 │
└─────────────────────────────────────────────────────────────┘
         │
         ▼
┌─────────────────────────────────────────────────────────────┐
│  Organized Google Drive Structure                           │
│  ───────────────────────────────────────────────────────────│
│  📁 Vehicle ABC-1234/                                       │
│     ├── 📁 2024-03-15 - Incident - John Doe - Honda Civic  │
│     │   ├── 📄 Incident Summary.gdoc                       │
│     │   ├── 📷 Damage Photo 1.jpg                          │
│     │   ├── 📷 Scene Diagram.png                           │
│     │   └── 📄 Email Notification Log.txt                  │
│     ├── 📄 Service - 2024-01-10.pdf                        │
│     └── 📄 Insurance Policy.pdf                            │
└─────────────────────────────────────────────────────────────┘
         │
         ▼
┌─────────────────────────────────────────────────────────────┐
│  Stakeholder Email (HTML formatted)                         │
│  ───────────────────────────────────────────────────────────│
│  🚨 Vehicle Incident: John Doe - Honda Civic (ABC-1234)    │
│                                                             │
│  Coverage: Owned / Full Coverage (State Farm #12345)       │
│  Last Service: 2024-01-10 (Link to invoice)               │
│  Next Service Due: 04/10/2024 ⚠️ OVERDUE                   │
│                                                             │
│  👉 [VIEW INCIDENT FOLDER]                                 │
└─────────────────────────────────────────────────────────────┘

Key Features

🔍 Intelligent Vehicle Context

The system automatically looks up critical data from a Master Vehicle List:

  • Lease vs. ownership status
  • Insurance carrier and policy number
  • Coverage type (liability vs. comprehensive)
  • Vehicle make/model/year

This means managers get actionable context immediately—no digging through files to find "which insurance company covers this vehicle?"

📅 Automated Maintenance Tracking

The script scans each vehicle's folder for service records and:

  • Parses dates from filenames (e.g., Service - 2024-03-15.pdf)
  • Calculates the next quarterly service due date
  • Flags overdue maintenance in incident emails
  • Links directly to the last service invoice

📧 Smart Email Notifications

Every incident triggers an HTML email to configurable stakeholders:

  • Operations Director (always)
  • Program Manager (if known)
  • Fleet Coordinator
  • Insurance liaison (for damage claims)

Emails include one-click links to the incident folder, formatted summaries, and all relevant context. No more "forward this to insurance" email chains.

🗂️ Structured File Organization

Files are automatically organized using a standardized naming convention:

Folder Name Format: YYYY-MM-DD - Incident - [Driver Name] - [Vehicle Make/Model] (Plate) Example: 2024-03-15 - Incident - Jane Smith - Toyota Camry (XYZ-5678)/ ├── 2024-03-15 - Incident - Jane Smith - Toyota Camry (XYZ-5678) - Summary.gdoc ├── Damage Photo 1.jpg ├── Scene Diagram.png └── Email Notification Log.txt

This makes audits, insurance claims, and historical reviews trivial. Everything is timestamped, searchable, and linked to the vehicle.

🚓 Police & Witness Tracking

The form uses conditional branching to capture critical details only when relevant:

  • Police attendance: Officer name, badge number, police file #, detachment
  • Witness information: Names, phone numbers, any other contact details
  • Other driver details: Insurance, license, vehicle info (if multi-vehicle incident)

Drivers only see the sections they need—streamlined UX without sacrificing data completeness.

🩹 Injury & Liability Tracking

Automatically flags high-risk incidents requiring immediate follow-up:

  • Employee injuries (triggers workers' comp notification)
  • Non-employee injuries (clients, other drivers, pedestrians)
  • Weather/road conditions captured for liability context

The injury flags allow HR to proactively reach out before incidents escalate to formal complaints or claims.

📷 Vehicle Damage Diagram

The form includes a downloadable damage diagram template (top, side, front/rear views) that drivers can mark up on their phones and upload.

  • Standardizes damage documentation across all incidents
  • Insurance adjusters love this (saves hours of back-and-forth)
  • Combines with photo uploads for complete visual record

Photo guidance prompts drivers to capture: wide-angle scene, close-up damage, license plates, insurance slips, and driver's licenses.

🚨 Built-In Safety Instructions

The form header includes immediate action guidance for drivers:

  • When to call 911 (injuries, impaired drivers, blocking traffic)
  • Mandatory on-duty manager contact before proceeding
  • When police are required at the scene (injuries, hit-and-run, government vehicles, >$5k damage)
  • What NOT to do (don't admit fault, don't promise payment)

This ensures compliance and protects the organization from liability—especially critical for inexperienced drivers who've never been in an accident.

Business Impact

~8 hrs/week Admin time saved
100% Incident documentation rate
$0 Infrastructure cost
~30 min Average response time

Measured Outcomes (6-month pilot)

Technical Highlights

Why Google Apps Script?

Unlike heavyweight solutions (fleet management SaaS, custom databases), this system:

Code Architecture

The maintenance date parser uses regex to extract dates from filenames in multiple formats (YYYY-MM-DD, YYYY/MM/DD, YYYY_MM_DD) and falls back to file creation date if no match is found.

The script is modular and defensive:

  1. Configuration block - All IDs/settings in one place (easy for other orgs to adapt)
  2. Lookup functions - Separate concerns (vehicle data vs. maintenance data)
  3. Main handler - Orchestrates workflow with robust error handling
  4. Fallback logic - Gracefully handles missing data (e.g., new vehicles not yet in map)

Sample Code: Maintenance Lookup

function lookupMaintenanceData(licensePlate) { const incidentFolderId = VEHICLE_FOLDER_MAP[licensePlate]; const folder = DriveApp.getFolderById(incidentFolderId); const files = folder.getFiles(); let latestDate = null; const serviceRegex = /service/i; const dateRegex = /(\d{4}[-/_]\d{2}[-/_]\d{2})/; while (files.hasNext()) { const file = files.next(); const fileName = file.getName(); if (serviceRegex.test(fileName)) { let dateMatch = fileName.match(dateRegex); let currentDate = dateMatch ? new Date(dateMatch[1]) : file.getDateCreated(); // Fallback if (!latestDate || currentDate > latestDate) { latestDate = currentDate; } } } return latestDate; }

Deployment Guide for Other Nonprofits

This system is designed to be deployable by any nonprofit with Google Workspace. No technical expertise required beyond basic spreadsheet skills.

Prerequisites

Setup Process

  1. Copy the template - with step-by-step instructions
  2. Create your Master Vehicle List - Populate the spreadsheet template with your fleet data
  3. Set up Drive folder structure - One folder per vehicle (script auto-creates subfolders)
  4. Configure the script - Update folder IDs and email addresses (all in one config block)
  5. Test with a sample incident - Submit a test form to verify the workflow
  6. Train your team - Share the form link and show managers the email alerts

Full documentation: Deployment Guide | Download Templates

Future Enhancements

Potential features for v2.0 (community contributions welcome):

Lessons Learned

What Worked

What I'd Do Differently

Project Links