Preventive Maintenance Checks and Services (PMCS)

I generally try to take good care of my car. In the Army we took vehicular maintenance very seriously and regularly conducted PMCS, or “Preventive Maintenance Checks and Services”. Every unit that I was in would dedicate at least an entire day of the week, every week, to checking POL (Petroleum, Oil, and Lubricants), transmission, tires, and the like. Called “Motorpool Mondays” some units went as far as to lock their Soldiers in the garage until the end of the duty day to ensure due diligence was done, OSHA regulations be damned.

So, when my Nissan Juke began making odd noises I was mortified. I had religiously followed the maintenance schedule since purchasing the car in 2013. When my car was broken into in 2016 and the owner’s manual was stolen along with everything else in my glove compartment, I paid the $98 to buy a new manual from Nissan just so I could continue to record maintenance conducted in the manual appendix.

When I took the car into my mechanic I found out that not only had I worn through to the metal on my front brake pads, but that the brake rotors had been irreparably damaged as well. All told it was going to be a $540 repair job, two thirds of which could have been avoided if I had replaced my brake pads earlier. What had gone wrong? Unfortunately for me my mechanic had neglected to inspect the brake pads the last couple visits. This allowed them to wear down until they damaged the brake rotors. It was an expensive mistake for me, but one I knew I could avoid going forward by implementing two changes.

The first was to create a Google Sheets document. I transcribed my vehicle’s maintenance schedule on the first tab, labelled “Schedule”. The second tab I labeled “MaintenanceLog” and copied over the service charges recorded on the receipts I had saved over the past eight years. Once that was done, I created an Apps Script (full script included at the end of the article for reference). Apps Scripts are Google’s version of the Visual Basic Macros you might use with a Microsoft Excel workbook. My script correlates entries on the “Schedule” tab with those on the “MaintenanceLog” tab and highlight items that are overdue, either in terms of mileage or date of last service. So now the next time the sticker on the inside of my windshield says I’m due for maintenance I can check my excel sheet and see exactly what needs to be worked on.

For the second change I resolved to conduct as much maintenance as feasible on my own. Truth be told, I had grown complacent. I’ve always personally done minor maintenance on my car like replacing the headlights, the sparkplugs, and the battery as necessary. Everything else I had outsourced to my mechanic. Oil, transmission, transfer case, coolant, you name it. I simply drove my car until my odometer matched the mileage on the sticker and then brought it in to let them do the service. I figured that they had the experience to do the necessary maintenance faster and more thoroughly than I ever could. Which is why I think it fitting to end with a quote from Robert Pirsig that I may have dismissed out of hand in my first reading of Zen and the Art of Motorcycle Maintenance.

“[The mechanics] were like spectators. You had the feeling they had just wandered in there themselves and somebody had handed them a wrench. There was no identification with the job. No saying, ‘I am a mechanic.’ At 5 P.M. or whenever their eight hours were in, you knew they would cut it off and not have another thought about their work … We were all spectators. And it occurred to me there is no manual that deals with the real business of motorcycle maintenance, the most important aspect of all … Caring about what you are doing”

I’m not saying that the mechanics weren’t professional. But because they are professionals and not personally invested, the stakes aren’t as high for them. My car was just one of many worked on that day. What I’ve realized after all this is that the onus is on me (and other drivers) to care about the vehicles we drive, because we’re the ones that rely on them. We can outsource expertise to a certain extent, but not responsibility. Hopefully with this epiphany we can take a little better care of the things and people that matter to us.

The earlier mentioned Apps Script, as promised:

function onOpen(e) {
  highlightSchedule();
}

function onEdit(e) {
  highlightSchedule();
}

