Generate Google Docs from a Sheet row

// Makes a Google Doc from a template for every Sheet row marked Ready, and writes the link back to the row.
// Paste into Code.gs in a new project at script.google.com, edit CONFIG, run installTrigger() once.
// In the template, write {{Heading}} wherever a value should go, using the Sheet's column headings.

const CONFIG = {
  SPREADSHEET_ID: 'PASTE_THE_SPREADSHEET_ID_HERE',
  SHEET_NAME: 'Documents',
  TEMPLATE_DOC_ID: 'PASTE_THE_TEMPLATE_DOC_ID_HERE',
  OUTPUT_FOLDER_ID: 'PASTE_THE_DRIVE_FOLDER_ID_HERE',
  REF_HEADER: 'Ref',               // a value that is different on every row, such as a proposal number
  NAME_HEADER: 'Client',           // used with the Ref in the document's file name
  OWNER_HEADER: 'Owner email',     // optional: this person is given edit access to their document
  STATUS_HEADER: 'Status',         // type Ready here to ask for a document
  MAX_PER_RUN: 20,
  // 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,
  ALERT_TO: 'ops@example.com',
  ALERT_EVERY_MS: 6 * 60 * 60 * 1000,
};

const SCRIPT_HEADERS = ['Doc link', 'Generated on', 'Message']; // added to the right of your columns
const EMAIL_PATTERN = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;

function generateDocs() {
  const lock = LockService.getScriptLock();
  if (!lock.tryLock(30 * 1000)) return;
  const started = Date.now();
  const problems = [];
  let sheet = null;
  let cols = null;
  let out = null;
  try {
    const ss = SpreadsheetApp.openById(CONFIG.SPREADSHEET_ID);
    sheet = ss.getSheetByName(CONFIG.SHEET_NAME);
    if (!sheet) throw new Error('There is no tab called ' + CONFIG.SHEET_NAME);
    // One read of the tab, as text exactly as it looks on screen.
    const grid = sheet.getDataRange().getDisplayValues();
    const headers = grid[0].slice();
    cols = columns_(sheet, headers);
    const rows = grid.slice(1);
    out = rows.map(function (r) {
      return { status: cell_(r, cols.status), link: cell_(r, cols.link), on: cell_(r, cols.on), message: cell_(r, cols.message) };
    });

    // Count each Ref once, so two rows can never share (and overwrite) one document.
    const refCount = {};
    rows.forEach(function (r) { const ref = cell_(r, cols.ref).trim(); if (ref) refCount[ref] = (refCount[ref] || 0) + 1; });

    const folder = DriveApp.getFolderById(CONFIG.OUTPUT_FOLDER_ID);
    const template = DriveApp.getFileById(CONFIG.TEMPLATE_DOC_ID);
    let made = 0;
    for (let i = 0; i < rows.length; i++) {
      const o = out[i];
      if (o.status.trim().toLowerCase() !== 'ready') continue;
      if (made >= CONFIG.MAX_PER_RUN || Date.now() - started > CONFIG.TIME_BUDGET_MS) break; // the next run carries on
      const ref = cell_(rows[i], cols.ref).trim();
      if (!ref || refCount[ref] > 1) {
        o.touched = true;
        o.status = 'Failed';
        o.message = ref ? 'Ref is used on more than one row' : 'Ref is empty';
        problems.push('Row ' + (i + 2) + ': ' + o.message);
        continue;
      }
      try {
        const base = ref + (cols.name >= 0 && rows[i][cols.name] ? ' - ' + rows[i][cols.name] : '');
        const notes = [];
        let name = base;
        let file;
        if (o.link === '') {
          // First document for this row. If an earlier run made it but stopped before saving the link, use that one.
          const existing = folder.getFilesByName(name);
          file = existing.hasNext() ? existing.next() : template.makeCopy(name, folder);
        } else {
          // The row already has a document and has been set to Ready again: a fresh copy of the template,
          // named "... (v2)", "(v3)" and so on. The earlier documents are never changed or deleted.
          let n = 2;
          while (folder.getFilesByName(base + ' (v' + n + ')').hasNext()) n++;
          name = base + ' (v' + n + ')';
          file = template.makeCopy(name, folder);
          notes.push('New version made, the earlier document is unchanged');
        }
        const body = DocumentApp.openById(file.getId()).getBody();
        headers.forEach(function (h, c) {
          if (h && SCRIPT_HEADERS.indexOf(h) === -1) fill_(body, '{{' + h + '}}', rows[i][c]);
        });
        const left = body.getText().match(/\{\{[^{}]+\}\}/g);
        if (left) notes.push('Not filled: ' + left.filter(function (t, k) { return left.indexOf(t) === k; }).join(' '));
        const owner = cols.owner >= 0 ? String(rows[i][cols.owner]).trim() : '';
        const trouble = [];
        if (owner) {
          if (!EMAIL_PATTERN.test(owner)) {
            trouble.push('Owner email not valid, document not shared');
          } else {
            try {
              file.addEditor(owner);
            } catch (err) {
              trouble.push('Could not share with ' + owner + ': ' + err.message); // the document is still made and linked
            }
          }
        }
        o.touched = true;
        o.status = left ? 'Generated with gaps' : 'Generated';
        o.link = file.getUrl();
        o.on = Utilities.formatDate(new Date(), ss.getSpreadsheetTimeZone(), 'yyyy-MM-dd HH:mm');
        o.message = notes.concat(trouble).join('. ');
        if (left || trouble.length) problems.push('Row ' + (i + 2) + ': ' + notes.concat(trouble).join('. '));
        made++;
      } catch (err) {
        o.touched = true;
        o.status = 'Failed';
        o.message = err.message;
        problems.push('Row ' + (i + 2) + ': ' + err.message);
      }
    }
  } catch (err) {
    problems.push('Run failed: ' + err.message);
  } finally {
    // Record what was done even if the loop stopped early, so nothing is repeated.
    if (sheet && cols && out && out.length) {
      try {
        writeBack_(sheet, cols, out);
      } catch (err) {
        // Report it and carry on to the alert. Documents made this run are named by Ref, so a first-time
        // document is reused on the next run; a regenerated one would get another version number.
        problems.push('Could not save results to the sheet: ' + err.message);
      }
    }
    lock.releaseLock();
  }
  alertOnce_(problems);
}

