MICHROCK LOGISTICS, INC.

P&D FLEET COMMAND

Weekly Volume

Stops: 0

Packages: 0

DRO: 0s • 0p

Weekly Volume Variance

Stops: 0

Packages: 0

On-Truck vs. Planned DRO Diff

Weekly Fleet ILS

100%

16 of 16 Drivers ≥ 99.5% Target

Drivers

Full-Time (≥5d): 0

Part-Time (<5d): 0

Seasonal (TBD): 0

16 Total Drivers

Week of Sat Aug 22, 2026 – Fri Aug 28, 2026

Driver & Base Schedule DRO (Stops / Pkg) Truck (Stops / Pkg) Diff (Variance)	SAT (Aug 22)	SUN (Aug 23)	MON (Aug 24)	TUE (Aug 25)	WED (Aug 26)	THU (Aug 27)	FRI (Aug 28)	Weekly Summary

⚡ Quick Add/Remove: Click "+ Add" on any off-cell to assign in 1 click (or click ⚙️ for custom details). Hover over an active shift and click × to immediately remove it. Click on any day column header to edit DRO, On Truck, and Completed metrics.

📅 Gregorian 2026 • Quarter 3 (Q3) ✨ Current Quarter

July 1, 2026 – September 30, 2026 • 92 Days

Quarterly Safety Summary Report

Gregorian Q3 2026

Quarterly compliance summary and coaching status across all 16 delivery drivers for the selected Gregorian quarter.

Clean Safety Record

14 / 16 (87.5%)

Drivers With Incidents

DriveCam / Speeding in Q

Commendations / Passed

Driver & Role	Default Route	Quarter Incidents	Quarter Incident Breakdown & Coaching Notes	Quarter Standing	Actions

Mechanic Shop • Active Issues by Truck Number

Direct numeric view for shop technicians to inspect reported faults, add work notes, and clear work orders.

Fleet Manager Master Data Table

Align, edit, add, or update vehicle specs, VINs, mileage, and status across the entire enterprise data list.

Truck #	Vehicle Specs & Owner	17-Digit VIN	Plate & State	Odometer	Operational Status	Compliance	Docs	Actions

Total iPads

0 Registered Units

In Field / Active

0 0% Dispatched

Advantix Cellular

0 eSIM Active

Spares & In-Bay

0 Standby Units

MichRock ID	Device Model & Specs	Serial Number	Cellular IMEI & eSIM EID	Advantix Cellular	Operational Status	Notes & Details	Actions

Total Scanners

0 Inventory Units

In Field / In Use

0 0% Active

Transferred

0 Transferred Units

Need to Add / Setup

0 Pending Portal Registry

Scanner Serial Number	Operational Status	Transferred & Photo	Activation Date	Notes / Inventory Details	Actions

Duty Status	Driver & Role	Assigned Route	Assigned Truck	ILS %	Stops	🚨 Safety	Shift Notes

Driver Name & Role	SAT	SUN	MON	TUE	WED	THU	FRI	Total Days

Michrock Logistics operates 100% self-contained in this web interface. You can save, download, or restore your fleet and schedule database at any time.

Full System JSON Snapshot

Exports complete fleet inventory, audit histories, active issues, 16-driver roster with availability, and dispatch schedules into a single JSON file.

Restore Database from File

Upload a previously exported backup file to restore all fleet and schedule data.

Restore Factory Defaults

Clean Slate

Instantly resets and restores all original 16 fleet trucks, 16 lineup drivers, assigned routes, and default weekly schedules.

📊 Google Sheets Live Database Backend

Google Workspace Ready

Connects this dashboard directly to a live Google Sheet in your company Google Drive. All shifts, truck statuses, driver rosters, and safety logs are saved in human-readable spreadsheet tabs that you can view, edit, and query anytime.

📋 Setup Instructions (1-Minute Google Apps Script Code)

Create a new spreadsheet at sheets.new titled "MichRock Fleet & Schedule Master DB".
In the top menu, click Extensions > Apps Script.
Delete any sample code and paste the script below.
Click Deploy > New deployment > Select type > Web app:
• Execute as: Me
• Who has access: Anyone
Click Deploy, grant Google Drive/Sheets permissions, copy the Web App URL, and paste it in the box above!

