The Monday Money Report

/**
 * The Monday Money Report  -  by Helm Systems (helmsystems.biz)
 *
 * Emails you every Monday morning with:
 *   1. Money waiting on you   (invoices past their due date)
 *   2. Estimates going cold   (sent, but nobody has followed up)
 *   3. Leads that were missed (nobody replied in time)
 *   4. This week vs. last week (leads, estimates sent, cash collected)
 *
 * Every item has a one-tap button that opens a ready-to-send email.
 *
 * It only reads the Leads, Estimates, Invoices and Settings tabs of THIS sheet,
 * and only sends one email: to the address you put in Settings.
 * Nothing leaves your Google account.
 *
 * Setup: Extensions > Apps Script > paste this over everything > Save >
 *        pick "turnOn" in the dropdown > Run.
 */

var SHEET_LEADS = "Leads";
var SHEET_ESTIMATES = "Estimates";
var SHEET_INVOICES = "Invoices";
var SHEET_SETTINGS = "Settings";
var DAY_MS = 86400000;

// ---------------------------------------------------------------------------
// Menu and switches
// ---------------------------------------------------------------------------

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("Monday Report")
    .addItem("Email me a report now", "sendMondayReport")
    .addItem("Turn on the weekly email", "turnOn")
    .addItem("Turn it off", "turnOff")
    .addToUi();
}

/** Schedules the weekly email, then sends one right away so you can see it work. */
function turnOn() {
  var s = readSettings_(SpreadsheetApp.getActiveSpreadsheet());
  removeTriggers_();
  ScriptApp.newTrigger("sendMondayReport")
    .timeBased()
    .onWeekDay(weekDay_(s.sendDay))
    .atHour(s.sendHour)
    .create();
  sendMondayReport();
  notify_(
    "Done. A report is on its way to " +
      s.to +
      ", and the next one arrives every " +
      s.sendDay +
      " around " +
      s.sendHour +
      ":00."
  );
}

function turnOff() {
  removeTriggers_();
  notify_("The weekly email is off. Your data is untouched.");
}

// ---------------------------------------------------------------------------
// The report
// ---------------------------------------------------------------------------

function sendMondayReport() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = readSettings_(ss);
  if (!s.to) {
    throw new Error('Add your email address next to "Send the report to" on the Settings tab.');
  }
  var data = {
    leads: readRows_(ss, SHEET_LEADS),
    estimates: readRows_(ss, SHEET_ESTIMATES),
    invoices: readRows_(ss, SHEET_INVOICES),
  };
  var report = buildReport_(data, s, new Date());
  MailApp.sendEmail({
    to: s.to,
    subject: subjectLine_(report, s),
    body: renderText_(report, s),
    htmlBody: renderHtml_(report, s),
    name: "Monday Money Report",
  });
}

/** Pure function: rows in, numbers out. No Google services used. */
function buildReport_(data, s, now) {
  var today = startOfDay_(now);
  var r = {
    overdue: [],
    cold: [],
    missed: [],
    samples: 0,
    week: {
      leads: { now: 0, before: 0 },
      sent: { now: 0, before: 0 },
      cash: { now: 0, before: 0 },
    },
  };

  data.invoices.forEach(function (row) {
    var customer = text_(row[1]);
    if (!customer) return;
    if (isSample_(customer)) r.samples++;
    var amount = money_(row[3]);
    var due = toDate_(row[4]);
    var paid = toDate_(row[5]);
    if (paid) {
      bucket_(r.week.cash, daysBetween_(paid, today), amount);
    } else if (due && due < today) {
      r.overdue.push({
        customer: customer,
        email: text_(row[2]),
        amount: amount,
        daysLate: daysBetween_(due, today),
      });
    }
  });

  data.estimates.forEach(function (row) {
    var customer = text_(row[1]);
    if (!customer) return;
    if (isSample_(customer)) r.samples++;
    var amount = money_(row[3]);
    var sent = toDate_(row[0]);
    if (sent) bucket_(r.week.sent, daysBetween_(sent, today), amount);
    if (isClosed_(row[5]) || !sent) return;
    var lastTouch = toDate_(row[4]) || sent;
    var quiet = daysBetween_(lastTouch, today);
    if (quiet >= s.staleDays) {
      r.cold.push({
        customer: customer,
        email: text_(row[2]),
        amount: amount,
        daysQuiet: quiet,
      });
    }
  });

  data.leads.forEach(function (row) {
    var name = text_(row[1]);
    if (!name) return;
    if (isSample_(name)) r.samples++;
    var received = toDate_(row[0]);
    if (received) bucket_(r.week.leads, daysBetween_(received, today), 1);
    if (isClosed_(row[5]) || !received || toDate_(row[4])) return;
    var hours = (now.getTime() - received.getTime()) / 3600000;
    if (hours > s.replyHours) {
      r.missed.push({
        name: name,
        contact: text_(row[2]),
        source: text_(row[3]),
        daysAgo: daysBetween_(received, today),
      });
    }
  });

  r.overdue.sort(function (a, b) {
    return b.amount - a.amount;
  });
  r.cold.sort(function (a, b) {
    return b.amount - a.amount;
  });
  r.missed.sort(function (a, b) {
    return b.daysAgo - a.daysAgo;
  });
  r.overdueTotal = sum_(r.overdue, "amount");
  r.coldTotal = sum_(r.cold, "amount");
  return r;
}

