Flag stale Sheet rows and nudge the owner in Gmail

// Finds rows in a tracking Sheet that nobody has touched for a while and emails each owner one list.
// Paste into Code.gs in a new project at script.google.com, edit CONFIG, run installTriggers() once.

const CONFIG = {
  SPREADSHEET_ID: 'PASTE_THE_SPREADSHEET_ID_HERE',
  SHEET_NAME: 'Tracker',
  ITEM_HEADER: 'Item',             // the sheet's column headings, spelled exactly
  OWNER_HEADER: 'Owner email',
  STATUS_HEADER: 'Status',
  UPDATED_HEADER: 'Last updated',  // stamped automatically when someone edits the row (see stampEdit)
  NUDGED_HEADER: 'Last nudged',    // written by the script
  DONE_STATUSES: ['Done', 'Closed', 'Paid', 'Lost'], // rows with these statuses are never chased
  STALE_DAYS: 7,                   // untouched for this many days counts as stale
  REPEAT_DAYS: 7,                  // do not nudge the same row again for this many days
  MAX_ROWS_PER_EMAIL: 40,
  // Stop well before the 6 minute limit. On a gmail.com account the whole day's
  // trigger time is 90 minutes, so lower this (for example 60 * 1000) there.
  TIME_BUDGET_MS: 4 * 60 * 1000,
  ADMIN_EMAIL: 'ops@example.com',  // hears about rows the script cannot act on
};

const MONTHS = ['jan', 'feb', 'mar', 'apr', 'may', 'jun', 'jul', 'aug', 'sep', 'oct', 'nov', 'dec'];
const EMAIL_PATTERN = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;