Google Apps Script (Code.gs):

/**
 * =========================================================================
 * MICHROCK LOGISTICS - BULLETPROOF GOOGLE SHEETS BACKEND (AUTO-DISCOVERY)
 * =========================================================================
 * 
 * Features:
 * 1. Automatic Spreadsheet Discovery (Finds or Creates Spreadsheet in Drive)
 * 2. Multi-Tab Weekly Schedule Generator (e.g. Week_Aug22-Aug28)
 * 3. Sanitized & Pen-Test Hardened (Zero formula injection)
 * 4. Cross-Origin JSONP & Simple POST Support
 * =========================================================================
 */

var SHEET_SCHEDULE = "Weekly_Schedule";
var SHEET_FLEET = "Fleet_Trucks";
var SHEET_DRIVERS = "Drivers_Roster";
var SHEET_SAFETY = "Safety_Incidents";
var SPREADSHEET_TITLE = "MichRock Fleet & Schedule Master DB";

function getSpreadsheet() {
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    if (ss) return ss;
  } catch (e) {}

  try {
    var files = DriveApp.getFilesByName(SPREADSHEET_TITLE);
    if (files.hasNext()) {
      return SpreadsheetApp.open(files.next());
    }
  } catch (e) {}

  return SpreadsheetApp.create(SPREADSHEET_TITLE);
}

function doGet(e) {
  try {
    var ss = getSpreadsheet();
    var action = (e && e.parameter && e.parameter.action) ? e.parameter.action : "getAll";
    var callback = (e && e.parameter && e.parameter.callback) ? e.parameter.callback : null;

    initAllSheets(ss);

    var result = {};

    if (action === "getAll") {
      result = {
        status: "success",
        spreadsheetName: ss.getName(),
        spreadsheetUrl: ss.getUrl(),
        timestamp: new Date().toISOString(),
        fleet: readSheetObjects(ss.getSheetByName(SHEET_FLEET)),
        drivers: readSheetObjects(ss.getSheetByName(SHEET_DRIVERS)),
        schedule: readSheetObjects(ss.getSheetByName(SHEET_SCHEDULE)),
        safety: readSheetObjects(ss.getSheetByName(SHEET_SAFETY))
      };
    } else if (action === "getSchedule") {
      result = { status: "success", data: readSheetObjects(ss.getSheetByName(SHEET_SCHEDULE)) };
    } else if (action === "getFleet") {
      result = { status: "success", data: readSheetObjects(ss.getSheetByName(SHEET_FLEET)) };
    } else if (action === "getDrivers") {
      result = { status: "success", data: readSheetObjects(ss.getSheetByName(SHEET_DRIVERS)) };
    } else if (action === "getSafety") {
      result = { status: "success", data: readSheetObjects(ss.getSheetByName(SHEET_SAFETY)) };
    } else {
      result = { status: "error", message: "Unknown action: " + action };
    }

    var jsonString = JSON.stringify(result);

    if (callback) {
      var safeCallback = String(callback).replace(/[^a-zA-Z0-9_]/g, "");
      return ContentService.createTextOutput(safeCallback + "(" + jsonString + ");")
        .setMimeType(ContentService.MimeType.JAVASCRIPT);
    }

    return ContentService.createTextOutput(jsonString)
      .setMimeType(ContentService.MimeType.JSON);

  } catch (err) {
    var errJson = JSON.stringify({ status: "error", message: err.toString() });
    if (e && e.parameter && e.parameter.callback) {
      var safeCb = String(e.parameter.callback).replace(/[^a-zA-Z0-9_]/g, "");
      return ContentService.createTextOutput(safeCb + "(" + errJson + ");")
        .setMimeType(ContentService.MimeType.JAVASCRIPT);
    }
    return ContentService.createTextOutput(errJson).setMimeType(ContentService.MimeType.JSON);
  }
}