/** Counts a value into "this week" (0-6 days ago) or "the week before" (7-13). */
function bucket_(target, daysAgo, value) {
  if (daysAgo >= 0 && daysAgo <= 6) target.now += value;
  else if (daysAgo >= 7 && daysAgo <= 13) target.before += value;
}

// ---------------------------------------------------------------------------
// Email: subject, plain text, HTML
// ---------------------------------------------------------------------------

function subjectLine_(r, s) {
  var biz = s.business ? s.business + ": " : "";
  if (r.overdueTotal > 0) {
    return biz + "Monday Money Report - " + dollars_(r.overdueTotal) + " waiting on you";
  }
  if (r.cold.length || r.missed.length) {
    return biz + "Monday Money Report - " + (r.cold.length + r.missed.length) + " people to follow up with";
  }
  return biz + "Monday Money Report - all clear this week";
}

function renderText_(r, s) {
  var lines = [];
  lines.push("MONDAY MONEY REPORT" + (s.business ? " - " + s.business : ""));
  lines.push("");
  lines.push("MONEY WAITING ON YOU: " + dollars_(r.overdueTotal) + " across " + r.overdue.length + " overdue invoices");
  r.overdue.slice(0, 5).forEach(function (o) {
    lines.push("  - " + o.customer + ": " + dollars_(o.amount) + ", " + days_(o.daysLate) + " late");
  });
  lines.push("");
  lines.push("ESTIMATES GOING COLD: " + r.cold.length + " worth " + dollars_(r.coldTotal));
  r.cold.slice(0, 5).forEach(function (c) {
    lines.push("  - " + c.customer + ": " + dollars_(c.amount) + ", quiet for " + days_(c.daysQuiet));
  });
  lines.push("");
  lines.push("LEADS NOBODY REPLIED TO: " + r.missed.length);
  r.missed.slice(0, 5).forEach(function (m) {
    lines.push("  - " + m.name + " (" + (m.contact || "no contact") + "), " + days_(m.daysAgo) + " ago");
  });
  lines.push("");
  lines.push("LAST 7 DAYS vs THE WEEK BEFORE");
  lines.push("  New leads: " + r.week.leads.now + " vs " + r.week.leads.before);
  lines.push("  Estimates sent: " + dollars_(r.week.sent.now) + " vs " + dollars_(r.week.sent.before));
  lines.push("  Cash collected: " + dollars_(r.week.cash.now) + " vs " + dollars_(r.week.cash.before));
  if (r.samples) {
    lines.push("");
    lines.push("Heads up: " + r.samples + " SAMPLE rows are still in your sheet. Delete them for real numbers.");
  }
  lines.push("");
  lines.push("Made by Helm Systems. These numbers come from the rows in your sheet. helmsystems.biz");
  return lines.join("\n");
}

