FleetTracker: Technical Deep Dive

Architecture, Code Patterns, and Design Decisions

System Overview

FleetTracker is a serverless automation system built on Google Apps Script. It orchestrates multiple Google Workspace APIs (Forms, Sheets, Drive, Docs, Gmail) to create a complete incident management workflow.

Google Apps Script runs on Google's infrastructure with automatic scaling, authentication, and API quota management. This means zero DevOps overhead for the organization.

Technology Stack

Runtime: Google Apps Script (V8 engine)
Language: JavaScript ES6
APIs: Drive API v3, Sheets API v4, Gmail API, Forms API
Trigger: Form submission event (installable trigger)
Execution: ~5-10 seconds per incident (well under quota limits)

Code Architecture

Design Principles

1. Configuration Over Code

All organization-specific settings (folder IDs, email addresses, field names) live in a single configuration block. This allows other nonprofits to deploy the system by editing ~20 lines instead of hunting through 300+ lines of logic.

2. Defensive Programming

Every external lookup has fallback logic. Missing data degrades gracefully rather than crashing the workflow. This is critical when non-technical staff maintain the system.

3. Separation of Concerns

Lookup functions are isolated from the main handler. This makes the code testable (you can call lookupMaintenanceData() without triggering a full incident) and easier to extend (add new lookups without touching core logic).

4. Audit Trail by Default

Everything is logged: email content archived to Drive, timestamps on all folders, structured Google Doc summaries. This isn't optional—compliance demands it.

Configuration Block

The script starts with a centralized configuration section. This is the only part other organizations need to modify during deployment.

// --- CONFIGURATION ---

// Email recipients (comma-separated)
const RECIPIENT_EMAILS = 'ops@example.org,fleet@example.org';

// Master List Spreadsheet (vehicle database)
const MASTER_LIST_SPREADSHEET_ID = '1895NA95MNA054MA9590AJMT9401JM';
const MASTER_LIST_SHEET_NAME = 'Master Sheet';
const MASTER_LIST_KEY_HEADER = 'License Plate';
const MASTER_LIST_SUMMARY_HEADER = 'Lease/Coverage Summary';

// Folder IDs for file organization
const NEW_VEHICLE_ARCHIVE_FOLDER_ID = '902jnfc901ndiweh840dnmqaH495NMFHJ';
const UPLOAD_FOLDER_ID = '123412351231514asdab123tb78v9s093n690vbhw905n6t0';

// Form field names (MUST match Google Form column headers)
const DATE_FIELD_NAME = 'Date of Incident';
const DRIVER_NAME_FIELD_NAME = 'Full Legal Name of the Driver';
const UPLOAD_FIELD_NAME = 'Vehicle Damage/Photos & Scene Diagram';

// Vehicle-to-Folder mapping (maintenance records)
const VEHICLE_FOLDER_MAP = {
  "ABC-1234": "1A2-42ydOzwGw4qasaPfjw4_jlUdNR2ld",
  "XYZ-5678": "9B8-73xdPawHx5rbtBgkx5_kmVeOS3me",
  // Add more vehicles here...
};
            

⊕ Why this matters: Non-developers can add new vehicles to the system by simply adding a line to VEHICLE_FOLDER_MAP. No code logic changes needed.

Core Functions

1. lookupMaintenanceData(licensePlate)

This function scans a vehicle's Drive folder to find the most recent service record, then calculates the next service due date.

