Sync a Google Form to Sheets with validation and alerts

// Checks Google Form responses as they arrive in a Sheet and emails a digest of the ones that need a person.
// Paste into Code.gs in a new project at script.google.com, edit CONFIG, run installTriggers() once.

const CONFIG = {
  SPREADSHEET_ID: 'PASTE_THE_RESPONSES_SPREADSHEET_ID_HERE',
  SHEET_NAME: 'Form responses 1',
  CHECK_HEADER: 'Check',        // columns the script adds to the right of the form's columns
  ALERTED_HEADER: 'Alerted',
  // column must match a header in the sheet exactly (it is the form question text).
  // types: email, number (min, max), pattern (a regular expression), oneOf (values). Leave type out for text.
  RULES: [
    { column: 'Name', required: true },
    { column: 'Email', required: true, type: 'email' },
    { column: 'Phone', type: 'pattern', pattern: '^\\+?[0-9 ()-]{8,16}$', hint: 'does not look like a phone number' },
    { column: 'Quantity', type: 'number', min: 1, max: 500 },
    { column: 'Site', required: true, type: 'oneOf', values: ['North', 'South', 'East'] },
  ],
  URGENT_COLUMN: 'Details',     // a response whose text contains any of these words is marked Urgent
  URGENT_WORDS: ['urgent', 'asap', 'emergency'],
  MAX_ROWS_PER_RUN: 300,
  MAX_ROWS_IN_DIGEST: 50,
  // 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 EMAIL_PATTERN = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;

function checkResponses() {
  const lock = LockService.getScriptLock();
  // If two submissions land together the second run may find the lock busy and leave.
  // The hourly run installed below picks up whatever it skipped.
  if (!lock.tryLock(30 * 1000)) return;
  const started = Date.now();
  const problems = [];
  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 lastRow = sheet.getLastRow();
    if (lastRow < 2) return;

    // One read of the whole response tab, then everything happens in memory.
    const grid = sheet.getRange(1, 1, lastRow, sheet.getLastColumn()).getDisplayValues();
    const headers = grid[0].slice();
    const added = [];
    let checkCol = headers.indexOf(CONFIG.CHECK_HEADER);
    if (checkCol < 0) { checkCol = headers.length; headers.push(CONFIG.CHECK_HEADER); added.push(CONFIG.CHECK_HEADER); }
    let alertedCol = headers.indexOf(CONFIG.ALERTED_HEADER);
    if (alertedCol < 0) { alertedCol = headers.length; headers.push(CONFIG.ALERTED_HEADER); added.push(CONFIG.ALERTED_HEADER); }
    if (added.length) sheet.getRange(1, headers.length - added.length + 1, 1, added.length).setValues([added]);

    const rules = prepareRules_(headers, problems);
    const urgentCol = headers.indexOf(CONFIG.URGENT_COLUMN);
    if (urgentCol < 0) problems.push('Urgent column not found in the sheet: ' + CONFIG.URGENT_COLUMN);

    const rows = grid.slice(1).map(function (r) {
      const row = r.slice();
      while (row.length < headers.length) row.push('');
      return row;
    });

    // Pass 1: check every response that has no result yet.
    let checked = 0;
    let changed = false;
    for (let i = 0; i < rows.length; i++) {
      const row = rows[i];
      if (row[checkCol] !== '') continue;
      if (checked >= CONFIG.MAX_ROWS_PER_RUN || Date.now() - started > CONFIG.TIME_BUDGET_MS) break; // the next run carries on
      row[checkCol] = verdict_(row, rules, urgentCol);
      checked++;
      changed = true;
    }
    if (changed) writeColumn_(sheet, checkCol, rows);

    // Pass 2: one digest for everything flagged and not yet reported.
    const pending = [];
    rows.forEach(function (row, i) {
      if (row[checkCol] !== '' && row[checkCol] !== 'OK' && row[alertedCol] === '') pending.push(i);
    });
    if (pending.length) {
      sendDigest_(ss, sheet, rows, pending, checkCol);
      const stamp = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyy-MM-dd HH:mm');
      pending.forEach(function (i) { rows[i][alertedCol] = stamp; });
      writeColumn_(sheet, alertedCol, rows); // only after the email went out
    }
  } catch (err) {
    problems.push('Run failed: ' + err.message);
  } finally {
    lock.releaseLock();
  }
  alertOnce_(problems);
}