function highlightSchedule() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const schedule = ss.getSheetByName('Schedule');
  const logSheet = ss.getSheetByName('MaintenanceLog');
  if (!schedule || !logSheet) {
    throw new Error('Sheets named "Schedule" and "MaintenanceLog" must exist in this spreadsheet.');
  }

  // Read Schedule values
  const schedRange = schedule.getDataRange();
  const schedValues = schedRange.getValues();
  if (schedValues.length < 2) return; // no data

  const schedHeader = schedValues[0].map(String);
  const colIdx = {
    item: findHeaderIndex(schedHeader, 'Service Item'),
    months: findHeaderIndex(schedHeader, 'Interval (Months)'),
    miles: findHeaderIndex(schedHeader, 'Interval (Miles)')
  };
  if (colIdx.item === -1 || colIdx.months === -1 || colIdx.miles === -1) {
    throw new Error('Schedule sheet must have headers: Service Item, Interval (Months), Interval (Miles).');
  }

  // Read current reading and current date overrides from anywhere in Schedule
  const contextInfo = extractContextInfoFromSchedule(schedValues);
  const currentReading = contextInfo.currentReading; // number or null
  const today = contextInfo.currentDate || new Date();

  // Build last-service index from MaintenanceLog
  const logRange = logSheet.getDataRange();
  const logValues = logRange.getValues();
  if (logValues.length < 2) return; // no log data

  const logHeader = logValues[0].map(String);
  const logIdx = {
    date: findHeaderIndex(logHeader, 'Date of Service'),
    mileage: findHeaderIndex(logHeader, 'Mileage'),
    work: findHeaderIndex(logHeader, 'Work Performed')
  };
  if (logIdx.date === -1 || logIdx.mileage === -1 || logIdx.work === -1) {
    throw new Error('MaintenanceLog sheet must have headers: Date of Service, Mileage, Work Performed.');
  }

  // Collect list of schedule items to match against log entries
  const scheduleItems = [];
  for (let r = 1; r < schedValues.length; r++) {
    const name = String(schedValues[r][colIdx.item]).trim();
    if (name) scheduleItems.push(name);
  }

  Logger.log('scheduleItems: ' + scheduleItems);

  const lastServiceMap = buildLastServiceIndex(logValues, logIdx, scheduleItems);

  // Clear existing backgrounds for the schedule table region
  const lastRow = schedule.getLastRow();
  const lastCol = schedule.getLastColumn();
  schedule.getRange(2, 1, Math.max(0, lastRow - 1), lastCol).setBackground(null);

  // Apply color rules row by row
  for (let r = 1; r < schedValues.length; r++) {
    const rowVals = schedValues[r];
    const itemName = String(rowVals[colIdx.item]).trim();
    
    if (!itemName) continue; // skip empty rows

    const intervalMonths = toNumber(rowVals[colIdx.months]);
    const intervalMiles = toNumber(rowVals[colIdx.miles]);

    const last = lastServiceMap[normalize(itemName)] || null;

    // If a schedule item has no record in the maintenance log, highlight grey
    if (!last) {
      schedule.getRange(r + 1, 1, 1, lastCol).setBackground('#dddddd');
      continue;
    }

    // Logger.log('itemName: ' + itemName + '; last service date: ' + last.date + ', last service mileage: ' + last.mileage);

    let colorTime = null;
    let colorMiles = null;

    // Time-based rule
    if (last && last.date && isFinite(intervalMonths)) {
      const monthsSince = monthsBetween(last.date, today);
      if (monthsSince >= intervalMonths) {
        colorTime = 'red';
      } else if (intervalMonths - monthsSince <= 1) {
        colorTime = 'yellow';
      }
    }

    // Mileage-based rule
    if (last && isFinite(last.mileage) && isFinite(intervalMiles) && isFinite(currentReading)) {
      const milesSince = currentReading - last.mileage;
      if (milesSince >= intervalMiles) {
        colorMiles = 'red';
      } else if (intervalMiles - milesSince <= 1000) {
        colorMiles = 'yellow';
      }
    }

    const finalColor = resolveColor(colorTime, colorMiles);
    if (finalColor) {
      schedule.getRange(r + 1, 1, 1, lastCol).setBackground(finalColor);
    }
  }
}