function lookupMaintenanceData(licensePlate) {
  // Get the folder ID from the static map
  const incidentFolderId = VEHICLE_FOLDER_MAP[licensePlate];

  // Default return object (used if no data found)
  const maintenanceData = {
    lastServiceDate: 'N/A (No service file found)',
    lastServiceLink: 'N/A',
    nextServiceDate: 'N/A'
  };

  if (!incidentFolderId) {
    maintenanceData.lastServiceDate = 'Error: Vehicle folder ID not configured';
    return maintenanceData;
  }

  try {
    const folder = DriveApp.getFolderById(incidentFolderId);
    const files = folder.getFiles();

    let latestDate = null;
    let latestFileUrl = '';

    // Regex patterns to identify service files
    const serviceRegex = /service/i; // Case-insensitive "service"
    const yyyyDateRegex = /(\d{4}[-/_]\d{2}[-/_]\d{2})/; // YYYY-MM-DD variants

    while (files.hasNext()) {
      const file = files.next();
      const fileName = file.getName();

      // Only process files with "service" in the name
      if (serviceRegex.test(fileName)) {
        let currentDate = null;
        let dateMatch = fileName.match(yyyyDateRegex);

        if (dateMatch) {
          // Parse date from filename
          const dateString = dateMatch[1].replace(/[-/_]/g, '-');
          currentDate = new Date(dateString);
        } else {
          // FALLBACK: Use file creation date
          currentDate = file.getDateCreated();
        }

        // Track the most recent service date
        if (currentDate && !isNaN(currentDate.getTime())) {
          if (!latestDate || currentDate.getTime() > latestDate.getTime()) {
            latestDate = currentDate;
            latestFileUrl = file.getUrl();
          }
        }
      }
    }

    if (latestDate) {
      // Format the last service date (YYYY-MM-DD)
      const yyyy = latestDate.getFullYear();
      const mm = String(latestDate.getMonth() + 1).padStart(2, '0');
      const dd = String(latestDate.getDate()).padStart(2, '0');
      maintenanceData.lastServiceDate = `${yyyy}-${mm}-${dd}`;
      maintenanceData.lastServiceLink = latestFileUrl;

      // Calculate next service date (add 3 months)
      const nextService = new Date(latestDate);
      nextService.setMonth(nextService.getMonth() + 3);

      const nextMm = String(nextService.getMonth() + 1).padStart(2, '0');
      const nextDd = String(nextService.getDate()).padStart(2, '0');
      const nextYyyy = nextService.getFullYear();
      maintenanceData.nextServiceDate = `${nextMm}/${nextDd}/${nextYyyy}`;
    }
  } catch (err) {
    Logger.log(`Error accessing folder: ${err.toString()}`);
    maintenanceData.lastServiceDate = 'CRITICAL ERROR retrieving service history';
  }

  return maintenanceData;
}
            

Key Design Decisions:

Regex Flexibility: The date regex accepts multiple separators (-/_) to handle inconsistent file naming
Fallback to File Creation Date: If no date is in the filename, use the upload date (better than failing)
Error Isolation: Errors are caught and logged, but the function always returns a valid object (never crashes)
Explicit Return Structure: The object shape is predictable, making email template logic simple

⊕ Edge Case Handling: What if there are multiple "service" files with the same date? The code picks the first one encountered (file iteration order), which is acceptable since the dates match. A future enhancement could sum costs from same-date files.

2. lookupVehicleData(fullVehicleString)

This function extracts the license plate from the form submission and looks up vehicle metadata from the Master Sheet (insurance, lease status, make/model).

function lookupVehicleData(fullVehicleString) {
  // Extract license plate (assumes format: "2020 Honda Civic ABC-1234")
  const parts = fullVehicleString.split(' ');
  const licensePlate = parts[parts.length - 1];

  // Default values if lookup fails
  let coverageSummary = `License Plate (${licensePlate}) not found in Master List.`;
  let vehicleMakeModel = licensePlate;

  try {
    const ss = SpreadsheetApp.openById(MASTER_LIST_SPREADSHEET_ID);
    const sheet = ss.getSheetByName(MASTER_LIST_SHEET_NAME);

    if (!sheet) {
      return { key: licensePlate, summary: 'Error: Sheet not found', makeModel: licensePlate };
    }

    const data = sheet.getDataRange().getValues();
    const headers = data[0];
    const dataRows = data.slice(1);

    // Find column indices dynamically (resilient to column reordering)
    const keyColIndex = headers.indexOf(MASTER_LIST_KEY_HEADER);
    const summaryColIndex = headers.indexOf(MASTER_LIST_SUMMARY_HEADER);
    const makeColIndex = headers.indexOf(MASTER_LIST_VEHICLE_HEADER);

    // Search for matching license plate
    for (const row of dataRows) {
      if (row[keyColIndex] === licensePlate) {
        coverageSummary = row[summaryColIndex] || 'No summary provided';
        vehicleMakeModel = makeColIndex !== -1
          ? `${row[makeColIndex]} (${licensePlate})`
          : licensePlate;
        break;
      }
    }
  } catch (err) {
    Logger.log(`CRITICAL ERROR accessing Master List: ${err.toString()}`);
    coverageSummary = `CRITICAL ERROR: ${err.toString()}`;
  }

  return { key: licensePlate, summary: coverageSummary, makeModel: vehicleMakeModel };
}
            