function doPost(e) {
  try {
    var ss = getSpreadsheet();
    initAllSheets(ss);

    var payload = null;
    if (e && e.postData && e.postData.contents) {
      try {
        payload = JSON.parse(e.postData.contents);
      } catch (err1) {
        var raw = e.postData.contents;
        if (typeof raw === "string" && raw.indexOf("data=") === 0) {
          payload = JSON.parse(decodeURIComponent(raw.substring(5)));
        }
      }
    } else if (e && e.parameter && e.parameter.data) {
      payload = JSON.parse(e.parameter.data);
    }

    if (!payload) {
      throw new Error("No readable JSON payload found in request");
    }

    var action = payload.action || "syncAll";

    if (action === "syncAll" || payload.schedule || payload.fleet || payload.drivers) {
      var schedule = payload.schedule || [];
      var fleet = payload.fleet || [];
      var drivers = payload.drivers || [];

      // 1. Write Master Sheets
      if (schedule.length > 0) writeScheduleSheet(ss.getSheetByName(SHEET_SCHEDULE), schedule);
      if (fleet.length > 0) writeFleetSheet(ss.getSheetByName(SHEET_FLEET), fleet);
      if (drivers.length > 0) {
        writeDriversSheet(ss.getSheetByName(SHEET_DRIVERS), drivers);
        writeSafetySheet(ss.getSheetByName(SHEET_SAFETY), drivers, schedule);
      }

      // 2. Generate Multi-Tab Weekly Matrices
      createDedicatedWeeklyTabs(ss, drivers, schedule, payload.weekStart);
    }

    return ContentService.createTextOutput(JSON.stringify({
      status: "success",
      message: "Synced to " + ss.getName(),
      spreadsheetUrl: ss.getUrl(),
      timestamp: new Date().toISOString()
    })).setMimeType(ContentService.MimeType.JSON);

  } catch (err) {
    return ContentService.createTextOutput(JSON.stringify({
      status: "error",
      message: err.toString()
    })).setMimeType(ContentService.MimeType.JSON);
  }
}

function sanitizeCell(val) {
  if (val === null || val === undefined) return "";
  if (typeof val === "number" || typeof val === "boolean") return val;
  var str = String(val).trim();
  if (str.length > 0) {
    var c = str.charAt(0);
    if (c === "=" || c === "+" || c === "-" || c === "@") {
      return "'" + str;
    }
  }
  return str;
}

function initAllSheets(ss) {
  var definitions = [
    {
      name: SHEET_SCHEDULE,
      color: "#1e3a8a",
      headers: ["Date", "Day", "Driver Name", "Duty Status", "Route", "Truck Number", "ILS Score %", "Planned Stops", "Safety Flag", "Shift Notes"]
    },
    {
      name: SHEET_FLEET,
      color: "#f97316",
      headers: ["Truck Number", "Year", "Make", "Model", "Type", "VIN", "Plate", "State", "Status", "Mileage", "Assigned Route", "Assigned Driver", "DOT Inspection Due", "Insurance Policy", "Last Service Date", "Open Issue Description"]
    },
    {
      name: SHEET_DRIVERS,
      color: "#059669",
      headers: ["Driver ID", "Full Name", "Role", "Phone", "Default Route", "Default Truck", "Base Availability Days", "Average ILS %", "Notes"]
    },
    {
      name: SHEET_SAFETY,
      color: "#dc2626",
      headers: ["Date", "Gregorian Quarter", "Driver Name", "Role", "Route", "Event Category", "Severity", "Title", "Description & Coaching Notes"]
    }
  ];

  definitions.forEach(function(def) {
    var sheet = ss.getSheetByName(def.name);
    if (!sheet) {
      sheet = ss.insertSheet(def.name);
    }
    if (sheet.getLastRow() === 0) {
      sheet.appendRow(def.headers);
      formatHeaderRow(sheet, def.color);
    }
  });
}

function formatHeaderRow(sheet, bgColor) {
  var headerRange = sheet.getRange(1, 1, 1, sheet.getLastColumn() || 1);
  headerRange.setBackground(bgColor)
    .setFontColor("#ffffff")
    .setFontWeight("bold")
    .setFontSize(11)
    .setHorizontalAlignment("center");
  sheet.setFrozenRows(1);
}