function renderHtml_(r, s) {
  var GREEN = "#2C4A3B";
  var RUST = "#B5502A";
  var PAPER = "#F1EADD";
  var CARD = "#FBF7EF";
  var INK = "#1A1712";
  var SOFT = "#6B6657";
  var font = "font-family:Helvetica,Arial,sans-serif;";

  function section(title, big, sub, body) {
    return (
      '<tr><td style="padding:22px 28px 6px 28px;border-top:1px solid #D8D0BE;">' +
      '<div style="' + font + "font-size:11px;letter-spacing:1.5px;text-transform:uppercase;color:" + SOFT + ';">' + esc_(title) + "</div>" +
      (big
        ? '<div style="font-family:Georgia,serif;font-size:30px;font-weight:bold;color:' + GREEN + ';margin-top:6px;">' + esc_(big) + "</div>"
        : "") +
      (sub
        ? '<div style="' + font + "font-size:14px;color:" + SOFT + ';margin-top:2px;">' + esc_(sub) + "</div>"
        : "") +
      "</td></tr>" +
      '<tr><td style="padding:6px 28px 18px 28px;">' + body + "</td></tr>"
    );
  }

  function row(left, right, link) {
    return (
      '<table width="100%" cellpadding="0" cellspacing="0" style="border-bottom:1px solid #E6DFCE;"><tr>' +
      '<td style="' + font + "padding:10px 0;font-size:14px;color:" + INK + ';">' + left + "</td>" +
      '<td align="right" style="' + font + "padding:10px 0;font-size:14px;color:" + SOFT + ';white-space:nowrap;">' + right + "</td>" +
      '<td width="110" align="right" style="padding:10px 0 10px 12px;white-space:nowrap;">' + (link || "") + "</td>" +
      "</tr></table>"
    );
  }

  function button(label, href) {
    if (!href) return "";
    return (
      '<a href="' + esc_(href) + '" style="' + font + "font-size:12px;font-weight:bold;color:#fff;background:" + RUST +
      ';text-decoration:none;padding:6px 10px;display:inline-block;">' + esc_(label) + "</a>"
    );
  }

  function more(list) {
    return list.length > 5
      ? '<div style="' + font + "font-size:13px;color:" + SOFT + ';padding-top:8px;">+ ' + (list.length - 5) + " more in your sheet</div>"
      : "";
  }

  function empty(msg) {
    return '<div style="' + font + "font-size:14px;color:" + SOFT + ';padding:8px 0;">' + msg + "</div>";
  }

  var overdueRows = r.overdue.slice(0, 5).map(function (o) {
    return row(
      "<strong>" + esc_(o.customer) + "</strong>",
      dollars_(o.amount) + " &middot; " + days_(o.daysLate) + " late",
      button("Send reminder", o.email && mailto_(o.email, "Invoice reminder", invoiceBody_(o, s)))
    );
  });
  var coldRows = r.cold.slice(0, 5).map(function (c) {
    return row(
      "<strong>" + esc_(c.customer) + "</strong>",
      dollars_(c.amount) + " &middot; quiet " + days_(c.daysQuiet),
      button("Follow up", c.email && mailto_(c.email, "Following up on your estimate", estimateBody_(c, s)))
    );
  });
  var missedRows = r.missed.slice(0, 5).map(function (m) {
    var isEmail = m.contact.indexOf("@") > -1;
    var href = isEmail
      ? mailto_(m.contact, "Thanks for reaching out", leadBody_(m, s))
      : m.contact
        ? "tel:" + m.contact.replace(/[^0-9+]/g, "")
        : "";
    return row(
      "<strong>" + esc_(m.name) + "</strong>" + (m.source ? " <span style=\"color:" + SOFT + ';">via ' + esc_(m.source) + "</span>" : ""),
      days_(m.daysAgo) + " ago",
      button(isEmail ? "Reply" : "Call", href)
    );
  });

  function trend(label, now, before, isMoney) {
    var fmt = isMoney ? dollars_ : String;
    var diff = now - before;
    var color = diff > 0 ? GREEN : diff < 0 ? RUST : SOFT;
    var word = diff > 0 ? "up" : diff < 0 ? "down" : "flat";
    var delta = diff === 0 ? "flat" : word + " " + fmt(Math.abs(diff));
    return (
      '<td width="33%" valign="top" style="padding:4px 8px 4px 0;">' +
      '<div style="' + font + "font-size:11px;letter-spacing:1px;text-transform:uppercase;color:" + SOFT + ';">' + label + "</div>" +
      '<div style="font-family:Georgia,serif;font-size:24px;font-weight:bold;color:' + GREEN + ';">' + fmt(now) + "</div>" +
      '<div style="' + font + "font-size:12px;color:" + color + ';">' + delta + "</div></td>"
    );
  }

  var weekBody =
    '<table width="100%" cellpadding="0" cellspacing="0"><tr>' +
    trend("New leads", r.week.leads.now, r.week.leads.before, false) +
    trend("Estimates sent", r.week.sent.now, r.week.sent.before, true) +
    trend("Cash collected", r.week.cash.now, r.week.cash.before, true) +
    "</tr></table>";

  var sampleNote = r.samples
    ? '<tr><td style="padding:14px 28px;background:#FFF4D6;' + font + 'font-size:13px;color:' + INK + ';">' +
      "<strong>Heads up:</strong> " + r.samples + " SAMPLE rows are still in your sheet, so these numbers are practice numbers. Delete the sample rows and your real ones show up here.</td></tr>"
    : "";

  return (
    '<div style="background:' + PAPER + ';padding:24px 0;">' +
    '<table align="center" width="600" cellpadding="0" cellspacing="0" style="max-width:600px;width:100%;background:' + CARD + ';border:1px solid #D8D0BE;">' +
    '<tr><td style="background:' + GREEN + ';padding:22px 28px;">' +
    '<div style="' + font + 'font-size:11px;letter-spacing:1.5px;text-transform:uppercase;color:#CFE0D4;">' + (s.business ? esc_(s.business) + " &middot; " : "") + "Monday Money Report</div>" +
    '<div style="font-family:Georgia,serif;font-size:22px;color:#fff;margin-top:6px;">Here is where your week stands.</div></td></tr>' +
    sampleNote +
    section(
      "Money waiting on you",
      dollars_(r.overdueTotal),
      r.overdue.length + (r.overdue.length === 1 ? " invoice is" : " invoices are") + " past due",
      overdueRows.length ? overdueRows.join("") + more(r.overdue) : empty("Nothing is overdue.")
    ) +
    section(
      "Estimates going cold",
      dollars_(r.coldTotal),
      r.cold.length + (r.cold.length === 1 ? " estimate has" : " estimates have") + " been quiet for " + s.staleDays + "+ days",
      coldRows.length ? coldRows.join("") + more(r.cold) : empty("Every open estimate has been followed up.")
    ) +
    section(
      "Leads nobody replied to",
      String(r.missed.length),
      "no first reply within " + s.replyHours + " hours",
      missedRows.length ? missedRows.join("") + more(r.missed) : empty("Every lead got a reply.")
    ) +
    section("Last 7 days vs. the week before", "", "", weekBody) +
    '<tr><td style="padding:16px 28px 22px 28px;border-top:1px solid #D8D0BE;' + font + "font-size:12px;color:" + SOFT + ';">' +
    "These numbers come from the rows in your sheet. Want them to fill in by themselves from your CRM and phone system, with no typing? " +
    '<a href="https://www.helmsystems.biz/#book" style="color:' + RUST + ';">That is what Helm Systems builds.</a></td></tr>' +
    "</table></div>"
  );
}