⚠️ Critical Assumption:

The function assumes the license plate is the last space-separated token in the form field. This works because the Google Form uses a dropdown populated from the Master Sheet with the format Year Make Model PLATE.

Why this matters: If staff manually type the vehicle instead of using the dropdown, this could break. The deployment guide should emphasize using the dropdown.

3. onFormSubmit(e) - Main Handler

This is the entry point for the entire system. It's triggered automatically when the Google Form is submitted.

Execution Flow

Step 1: Extract Form Data

Pull values from the event object using field names from configuration.

Step 2: Lookup Context

Call lookupVehicleData() and lookupMaintenanceData() to enrich the incident with metadata.

Step 3: Create Incident Folder

Generate a timestamped folder inside the vehicle's permanent folder. Format: YYYY-MM-DD - Incident - [Driver] - [Vehicle]

Step 4: Move Uploaded Files

Extract file IDs from the form upload field, copy files to the incident folder, then remove from the temporary upload folder.

Step 5: Generate Summary Document

Create a formatted Google Doc with all form data, lookup results, and descriptions. Move it to the incident folder.

Step 6: Send Email Notification

Compose an HTML email with incident details, maintenance alerts, and a direct link to the incident folder. Archive the email content to a text file.

Code Sample: Folder Naming and Creation

// Determine parent folder (vehicle-specific or archive)
let parentFolderId = VEHICLE_FOLDER_MAP[licensePlate];
if (!parentFolderId) {
  Logger.log(`Vehicle ${licensePlate} not in map. Filing in archive.`);
  parentFolderId = NEW_VEHICLE_ARCHIVE_FOLDER_ID;
}
const parentFolder = DriveApp.getFolderById(parentFolderId);

// Create incident folder with standardized name
const incidentFolderName = `${Utilities.formatDate(new Date(submissionDate), Session.getScriptTimeZone(), 'yyyy-MM-dd')} - Incident - ${driverName} - ${makeModel}`;
const incidentFolder = parentFolder.createFolder(incidentFolderName);
            

The date prefix (YYYY-MM-DD) ensures folders sort chronologically in Drive. This makes it trivial to find "incidents from Q1 2024" during audits.

Email Notification System

The email is the primary interface for stakeholders. It must be scannable, actionable, and include all critical context without requiring clicks.

Email Template Structure

const emailSubject = `🚨 Vehicle Incident: ${driverName} - ${makeModel}`;

const emailBody = `
  <p>A new <b>Vehicle Incident Report</b> has been filed.</p>
  <hr>

  <h3>Incident Details:</h3>
  <p><b>Driver:</b> ${driverName}</p>
  <p><b>Date/Time:</b> ${dateValue} at ${timeValue}</p>
  <p><b>Vehicle:</b> ${makeModel}</p>

  <h3>Coverage Context:</h3>
  <p>${coverageSummary}</p>

  <h3>Maintenance Context:</h3>
  <p><b>Last Service:</b>
    ${maintenanceData.lastServiceLink !== 'N/A'
      ? `<a href="${maintenanceData.lastServiceLink}">${maintenanceData.lastServiceDate}</a>`
      : maintenanceData.lastServiceDate
    }
  </p>
  <p><b>Next Service Due:</b> <b>${maintenanceData.nextServiceDate}</b> (Quarterly)</p>

  <hr>

  <p><b>Brief Description:</b></p>
  <p>${briefDescription}</p>

  <hr>

  <p>👉 <a href="${incidentFolderUrl}">VIEW INCIDENT FOLDER</a></p>
`;

MailApp.sendEmail({
  to: RECIPIENT_EMAILS,
  subject: emailSubject,
  htmlBody: emailBody
});
            