function readSheetObjects(sheet) {
  if (!sheet || sheet.getLastRow() <= 1) return [];
  var data = sheet.getDataRange().getValues();
  var headers = data[0];
  var rows = data.slice(1);

  return rows.map(function(row) {
    var obj = {};
    headers.forEach(function(h, idx) {
      var key = String(h).toLowerCase().replace(/[^a-z0-9]/g, "_");
      obj[key] = row[idx];
    });
    return obj;
  });
}

function writeScheduleSheet(sheet, schedule) {
  if (!sheet) return;
  var headers = ["Date", "Day", "Driver Name", "Duty Status", "Route", "Truck Number", "ILS Score %", "Planned Stops", "Safety Flag", "Shift Notes"];
  var rows = [headers];

  schedule.forEach(function(s) {
    rows.push([
      sanitizeCell(s.date),
      sanitizeCell(s.dayCode),
      sanitizeCell(s.driver),
      sanitizeCell(s.dutyStatus || "working"),
      sanitizeCell(s.route),
      sanitizeCell(s.truckNumber),
      parseFloat(s.ils) || 100,
      parseInt(s.stops, 10) || 125,
      s.safetyIssue ? "YES" : "NO",
      sanitizeCell(s.notes)
    ]);
  });

  sheet.clearContents();
  sheet.getRange(1, 1, rows.length, headers.length).setValues(rows);
  formatHeaderRow(sheet, "#1e3a8a");
  sheet.autoResizeColumns(1, headers.length);
}

function writeFleetSheet(sheet, fleet) {
  if (!sheet) return;
  var headers = ["Truck Number", "Year", "Make", "Model", "Type", "VIN", "Plate", "State", "Status", "Mileage", "Assigned Route", "Assigned Driver", "DOT Inspection Due", "Insurance Policy", "Last Service Date", "Open Issue Description"];
  var rows = [headers];

  fleet.forEach(function(t) {
    rows.push([
      sanitizeCell(t.truckNumber),
      parseInt(t.year, 10) || 2024,
      sanitizeCell(t.make),
      sanitizeCell(t.model),
      sanitizeCell(t.type || "Delivery Truck"),
      sanitizeCell(t.vin),
      sanitizeCell(t.plate),
      sanitizeCell(t.plateState),
      sanitizeCell(t.status || "in_service"),
      parseInt(t.mileage, 10) || 0,
      sanitizeCell(t.route),
      sanitizeCell(t.driver),
      sanitizeCell(t.dotInspection),
      sanitizeCell(t.insurancePolicy),
      sanitizeCell(t.lastServiceDate),
      sanitizeCell(t.activeIssue ? t.activeIssue.desc : "None")
    ]);
  });

  sheet.clearContents();
  sheet.getRange(1, 1, rows.length, headers.length).setValues(rows);
  formatHeaderRow(sheet, "#f97316");
  sheet.autoResizeColumns(1, headers.length);
}

function writeDriversSheet(sheet, drivers) {
  if (!sheet) return;
  var headers = ["Driver ID", "Full Name", "Role", "Phone", "Default Route", "Default Truck", "Base Availability Days", "Average ILS %", "Notes"];
  var rows = [headers];

  drivers.forEach(function(d) {
    rows.push([
      sanitizeCell(d.id),
      sanitizeCell(d.name),
      d.isManager ? "Manager (M)" : "Delivery Driver",
      sanitizeCell(d.phone),
      sanitizeCell(d.defaultRoute),
      sanitizeCell(d.defaultTruck),
      sanitizeCell((d.baseDays || []).join(", ")),
      parseFloat(d.ils) || 100,
      sanitizeCell(d.notes)
    ]);
  });

  sheet.clearContents();
  sheet.getRange(1, 1, rows.length, headers.length).setValues(rows);
  formatHeaderRow(sheet, "#059669");
  sheet.autoResizeColumns(1, headers.length);
}