function prepareRules_(headers, problems) {
  const usable = [];
  CONFIG.RULES.forEach(function (rule) {
    const col = headers.indexOf(rule.column);
    if (col < 0) { problems.push('A rule refers to a column that is not in the sheet: ' + rule.column); return; }
    usable.push({ rule: rule, col: col, regex: rule.type === 'pattern' ? new RegExp(rule.pattern) : null });
  });
  return usable;
}

function verdict_(row, rules, urgentCol) {
  const issues = [];
  rules.forEach(function (r) {
    const value = String(row[r.col]).trim();
    const label = r.rule.column;
    if (value === '') {
      if (r.rule.required) issues.push(label + ' is empty');
      return;
    }
    if (r.rule.type === 'email' && !EMAIL_PATTERN.test(value)) issues.push(label + ' is not a valid email');
    if (r.rule.type === 'pattern' && !r.regex.test(value)) issues.push(label + ' ' + (r.rule.hint || 'is in the wrong format'));
    if (r.rule.type === 'number') {
      const n = Number(value.replace(/,/g, ''));
      if (!isFinite(n)) issues.push(label + ' is not a number');
      else if ((r.rule.min !== undefined && n < r.rule.min) || (r.rule.max !== undefined && n > r.rule.max)) {
        issues.push(label + ' is outside ' + r.rule.min + ' to ' + r.rule.max);
      }
    }
    if (r.rule.type === 'oneOf') {
      const ok = r.rule.values.some(function (v) { return v.toLowerCase() === value.toLowerCase(); });
      if (!ok) issues.push(label + ' is not one of the expected values');
    }
  });
  let urgent = false;
  if (urgentCol >= 0) {
    const text = String(row[urgentCol]).toLowerCase();
    urgent = CONFIG.URGENT_WORDS.some(function (w) { return text.indexOf(w.toLowerCase()) !== -1; });
  }
  if (urgent) issues.unshift('Urgent');
  if (!issues.length) return 'OK';
  return urgent ? issues.join('; ') : 'Problem: ' + issues.join('; ');
}

// Writes one script-owned column back in a single call.
function writeColumn_(sheet, col, rows) {
  sheet.getRange(2, col + 1, rows.length, 1).setValues(rows.map(function (r) { return [safe_(r[col])]; }));
}

function sendDigest_(ss, sheet, rows, pending, checkCol) {
  const shown = pending.slice(0, CONFIG.MAX_ROWS_IN_DIGEST);
  const base = ss.getUrl() + '#gid=' + sheet.getSheetId();
  const items = shown.map(function (i) {
    const number = i + 2; // row 1 is the header
    return '<li>Row <a href="' + esc_(base + '&range=A' + number) + '">' + number + '</a>: ' +
      esc_(rows[i][0]) + ' - ' + esc_(rows[i][checkCol]) + '</li>';
  }).join('');
  const more = pending.length > shown.length
    ? '<p>And ' + (pending.length - shown.length) + ' more in the sheet.</p>'
    : '';
  MailApp.sendEmail({
    to: CONFIG.ALERT_TO,
    subject: 'Form responses to look at (' + pending.length + ')',
    body: pending.length + ' form responses need a look. Open the sheet and filter the Check column: ' + ss.getUrl(),
    htmlBody: '<p>' + pending.length + ' form responses need a look.</p><ul>' + items + '</ul>' + more,
  });
}

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

// Stops text typed by a stranger, like "=IMPORTXML(...)", 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, 'Form checks need 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 triggers.
function installTriggers() {
  ScriptApp.getProjectTriggers()
    .filter(function (t) { return t.getHandlerFunction() === 'checkResponses'; })
    .forEach(function (t) { ScriptApp.deleteTrigger(t); });
  ScriptApp.newTrigger('checkResponses').forSpreadsheet(CONFIG.SPREADSHEET_ID).onFormSubmit().create();
  ScriptApp.newTrigger('checkResponses').timeBased().everyHours(1).create(); // safety net
}