Why This Design Works

Subject Line: Includes driver and vehicle for instant context (searchable in Gmail)
Coverage Summary First: Managers immediately see "who pays for this?" (owned vs. leased)
Maintenance Alerts: Proactively flags overdue service (prevents "why wasn't this maintained?" questions)
One-Click Access: The folder link is the primary CTA—no need to navigate Drive manually
Archived Copy: The email content is saved to a .txt file in the incident folder for audit trails

⊕ Edge Case: What if RECIPIENT_EMAILS is empty? The email send fails silently (logged), but the incident folder/doc are still created. This ensures data isn't lost even if email breaks.

Error Handling Philosophy

This system runs unattended in production. Users are not developers—they're busy program staff. Error handling must be bulletproof.

Defensive Patterns Used

Try-Catch Around External APIs
Every DriveApp, SpreadsheetApp, and MailApp call is wrapped in try-catch. If the Sheets API is down, the script degrades gracefully.
Default Values Everywhere
Functions return valid objects even on failure. The email template can safely reference maintenanceData.lastServiceDate knowing it's always a string (never undefined).
Explicit Logging
Every error logs to Logger with context. Admins can view logs in the Apps Script dashboard.
Fallback Folder
If a vehicle's folder ID isn't in VEHICLE_FOLDER_MAP, files go to NEW_VEHICLE_ARCHIVE_FOLDER_ID instead of failing.

⚠️ Known Limitation: Form Field Name Changes

If someone renames a Google Form question (e.g., "Driver Name" → "Driver's Full Name"), the script breaks because responseData[DRIVER_NAME_FIELD_NAME] returns undefined.

Mitigation: The deployment guide includes a warning to never rename form fields without updating the script constants. A future enhancement could add validation to detect this.

Performance Considerations

Execution Time

Typical execution: 5-10 seconds

Form data extraction: ~0.1s
Master Sheet lookup: ~1s (depends on sheet size)
Maintenance folder scan: ~2-4s (depends on file count)
Folder creation + file moves: ~1-2s
Google Doc generation: ~1s
Email send: ~0.5s

Google Apps Script has a 6-minute execution limit for triggers. This system uses ~0.3% of that limit, leaving ample headroom for scaling.

Quota Limits

Resource	Daily Limit	Usage Per Incident	Max Incidents/Day
Email sends	1,500	1	1,500
Drive API calls	20,000	~10-15	~1,500
Execution time	90 min	~10s	~540

Conclusion: The system can handle 500+ incidents per day before hitting any quota limits. A 30-vehicle fleet averaging 2-3 incidents/month is well within capacity.

Future Enhancements

1. Cost Tracking

Parse repair invoices to calculate total cost of ownership per vehicle. Could use OCR (Drive API's files.export with text extraction) to auto-populate costs.

2. Dashboard Integration

Connect the response sheet to Looker Studio for visualizations:

Incidents per vehicle (identify problem vehicles)
Incidents per driver (training opportunities)
Seasonal trends (e.g., winter weather spike)

3. Automated Insurance Filing

For high-severity incidents (marked via form checkbox), auto-generate a pre-filled insurance claim form PDF and attach it to the email.

4. Mileage-Based Maintenance

Replace the fixed 3-month service interval with odometer-based tracking. Would require adding a "Current Mileage" field to the form.

Lessons Learned

What Worked Well

Configuration-first design: Made deployment to other orgs trivial
Fallback logic: System never crashes—it degrades gracefully
Email as interface: Managers don't need to learn a new tool
Structured file naming: Makes audits and searches fast

What I'd Improve

Unit tests: Should have written tests for lookup functions (used manual testing instead)
Version control from day 1: Iterated in the Apps Script editor; git would have helped
More validation: Should check form field names on first run and alert if misconfigured
Metrics logging: Could track execution time per step for performance optimization

Most Surprising Discovery

The maintenance date parsing was way harder than expected. Staff uploaded files with inconsistent naming (Service_2024-03-15.pdf, service 03/15/24.pdf, oil change 3-15.pdf). Had to iterate on the regex multiple times and add the "file creation date fallback" to handle the long tail of edge cases.