function writeSafetySheet(sheet, drivers, schedule) {
  if (!sheet) return;
  var headers = ["Date", "Gregorian Quarter", "Driver Name", "Role", "Route", "Event Category", "Severity", "Title", "Description & Coaching Notes"];
  var rows = [headers];

  drivers.forEach(function(d) {
    (d.history || []).forEach(function(evt) {
      var qNum = getQuarterFromDate(evt.date || "2026-08-22");
      rows.push([
        sanitizeCell(evt.date || "2026-08-22"),
        "Q" + qNum + " 2026",
        sanitizeCell(d.name),
        d.isManager ? "Manager (M)" : "Driver",
        sanitizeCell(d.defaultRoute || "Route 104"),
        sanitizeCell(evt.type || "COACHING"),
        sanitizeCell((evt.severity || "medium").toUpperCase()),
        sanitizeCell(evt.title || "Safety Event"),
        sanitizeCell(evt.desc || "")
      ]);
    });
  });

  if (schedule && Array.isArray(schedule)) {
    schedule.forEach(function(s) {
      if (s.safetyIssue) {
        var qNum = getQuarterFromDate(s.date);
        rows.push([
          sanitizeCell(s.date),
          "Q" + qNum + " 2026",
          sanitizeCell(s.driver),
          "Driver",
          sanitizeCell(s.route || "Route 104"),
          "SHIFT_FLAG",
          "HIGH",
          "Shift Safety Flagged",
          sanitizeCell(s.notes || "Flagged on daily dispatch tile")
        ]);
      }
    });
  }

  sheet.clearContents();
  sheet.getRange(1, 1, rows.length, headers.length).setValues(rows);
  formatHeaderRow(sheet, "#dc2626");
  sheet.autoResizeColumns(1, headers.length);
}

function createDedicatedWeeklyTabs(ss, drivers, schedule, currentWeekStart) {
  var weekStartDates = [];
  if (currentWeekStart) weekStartDates.push(currentWeekStart);

  schedule.forEach(function(s) {
    if (s.date) {
      var satStr = getSaturdayOfWeek(s.date);
      if (satStr && weekStartDates.indexOf(satStr) === -1) {
        weekStartDates.push(satStr);
      }
    }
  });

  weekStartDates.forEach(function(satStr) {
    writeSingleWeeklyMatrixTab(ss, satStr, drivers, schedule);
  });
}

function writeSingleWeeklyMatrixTab(ss, satDateStr, drivers, schedule) {
  var weekDates = get7DaysFromSaturday(satDateStr);
  var tabName = "Week_" + weekDates[0].monthShort + weekDates[0].dayNum + "-" + weekDates[6].monthShort + weekDates[6].dayNum;

  var sheet = ss.getSheetByName(tabName);
  if (!sheet) {
    sheet = ss.insertSheet(tabName);
  }

  var headers = [
    "Driver Name",
    "Role",
    "Default Route",
    "Default Truck",
    "SAT (" + weekDates[0].monthDay + ")",
    "SUN (" + weekDates[1].monthDay + ")",
    "MON (" + weekDates[2].monthDay + ")",
    "Tues (" + weekDates[3].monthDay + ")",
    "WED (" + weekDates[4].monthDay + ")",
    "THU (" + weekDates[5].monthDay + ")",
    "FRI (" + weekDates[6].monthDay + ")",
    "Total Shifts",
    "Weekly ILS % (>=99.5% Days / Total)"
  ];

  var rows = [headers];

  drivers.forEach(function(d) {
    var activeShiftsCount = 0;
    var dayCells = weekDates.map(function(w) {
      var shift = null;
      for (var i = 0; i < schedule.length; i++) {
        if (schedule[i].driver === d.name && schedule[i].date === w.dateStr) {
          shift = schedule[i];
          break;
        }
      }
      if (shift && shift.dutyStatus === "working") {
        activeShiftsCount++;
        var route = shift.route || d.defaultRoute || "Route";
        var truck = shift.truckNumber || d.defaultTruck || "";
        var ils = shift.ils ? " (" + shift.ils + "%)" : "";
        var flag = shift.safetyIssue ? " [SAFETY]" : "";
        return sanitizeCell(route + " (#" + truck + ")" + ils + flag);
      } else if (shift && shift.dutyStatus === "requested_off") {
        return "REQ. OFF";
      } else if (d.baseDays && d.baseDays.indexOf(w.dayCode) !== -1 && (!shift || shift.dutyStatus !== "off")) {
        activeShiftsCount++;
        return sanitizeCell((d.defaultRoute || "Route") + " (#" + (d.defaultTruck || "") + ")");
      }
      return "OFF";
    });

    var compliantShiftsCount = 0;
    for (var j = 0; j < weekDates.length; j++) {
      var wDate = weekDates[j].dateStr;
      for (var k = 0; k < schedule.length; k++) {
        if (schedule[k].driver === d.name && schedule[k].date === wDate && schedule[k].dutyStatus === "working") {
          var sVal = (schedule[k].ils !== undefined && schedule[k].ils !== null) ? parseFloat(schedule[k].ils) : (parseFloat(d.ils) || 100);
          if (sVal >= 99.5) compliantShiftsCount++;
          break;
        }
      }
    }
    var weeklyIlsStr = activeShiftsCount > 0 ? ((compliantShiftsCount / activeShiftsCount) * 100).toFixed(0) + "% (" + compliantShiftsCount + "/" + activeShiftsCount + " days)" : "100%";

    var row = [
      sanitizeCell(d.name),
      d.isManager ? "Manager (M)" : "Delivery Driver",
      sanitizeCell(d.defaultRoute),
      sanitizeCell(d.defaultTruck)
    ].concat(dayCells).concat([activeShiftsCount, weeklyIlsStr]);

    rows.push(row);
  });

  sheet.clearContents();
  sheet.getRange(1, 1, rows.length, headers.length).setValues(rows);
  formatHeaderRow(sheet, "#6366f1");
  sheet.setFrozenColumns(4);
  sheet.autoResizeColumns(1, headers.length);
}