// Replaces every copy of a placeholder with the value, without treating the value as a pattern.
function fill_(body, token, value) {
  const pattern = token.replace(/[.*+?^${}()|[\]\\]/g, '\\$&');
  const text = String(value).replace(/\{\{/g, '{ {'); // a value must never contain a placeholder of its own
  for (let guard = 0; guard < 200; guard++) {
    const found = body.findText(pattern);
    if (!found) return;
    const element = found.getElement().asText();
    const start = found.isPartial() ? found.getStartOffset() : 0;
    const end = found.isPartial() ? found.getEndOffsetInclusive() : element.getText().length - 1;
    // Insert first, then remove the placeholder, so the paragraph is never left with no text.
    if (text) element.insertText(start, text);
    element.deleteText(start + text.length, end + text.length);
  }
}

// Finds your columns by heading, and adds the three the script owns if they are missing.
function columns_(sheet, headers) {
  const find = function (name) { return headers.indexOf(name); };
  const cols = { ref: find(CONFIG.REF_HEADER), name: find(CONFIG.NAME_HEADER), owner: find(CONFIG.OWNER_HEADER), status: find(CONFIG.STATUS_HEADER) };
  if (cols.ref < 0) throw new Error('The sheet has no column headed ' + CONFIG.REF_HEADER);
  if (cols.status < 0) throw new Error('The sheet has no column headed ' + CONFIG.STATUS_HEADER);
  const added = [];
  SCRIPT_HEADERS.forEach(function (name) {
    if (find(name) < 0) { headers.push(name); added.push(name); }
  });
  if (added.length) sheet.getRange(1, headers.length - added.length + 1, 1, added.length).setValues([added]);
  cols.link = find('Doc link');
  cols.on = find('Generated on');
  cols.message = find('Message');
  return cols;
}

function cell_(row, index) { return index >= 0 && index < row.length ? String(row[index]) : ''; }

function writeBack_(sheet, cols, out) {
  const n = out.length;
  const put = function (col, key) {
    sheet.getRange(2, col + 1, n, 1).setValues(out.map(function (o) { return [safe_(o[key])]; }));
  };
  // People type Ready into the Status column while the script runs, so read it again just
  // before writing and change only the rows this run dealt with.
  const fresh = sheet.getRange(2, cols.status + 1, n, 1).getDisplayValues();
  out.forEach(function (o, i) { if (!o.touched) o.status = fresh[i][0]; });
  put(cols.status, 'status');
  put(cols.link, 'link');
  put(cols.on, 'on');
  put(cols.message, 'message');
}

// Stops error text, which can include sheet content, running as a formula.
function safe_(value) {
  const s = String(value);
  return /^[=+@\t\r-]/.test(s) ? "'" + s : s;
}

// 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, 'Document generation 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() === 'generateDocs'; })
    .forEach(function (t) { ScriptApp.deleteTrigger(t); });
  ScriptApp.newTrigger('generateDocs').timeBased().everyMinutes(10).create();
}