Generate a weekly report from Sheets and email it

// Emails a weekly report from a Google Sheet: headline figures in the body, the whole file as a PDF.
// The trigger runs hourly so a failed send is retried the same day instead of waiting a week.
// Paste into Code.gs in a new project at script.google.com, edit CONFIG, run installTrigger() once.

const CONFIG = {
  REPORT_SPREADSHEET_ID: 'PASTE_THE_SPREADSHEET_ID_HERE', // the file that holds your report
  SUMMARY_SHEET: 'Summary',          // this tab becomes the table in the email body
  RECIPIENTS: ['owner@example.com', 'team@example.com'],
  SUBJECT: 'Weekly report',
  SENDER_NAME: 'Weekly report',
  SEND_DAY: 1,                       // 1 = Monday ... 7 = Sunday
  SEND_FROM_HOUR: 7,                 // 24 hour clock, in the script's time zone
  MAX_ATTEMPTS_PER_DAY: 6,           // a failed send is retried each hour, this many times
  MAX_SUMMARY_ROWS: 40,              // longer tabs are cut off in the email body (the PDF still has everything)
  ALERT_TO: 'ops@example.com',
  ALERT_EVERY_MS: 6 * 60 * 60 * 1000, // at most one failure email per 6 hours
};

function sendWeeklyReport() {
  const lock = LockService.getScriptLock();
  if (!lock.tryLock(30 * 1000)) return; // another run is already sending
  const problems = [];
  let force = false;
  let sent = false;
  try {
    const props = PropertiesService.getScriptProperties();
    const zone = Session.getScriptTimeZone();
    const now = new Date();
    const today = Utilities.formatDate(now, zone, 'yyyy-MM-dd');
    // The trigger runs every hour. Only the send day, from the send hour, does any work, and only once.
    if (Number(Utilities.formatDate(now, zone, 'u')) !== CONFIG.SEND_DAY) return;
    if (Number(Utilities.formatDate(now, zone, 'HH')) < CONFIG.SEND_FROM_HOUR) return;
    if (props.getProperty('lastSentOn') === today) return;

    // A failed send is retried at the next hourly run, up to MAX_ATTEMPTS_PER_DAY times.
    const tries = props.getProperty('attemptsOn') === today ? Number(props.getProperty('attempts') || 0) : 0;
    props.setProperty('attemptsOn', today);
    props.setProperty('attempts', String(tries + 1));
    force = tries + 1 >= CONFIG.MAX_ATTEMPTS_PER_DAY; // the last attempt reports at once, throttle or not
    if (tries >= CONFIG.MAX_ATTEMPTS_PER_DAY) return;

    try {
      const ss = SpreadsheetApp.openById(CONFIG.REPORT_SPREADSHEET_ID);
      SpreadsheetApp.flush(); // apply any pending changes before the export
      const summary = ss.getSheetByName(CONFIG.SUMMARY_SHEET);
      if (!summary) throw new Error('There is no tab called ' + CONFIG.SUMMARY_SHEET);
      const rows = summary.getDataRange().getDisplayValues().slice(0, CONFIG.MAX_SUMMARY_ROWS);
      if (!rows.length || !rows[0].length) throw new Error('The ' + CONFIG.SUMMARY_SHEET + ' tab is empty');

      const pdf = ss.getAs(MimeType.PDF).setName(CONFIG.SUBJECT + ' ' + today + '.pdf');
      MailApp.sendEmail({
        to: CONFIG.RECIPIENTS.join(','),
        subject: CONFIG.SUBJECT + ' - ' + today,
        body: toText_(rows) + '\n\nFull report attached as a PDF.',
        htmlBody: toHtml_(rows) + '<p>Full report attached as a PDF.</p>',
        attachments: [pdf],
        name: CONFIG.SENDER_NAME,
      });
      props.setProperty('lastSentOn', today); // recorded only after the send worked
      sent = true;
    } catch (err) {
      problems.push('Weekly report attempt ' + (tries + 1) + ' of ' + CONFIG.MAX_ATTEMPTS_PER_DAY + ' failed: ' + err.message);
    }
  } catch (err) {
    problems.push('Weekly report run failed: ' + err.message);
  } finally {
    lock.releaseLock();
  }
  // After a send that followed failed attempts, send the held-back problems now rather than next week.
  alertOnce_(problems, force || sent);
}

// Every value from the sheet goes through esc_ before it is placed in HTML.
function toHtml_(rows) {
  const body = rows.map(function (row, i) {
    const tag = i === 0 ? 'th' : 'td';
    return '<tr>' + row.map(function (cell) {
      return '<' + tag + ' style="padding:4px 10px;border:1px solid #ddd;text-align:left">' + esc_(cell) + '</' + tag + '>';
    }).join('') + '</tr>';
  }).join('');
  return '<table style="border-collapse:collapse;font-family:Arial,sans-serif;font-size:14px">' + body + '</table>';
}

function toText_(rows) {
  return rows.map(function (row) { return row.join(' | '); }).join('\n');
}

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

// Keeps the held-back problems in a script property. Oldest entries are dropped first if the text
// would get long (a property value is limited to about 9 KB), and a failure to store never fails the run.
function saveQueue_(props, queue) {
  try {
    while (queue.length > 1 && JSON.stringify(queue).length > 6000) queue.shift();
    props.setProperty('pendingProblems', JSON.stringify(queue));
  } catch (e) { /* the problems are lost only if the store itself is down */ }
}

// Emails the problems to ALERT_TO, at most once every ALERT_EVERY_MS. Problems found while an email is
// being held back are kept in a script property and go out with the next one, so each is reported.
function alertOnce_(problems, force) {
  const props = PropertiesService.getScriptProperties();
  let queue = [];
  try { queue = JSON.parse(props.getProperty('pendingProblems') || '[]'); } catch (e) { queue = []; }
  (problems || []).forEach(function (p) {
    const text = String(p).slice(0, 160);
    if (queue.indexOf(text) === -1) queue.push(text);
  });
  queue = queue.slice(-30);
  if (!queue.length) return;
  const last = Number(props.getProperty('lastAlertAt') || 0);
  if (!force && Date.now() - last < CONFIG.ALERT_EVERY_MS) {
    saveQueue_(props, queue);
    return;
  }
  try {
    MailApp.sendEmail(CONFIG.ALERT_TO, 'Weekly report needs attention (' + queue.length + ')', queue.join('\n'));
    props.setProperty('lastAlertAt', String(Date.now()));
    props.deleteProperty('pendingProblems');
  } catch (err) {
    saveQueue_(props, queue); // keep them for the next try
  }
}

// Run once by hand. Safe to run again: it replaces the old trigger.
function installTrigger() {
  ScriptApp.getProjectTriggers()
    .filter(function (t) { return t.getHandlerFunction() === 'sendWeeklyReport'; })
    .forEach(function (t) { ScriptApp.deleteTrigger(t); });
  // Hourly. sendWeeklyReport does nothing except on the send day, from the send hour, until the report has gone.
  ScriptApp.newTrigger('sendWeeklyReport').timeBased().everyHours(1).create();
}