function getSaturdayOfWeek(dateStr) {
  var parts = dateStr.split("-");
  var d = new Date(parseInt(parts[0], 10), parseInt(parts[1], 10) - 1, parseInt(parts[2], 10));
  var day = d.getDay();
  var diff = (day === 6) ? 0 : -(day + 1);
  d.setDate(d.getDate() + diff);
  return Utilities.formatDate(d, "UTC", "yyyy-MM-dd");
}

function get7DaysFromSaturday(satDateStr) {
  var parts = satDateStr.split("-");
  var monthNames = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"];
  var days = ["SAT", "SUN", "MON", "TUES", "WED", "THU", "FRI"];
  var out = [];

  for (var i = 0; i < 7; i++) {
    var d = new Date(parseInt(parts[0], 10), parseInt(parts[1], 10) - 1, parseInt(parts[2], 10) + i);
    var yyyy = d.getFullYear();
    var mm = ("0" + (d.getMonth() + 1)).slice(-2);
    var dd = ("0" + d.getDate()).slice(-2);
    var dateStr = yyyy + "-" + mm + "-" + dd;
    var monthShort = monthNames[d.getMonth()];
    var dayNum = d.getDate();

    out.push({
      dateStr: dateStr,
      dayCode: days[i],
      monthShort: monthShort,
      dayNum: dayNum,
      monthDay: monthShort + " " + dayNum
    });
  }
  return out;
}

function getQuarterFromDate(dateStr) {
  if (!dateStr) return 3;
  var month = parseInt(dateStr.substring(5, 7), 10);
  if (month <= 3) return 1;
  if (month <= 6) return 2;
  if (month <= 9) return 3;
  return 4;
}

🔍 How to Query Your Backend in Google Sheets (Formulas & Examples)

You can create a custom tab in your spreadsheet (e.g. "Manager_Reports") and query any data using standard Google Sheets formulas:

Query all shifts for a specific route:
=QUERY(Weekly_Schedule!A:J, "SELECT A, C, E, F, G WHERE E = 'Route 104'", 1)
Query drivers with low ILS under 98%:
=QUERY(Drivers_Roster!A:I, "SELECT B, E, H WHERE H < 98", 1)
Query trucks currently In Shop / Out of Service:
=QUERY(Fleet_Trucks!A:P, "SELECT A, C, D, I, P WHERE I <> 'in_service'", 1)

✅ Fleet status updated successfully!

MICHROCK LOGISTICS, INC.

Master Driver Availability Roster

Quarterly Safety Summary Report

Multi-Week Volume Report

Mechanic Shop • Active Issues by Truck Number

Fleet Manager Master Data Table