function nudgeStaleRows() {
  const lock = LockService.getScriptLock();
  if (!lock.tryLock(30 * 1000)) return;
  const started = Date.now();
  const props = PropertiesService.getScriptProperties();
  // Anything the administrator could not be emailed about last time is carried over.
  let admin = [];
  try { admin = JSON.parse(props.getProperty('pendingAdmin') || '[]'); } catch (e) { admin = []; }
  try {
    const ss = SpreadsheetApp.openById(CONFIG.SPREADSHEET_ID);
    const sheet = ss.getSheetByName(CONFIG.SHEET_NAME);
    if (!sheet) throw new Error('There is no tab called ' + CONFIG.SHEET_NAME);
    const tz = ss.getSpreadsheetTimeZone();
    const today = Utilities.formatDate(new Date(), tz, 'yyyy-MM-dd');

    // One read of the tab; every decision below is made in memory.
    const grid = sheet.getDataRange().getValues();
    const headers = grid[0].map(String);
    const col = {
      item: headers.indexOf(CONFIG.ITEM_HEADER), owner: headers.indexOf(CONFIG.OWNER_HEADER),
      status: headers.indexOf(CONFIG.STATUS_HEADER), updated: headers.indexOf(CONFIG.UPDATED_HEADER),
      nudged: headers.indexOf(CONFIG.NUDGED_HEADER),
    };
    Object.keys(col).forEach(function (k) {
      if (col[k] < 0) throw new Error('A column heading is missing from the sheet: ' + k);
    });

    const byOwner = {};      // owner email -> stale rows, built in one pass
    const startClock = [];   // rows that have no Last updated yet
    for (let i = 1; i < grid.length; i++) {
      const row = grid[i];
      const item = String(row[col.item]).trim();
      if (item === '') continue;
      const status = String(row[col.status]).trim().toLowerCase();
      if (CONFIG.DONE_STATUSES.some(function (s) { return s.toLowerCase() === status; })) continue;

      if (String(row[col.updated]).trim() === '') { startClock.push(i + 1); continue; }
      const updated = dateKey_(row[col.updated], tz);
      if (!updated) { admin.push('Row ' + (i + 1) + ' (' + item + '): Last updated could not be read'); continue; }
      if (daysBetween_(updated, today) < CONFIG.STALE_DAYS) continue;

      const nudgedRaw = String(row[col.nudged]).trim();
      const nudged = nudgedRaw === '' ? null : dateKey_(row[col.nudged], tz);
      // An unreadable Last nudged is reported, and the row is treated as never nudged.
      if (nudgedRaw !== '' && !nudged) admin.push('Row ' + (i + 1) + ' (' + item + '): Last nudged could not be read, so the row was treated as not nudged yet');
      if (nudged && daysBetween_(nudged, today) < CONFIG.REPEAT_DAYS) continue;

      const owner = String(row[col.owner]).trim().toLowerCase();
      if (!EMAIL_PATTERN.test(owner)) { admin.push('Row ' + (i + 1) + ' (' + item + '): no valid owner email'); continue; }
      (byOwner[owner] = byOwner[owner] || []).push({ rowNumber: i + 1, item: item, days: daysBetween_(updated, today) });
    }

    // One email per owner. A row is marked as nudged only after its email has gone.
    const nudgedCells = [];
    let held = 0;
    Object.keys(byOwner).forEach(function (owner) {
      if (Date.now() - started > CONFIG.TIME_BUDGET_MS || MailApp.getRemainingDailyQuota() < 1) { held++; return; } // tomorrow's run carries on
      const list = byOwner[owner];
      const shown = list.slice(0, CONFIG.MAX_ROWS_PER_EMAIL);
      const lines = shown.map(function (r) { return '- Row ' + r.rowNumber + ': ' + r.item + ' (' + r.days + ' days untouched)'; });
      if (list.length > shown.length) lines.push('...and ' + (list.length - shown.length) + ' more in the sheet.');
      try {
        MailApp.sendEmail(owner, 'Tracker: ' + list.length + ' item(s) have gone quiet',
          'These items have not been updated for ' + CONFIG.STALE_DAYS + ' days or more:\n\n' + lines.join('\n') +
          '\n\nOpen the tracker: ' + ss.getUrl() + '\nUpdate the row, or set its Status to Done, and it stops appearing here.');
        list.forEach(function (r) { nudgedCells.push(toA1_(col.nudged, r.rowNumber)); });
      } catch (err) {
        admin.push('Could not email ' + owner + ': ' + err.message);
      }
    });

    if (held) admin.push(held + ' owner(s) were not emailed this run because the time budget or the daily email quota ran out. They are picked up by the next run.');

    // Two batched writes: the nudge dates, and a start date for rows that had none.
    if (nudgedCells.length) sheet.getRangeList(nudgedCells).setValue(new Date());
    if (startClock.length) {
      sheet.getRangeList(startClock.map(function (n) { return toA1_(col.updated, n); })).setValue(new Date());
      admin.push(startClock.length + ' row(s) had no Last updated date, so their clock started today (rows ' + startClock.slice(0, 10).join(', ') + (startClock.length > 10 ? '...' : '') + ')');
    }
  } catch (err) {
    admin.push('Run failed: ' + err.message);
  } finally {
    lock.releaseLock();
  }
  if (admin.length) {
    admin = admin.filter(function (line, i) { return admin.indexOf(line) === i; });
    try {
      MailApp.sendEmail(CONFIG.ADMIN_EMAIL, 'Tracker nudges: ' + admin.length + ' thing(s) to look at', admin.join('\n'));
      props.deleteProperty('pendingAdmin');
    } catch (err) {
      // Most likely the daily email quota. Keep the report for the next run.
      try {
        while (admin.length > 1 && JSON.stringify(admin).length > 6000) admin.shift();
        props.setProperty('pendingAdmin', JSON.stringify(admin));
      } catch (e) { /* the store itself is down */ }
    }
  }
}