function invoiceBody_(o, s) {
  return (
    "Hi " + first_(o.customer) + ",\n\nA quick reminder that your invoice for " + dollars_(o.amount) +
    " is now " + days_(o.daysLate) + " past due. If it has already gone out, thank you, and please ignore this. " +
    "If anything is holding it up, just reply and let me know.\n\nThanks,\n" + (s.business || "")
  );
}

function estimateBody_(c, s) {
  return (
    "Hi " + first_(c.customer) + ",\n\nI wanted to check in on the estimate I sent over (" + dollars_(c.amount) +
    "). Do you have any questions I can answer, or anything you would like adjusted?\n\nThanks,\n" + (s.business || "")
  );
}

function leadBody_(m, s) {
  return (
    "Hi " + first_(m.name) + ",\n\nThanks for reaching out, and sorry for the slow reply. " +
    "Are you still looking for help? I can get you scheduled this week.\n\nThanks,\n" + (s.business || "")
  );
}

// ---------------------------------------------------------------------------
// Reading the sheet
// ---------------------------------------------------------------------------

function readSettings_(ss) {
  var sh = ss.getSheetByName(SHEET_SETTINGS);
  var map = {};
  if (sh) {
    sh.getDataRange().getValues().forEach(function (row) {
      map[String(row[0]).trim()] = row[1];
    });
  }
  var hour = parseInt(map["Send at (hour, 0-23)"], 10);
  var day = text_(map["Send on"]) || "Monday";
  return {
    to: text_(map["Send the report to"]),
    business: text_(map["Business name"]),
    replyHours: positive_(map["Reply to new leads within (hours)"], 24),
    staleDays: positive_(map["Follow up on estimates after (days)"], 3),
    sendDay: day,
    sendHour: isNaN(hour) || hour < 0 || hour > 23 ? 7 : hour,
  };
}