function resolveColor(colorTime, colorMiles) {
  // Favor red over yellow, yellow over none
  if (colorTime === 'red' || colorMiles === 'red') return 'red';
  if (colorTime === 'yellow' || colorMiles === 'yellow') return 'yellow';
  return null;
}

function findHeaderIndex(headerRow, name) {
  const target = name.toLowerCase();
  for (let i = 0; i < headerRow.length; i++) {
    if (String(headerRow[i]).trim().toLowerCase() === target) return i;
  }
  return -1;
}

function toNumber(v) {
  if (v === null || v === undefined) return NaN;
  if (typeof v === 'number') return v;
  return Number(String(v).replace(/[$,\s]/g, ''));
}

function parseLogDate(v) {
  if (v instanceof Date) return v;
  const s = String(v).trim();
  // Accept YYYY-MM-DD, YYYY/MM/DD, or YYYYMMDD
  const m = s.match(/^(\d{4})[-\/]?(\d{2})[-\/]?(\d{2})$/);
  if (m) {
    const y = Number(m[1]);
    const mon = Number(m[2]);
    const d = Number(m[3]);
    return new Date(y, mon - 1, d);
  }
  // Fallback: try Date parse
  const d2 = new Date(s);
  if (!isNaN(d2.getTime())) return d2;
  return null;
}

function monthsBetween(startDate, endDate) {
  const msPerDay = 24 * 60 * 60 * 1000;
  const days = (endDate.getTime() - startDate.getTime()) / msPerDay;
  return days / 30; // approximate months
}

function normalize(s) {
  return String(s).toLowerCase().replace(/[^a-z0-9\s]/g, ' ').replace(/\s+/g, ' ').trim();
}

function tokenSet(s) {
  return new Set(normalize(s).split(' ').filter(Boolean));
}

function matchesWorkPerformed(scheduleItem, workString) {
  // Token-based match: all tokens of schedule item must appear in work string
  const itemTokens = tokenSet(scheduleItem);
  const workTokens = tokenSet(workString);
  for (const t of itemTokens) {
    if (!workTokens.has(t)) return false;
  }
  return true;
}

function buildLastServiceIndex(logValues, logIdx, scheduleItems) {
  // Produce a map from normalized schedule item name to {date, mileage} for the most recent log entry
  const map = {};
  const normalizedItems = scheduleItems.map((n) => ({ raw: n, norm: normalize(n) }));

  for (let r = 1; r < logValues.length; r++) {
    const dateVal = parseLogDate(logValues[r][logIdx.date]);
    const mileageVal = toNumber(logValues[r][logIdx.mileage]);
    const workVal = String(logValues[r][logIdx.work] || '').trim();
    if (!workVal) continue;
    for (const it of normalizedItems) {
      if (matchesWorkPerformed(it.raw, workVal)) {
        const prev = map[it.norm];
        if (!prev || (dateVal && prev.date && dateVal.getTime() > prev.date.getTime())) {
          map[it.norm] = { date: dateVal || prev?.date || null, mileage: isFinite(mileageVal) ? mileageVal : prev?.mileage || NaN };
        }
      }
    }
  }
  return map;
}

function extractContextInfoFromSchedule(schedValues) {
  let currentReading = null;
  let currentDate = null;

  for (let r = 0; r < schedValues.length; r++) {
    for (let c = 0; c < schedValues[r].length - 1; c++) {
      const label = String(schedValues[r][c]).trim().toLowerCase();
      const val = schedValues[r][c + 1];
      if (label === 'current reading') {
        currentReading = toNumber(val);
      } else if (label === 'date') {
        currentDate = parseLogDate(val);
      }
    }
  }
  Logger.log('currentReading: ' + currentReading);
  Logger.log('currentDate: ' + currentDate);
  return { currentReading, currentDate };
}