Every new contact in a Google Sheet

// Every new contact in a Google Sheet.
//
// Each new contact your Kard's webhook sends becomes a row of the sheet this
// script belongs to, once, and only after its signature proves it came from
// your Kard. Set it up with the University course "Every new contact in a
// Google Sheet":
//   1. In the sheet: Extensions > Apps Script. Paste this over what's there.
//   2. Deploy > New deployment > Web app. Execute as: Me. Who has access:
//      Anyone. Copy the web app URL.
//   3. Paste that URL into your Kard's webhook page and press Start sending.
//   4. Copy the signing secret it shows (whsec_...). Here, in Project
//      Settings > Script properties, add the property SIGNING_SECRET with
//      the secret as its value.
//   5. Press Send test on the webhook page: a "Sample contact" row appears.
//
// Rows go to the tab named "Contacts". Move columns or add your own: each
// is found by its heading. Answers to your Kard's own questions get a
// column each, headed by the question. A delivery that isn't added is noted
// on the "Not added" tab, with why.

// Google asks to see and change this spreadsheet only, not all your files.
/** @OnlyCurrentDoc */

const SHEET_NAME = 'Contacts';
const NOT_ADDED_SHEET_NAME = 'Not added';
const NOT_ADDED_ROWS_KEPT = 100;
// A delivery signed more than this long ago (or ahead) is refused, so a
// copy someone kept can't be sent again later.
const TOLERANCE_SECONDS = 5 * 60;

const COLUMNS = [
  ['Added', (event) => new Date(event.data.contact.created_at || event.timestamp)],
  ['Name', (event) => event.data.contact.name],
  ['Email', (event) => event.data.contact.email],
  ['Phone', (event) => event.data.contact.phone_number],
  ['Company', (event) => (event.data.contact.card_details || {}).company],
  ['Job title', (event) => (event.data.contact.card_details || {}).job_title],
  ['Message', (event) => event.data.contact.message],
  ['Note', (event) => event.data.contact.note],
  ['How they came', (event) => event.data.source_label],
  ['How they found the Kard', (event) => event.data.found_via_label],
  ['Event', (event) => (event.data.contact.event || {}).name],
  ['Kard', (event) => event.data.kard.name],
  ['Test', (event) => (event.test ? 'Yes' : '')],
  // The same for every delivery of one contact: a retry is never a second row.
  ['Webhook id', (event) => event.id],
];

function doPost(e) {
  const lock = LockService.getScriptLock();
  lock.waitLock(30000);
  try {
    const refusal = signatureRefusal(e);
    if (refusal) {
      noteNotAdded(refusal);
      return answer('Not added: ' + refusal);
    }

    const event = JSON.parse(e.postData.contents);
    if (event.type !== 'contact.created') return answer('Not a new contact: ' + event.type);

    return answer(addRow(event) ? 'Added' : 'Already added');
  } finally {
    lock.releaseLock();
  }
}

// Opening the web app's address in a browser.
function doGet() {
  return answer('This address adds the new contacts your Kard sends to its Google Sheet. Paste it into the Kard\'s webhook page.');
}

// Why the delivery can't be trusted, or null when its signature checks out
// (Standard Webhooks: an HMAC-SHA256 of "id.timestamp.body", keyed with the
// secret's base64 part). Apps Script can't read headers, so the webhook
// sends them in the address's query too.
function signatureRefusal(e) {
  const secret = PropertiesService.getScriptProperties().getProperty('SIGNING_SECRET');
  if (!secret) return 'the script property SIGNING_SECRET isn\'t set. Add your webhook\'s signing secret (whsec_...) in Project Settings > Script properties.';

  const query = e.parameter || {};
  const id = query['webhook-id'];
  const timestamp = query['webhook-timestamp'];
  const signatures = query['webhook-signature'];
  if (!id || !timestamp || !signatures || !e.postData) return 'it carried no signature, so it didn\'t come from your Kard\'s webhook.';

  const drift = Math.abs(Date.now() / 1000 - Number(timestamp));
  if (!(drift <= TOLERANCE_SECONDS)) return 'it was signed more than 5 minutes from now, so it may be an old copy sent again.';

  const key = Utilities.base64Decode(secret.trim().replace(/^whsec_/, ''));
  const signed = Utilities.newBlob(id + '.' + timestamp + '.' + e.postData.contents).getBytes();
  const expected = 'v1,' + Utilities.base64Encode(Utilities.computeHmacSha256Signature(signed, key));
  const matches = String(signatures).split(' ').some((candidate) => sameText(candidate, expected));

  return matches ? null : 'its signature doesn\'t match SIGNING_SECRET. Check the property holds this webhook\'s current secret (a new secret replaces the old one).';
}

// Compares every character, so the time taken says nothing about the secret.
function sameText(a, b) {
  if (a.length !== b.length) return false;
  let difference = 0;
  for (let i = 0; i < a.length; i++) difference |= a.charCodeAt(i) ^ b.charCodeAt(i);
  return difference === 0;
}

// Adds the contact as a row, or does nothing when its id is there already.
function addRow(event) {
  const sheet = sheetNamed(SHEET_NAME);
  const values = {};
  COLUMNS.forEach(([heading, read]) => { values[heading] = read(event); });
  (event.data.answers || []).forEach((answer) => { values[answer.question] = answer.answer; });

  const headings = ensureHeadings(sheet, Object.keys(values));
  const idColumn = headings.indexOf('Webhook id') + 1;
  if (sheet.getLastRow() > 1) {
    const ids = sheet.getRange(2, idColumn, sheet.getLastRow() - 1, 1).getValues();
    if (ids.some((row) => String(row[0]) === String(event.id))) return false;
  }

  sheet.appendRow(headings.map((heading) => cell(values[heading])));
  return true;
}

// The headings in row 1, after adding any of `wanted` that's missing.
function ensureHeadings(sheet, wanted) {
  const width = sheet.getLastColumn();
  const headings = width > 0 ? sheet.getRange(1, 1, 1, width).getValues()[0].map(String) : [];
  const missing = wanted.filter((heading) => headings.indexOf(heading) === -1);
  if (missing.length > 0) {
    sheet.getRange(1, headings.length + 1, 1, missing.length).setValues([missing.map(cell)]);
    if (headings.length === 0) sheet.setFrozenRows(1);
  }
  return headings.concat(missing);
}

// A cell's value. Text is written as text: a leading ' keeps a phone
// number's + and zeros, and keeps anything a visitor typed that starts
// with = from running as a formula.
function cell(value) {
  if (value === null || value === undefined || value === '') return '';
  if (value instanceof Date) return value;
  return '\'' + String(value);
}

function noteNotAdded(reason) {
  const sheet = sheetNamed(NOT_ADDED_SHEET_NAME);
  if (sheet.getLastRow() === 0) sheet.appendRow(['When', 'Why it wasn\'t added']);
  sheet.appendRow([new Date(), cell(reason)]);
  const extra = sheet.getLastRow() - 1 - NOT_ADDED_ROWS_KEPT;
  if (extra > 0) sheet.deleteRows(2, extra);
}

function sheetNamed(name) {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  return spreadsheet.getSheetByName(name) || spreadsheet.insertSheet(name);
}

// What the webhook gets back. Apps Script answers HtmlService output
// directly; text from ContentService comes back as a redirect, which the
// webhook counts as a failed delivery.
function answer(text) {
  return HtmlService.createHtmlOutput(text);
}