function readRows_(ss, name) {
  var sh = ss.getSheetByName(name);
  if (!sh) throw new Error('The "' + name + '" tab is missing. Re-download the workbook.');
  var values = sh.getDataRange().getValues();
  return values.slice(1);
}

// ---------------------------------------------------------------------------
// Small helpers
// ---------------------------------------------------------------------------

function removeTriggers_() {
  ScriptApp.getProjectTriggers().forEach(function (t) {
    if (t.getHandlerFunction() === "sendMondayReport") ScriptApp.deleteTrigger(t);
  });
}

function weekDay_(name) {
  var days = {
    sunday: ScriptApp.WeekDay.SUNDAY,
    monday: ScriptApp.WeekDay.MONDAY,
    tuesday: ScriptApp.WeekDay.TUESDAY,
    wednesday: ScriptApp.WeekDay.WEDNESDAY,
    thursday: ScriptApp.WeekDay.THURSDAY,
    friday: ScriptApp.WeekDay.FRIDAY,
    saturday: ScriptApp.WeekDay.SATURDAY,
  };
  return days[String(name).toLowerCase()] || ScriptApp.WeekDay.MONDAY;
}

function notify_(msg) {
  try {
    SpreadsheetApp.getActiveSpreadsheet().toast(msg, "Monday Report", 10);
  } catch (e) {
    Logger.log(msg);
  }
}

function startOfDay_(d) {
  var x = new Date(d.getTime());
  x.setHours(0, 0, 0, 0);
  return x;
}

function toDate_(v) {
  if (v === "" || v === null || v === undefined) return null;
  var d = v instanceof Date ? v : new Date(v);
  return isNaN(d.getTime()) ? null : startOfDay_(d);
}

function daysBetween_(from, to) {
  return Math.round((to.getTime() - from.getTime()) / DAY_MS);
}

function text_(v) {
  return v === null || v === undefined ? "" : String(v).trim();
}

function money_(v) {
  var n = typeof v === "number" ? v : parseFloat(String(v).replace(/[^0-9.\-]/g, ""));
  return isNaN(n) ? 0 : n;
}

function positive_(v, fallback) {
  var n = parseFloat(v);
  return isNaN(n) || n <= 0 ? fallback : n;
}

function isClosed_(status) {
  var t = text_(status).toLowerCase();
  return t === "won" || t === "lost" || t === "closed" || t === "paid";
}

function isSample_(name) {
  return /^sample\b/i.test(name);
}

function sum_(list, key) {
  return list.reduce(function (t, x) {
    return t + x[key];
  }, 0);
}

function days_(n) {
  return n + (n === 1 ? " day" : " days");
}

function dollars_(n) {
  return "$" + Math.round(n).toString().replace(/\B(?=(\d{3})+(?!\d))/g, ",");
}

function first_(name) {
  return name.replace(/^sample\s*[-–—:]*\s*/i, "").split(/\s+/)[0] || "there";
}

function mailto_(to, subject, body) {
  return "mailto:" + to + "?subject=" + encodeURIComponent(subject) + "&body=" + encodeURIComponent(body);
}

function esc_(s) {
  return String(s)
    .replace(/&/g, "&amp;")
    .replace(/</g, "&lt;")
    .replace(/>/g, "&gt;")
    .replace(/"/g, "&quot;");
}