// Installable on-edit trigger: stamps Last updated when a person edits a row.
// It does not run for edits made by scripts, imports or form submissions.
function stampEdit(e) {
  try {
    if (!e || !e.range) return;
    const sheet = e.range.getSheet();
    if (sheet.getName() !== CONFIG.SHEET_NAME || e.range.getLastRow() < 2) return;
    const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    const stampCol = headers.indexOf(CONFIG.UPDATED_HEADER) + 1;
    const nudgedCol = headers.indexOf(CONFIG.NUDGED_HEADER) + 1;
    if (!stampCol) return;
    const first = e.range.getColumn();
    const last = e.range.getLastColumn();
    if (first === last && (first === stampCol || first === nudgedCol)) return; // editing the stamp itself is not an update
    const firstRow = Math.max(e.range.getRow(), 2);
    const count = e.range.getLastRow() - firstRow + 1;
    const stamps = [];
    for (let i = 0; i < count; i++) stamps.push([new Date()]);
    sheet.getRange(firstRow, stampCol, count, 1).setValues(stamps);
  } catch (err) {
    reportStampFailure_(err);
  }
}

// A failing edit trigger would otherwise be silent. At most one email an hour, to the administrator.
function reportStampFailure_(err) {
  try {
    const props = PropertiesService.getScriptProperties();
    const last = Number(props.getProperty('lastStampAlertAt') || 0);
    if (Date.now() - last < 60 * 60 * 1000) return;
    MailApp.sendEmail(CONFIG.ADMIN_EMAIL, 'Tracker: the edit stamp failed', 'Last updated is not being stamped: ' + err.message);
    props.setProperty('lastStampAlertAt', String(Date.now()));
  } catch (e) { /* nothing more can be done from inside a trigger */ }
}

function toA1_(zeroBasedCol, rowNumber) {
  let n = zeroBasedCol + 1;
  let letters = '';
  while (n > 0) { const r = (n - 1) % 26; letters = String.fromCharCode(65 + r) + letters; n = Math.floor((n - 1) / 26); }
  return letters + rowNumber;
}

// Reads a date the way a person might have typed it. Returns 'yyyy-MM-dd' or null.
// Real date cells are read in the spreadsheet's time zone. Text is read day first: 5/11/2026 is 5 November.
function dateKey_(value, tz) {
  if (value instanceof Date) return isNaN(value.getTime()) ? null : Utilities.formatDate(value, tz, 'yyyy-MM-dd');
  const s = String(value).trim();
  let y, m, d, parts;
  if ((parts = /^(\d{4})-(\d{1,2})-(\d{1,2})/.exec(s))) {
    y = +parts[1]; m = +parts[2]; d = +parts[3];
  } else if ((parts = /^(\d{1,2})[\/.\-](\d{1,2})[\/.\-](\d{2}|\d{4})$/.exec(s))) {
    d = +parts[1]; m = +parts[2]; y = +parts[3];
    if (y < 100) y += 2000;
  } else if ((parts = /^(\d{1,2})\s+([A-Za-z]{3,9})\.?,?\s+(\d{4})$/.exec(s))) {
    d = +parts[1]; m = MONTHS.indexOf(parts[2].slice(0, 3).toLowerCase()) + 1; y = +parts[3];
  } else {
    return null;
  }
  const t = new Date(Date.UTC(y, m - 1, d));
  if (m < 1 || t.getUTCFullYear() !== y || t.getUTCMonth() !== m - 1 || t.getUTCDate() !== d) return null; // 31/02 is not a date
  return t.toISOString().slice(0, 10);
}

function daysBetween_(fromKey, toKey) {
  return Math.round((Date.parse(toKey) - Date.parse(fromKey)) / 86400000);
}

// Run once by hand. Safe to run again: it replaces the old triggers.
function installTriggers() {
  ScriptApp.getProjectTriggers()
    .filter(function (t) { return ['nudgeStaleRows', 'stampEdit'].indexOf(t.getHandlerFunction()) !== -1; })
    .forEach(function (t) { ScriptApp.deleteTrigger(t); });
  ScriptApp.newTrigger('nudgeStaleRows').timeBased().everyDays(1).atHour(8).create();
  ScriptApp.newTrigger('stampEdit').forSpreadsheet(CONFIG.SPREADSHEET_ID).onEdit().create();
}