Verify email addresses in Google Sheets with Spaw

A column of addresses becomes a column of verdicts without leaving the sheet. The script below adds two custom functions: SPAW_VERIFY checks one cell or a whole range against the Spaw API and returns any of the 27 fields of an email verification, and SPAW_DOMAIN inspects a domain's mail setup. It is plain Apps Script with no dependencies, it asks for no authorization because it only defines custom functions, and no message is ever sent to the addresses it checks.

Install

1. Open the spreadsheet and choose Extensions, then Apps Script.
2. Replace the contents of Code.gs with the script at the bottom of this page and save the project.
3. Create a secret API key on the API keys page of your dashboard (https://spaw.co/api-keys). Paste it into the SPAW_API_KEY constant at the top of the script, or store it as the script property SPAW_API_KEY under Project Settings. The property wins when both are set.
4. Back in the sheet, type =SPAW_VERIFY("[email protected]"). It answers deliverable and costs nothing, because the six spaw.test addresses are free test fixtures.

Anyone with edit access to the spreadsheet can open the script and read the key. Create a key just for this sheet, and revoke it on the API keys page when you want to rotate it. Publishable pk_ keys are browser keys and do not work from Apps Script.

Formulas

The second argument accepts any field of the email result or the domain result, and a comma-separated list returns the fields side by side. Blank cells and cells that hold numbers or dates answer an empty cell and never reach the API. Field names are checked before any request, so a typo in the second argument shows an error instead of spending a credit. SPAW_DOMAIN also accepts an email address and uses its domain.

What to do with the verdict

Filter on the first column: deliverable addresses go out, undeliverable addresses are dropped, and risky addresses deserve a look at reason and risk_score before you decide. A catch_all domain accepts mail for every address, so the mailbox could not be confirmed; role marks a shared inbox such as info@; disposable marks a burner. The did_you_mean field catches gmail.con and its relatives, so add a column for it and fix the typos before sending. The list cleaning checklist walks through the whole pass, and every reason has its own page under verdict reasons.

Cost and limits

Sheets recalculates custom functions more often than you might expect, so the script keeps every answer in the Apps Script cache for six hours, keyed by the lowercased address, and serves repeats from there. Beyond that, the API answers a repeat of a charged lookup free for seven days. A fresh deliverable or risky verdict costs 1 credit; an undeliverable answer is free.

A range is sent 50 addresses per request. Each key may make 5 requests per second, and a column of single-cell formulas recalculates all at once, so prefer one range formula per column; the script retries once after a rate-limit answer. If the balance runs out part-way through a range, the API answers the rows it could pay for, the script caches them, and the cell shows an error saying so. Top up on the Billing page and recalculate: the cached rows are not fetched or charged again. A custom function has 30 seconds to run, which fits a few hundred addresses per formula; for lists of thousands, use the bulk endpoint instead.

The script

Copy all of it into Code.gs. The same file, with its tests, lives in the Spaw integrations repository.

/**
 * Spaw email verification for Google Sheets.
 *
 * Custom functions that verify email addresses and inspect domains with the
 * Spaw API (https://spaw.co) straight from a formula:
 *
 *   =SPAW_VERIFY(A2)                              the verdict for one address
 *   =SPAW_VERIFY(A2, "reason")                    one field of the answer
 *   =SPAW_VERIFY(A2:A500, "deliverable,reason")   a range, several columns
 *   =SPAW_VERIFY(A2, "deliverable", "refresh")    a fresh, billable answer
 *   =SPAW_DOMAIN("acme.com", "mx_provider")       domain-level signals
 *
 * Install
 *   1. In your spreadsheet open Extensions > Apps Script.
 *   2. Replace the contents of Code.gs with this file and save the project.
 *   3. Create a secret API key at https://spaw.co/api-keys and paste it into
 *      SPAW_API_KEY below, or store it as the script property SPAW_API_KEY
 *      (Project Settings > Script Properties). The script property wins when
 *      both are set.
 *   4. Back in the sheet, try =SPAW_VERIFY("[email protected]").
 *
 * Anyone with edit access to the spreadsheet can open the script and read the
 * key, so create a key just for this sheet and revoke it on the API keys page
 * to rotate it. Publishable pk_ keys are browser keys and do not work here.
 *
 * Every answered lookup (a deliverable or risky verdict) costs 1 credit;
 * undeliverable answers, repeats within seven days and the spaw.test test
 * addresses are free. A mailbox the handshake could not confirm answers
 * "risky" with reason "unverified". Answers are kept in the script cache for
 * six hours so a recalculation does not call the API again. A range is
 * verified 50 addresses per request, which is the fastest and cheapest way to
 * check a column.
 *
 * The optional third argument asks for freshness: "refresh" sets the earlier
 * answer aside and bills the lookup as fresh when answered, "max_age=86400"
 * does so only when the earlier answer is older than that many seconds, and
 * "timeout=10" caps the mailbox handshake at that many seconds. Combine them
 * with commas: "max_age=3600,timeout=10".
 */

const SPAW_API_KEY = 'sk_live_REPLACE_ME';

const SPAW_API_BASE = 'https://spaw.co/api/v1';
const SPAW_BATCH_SIZE = 50;
const SPAW_CACHE_SECONDS = 21600;
const SPAW_CACHE_PREFIX = 'spaw:v1:';
const SPAW_CACHE_KEY_LIMIT = 250;

const SPAW_EMAIL_FIELDS = [
  'email', 'normalized_email', 'is_alias', 'is_gibberish', 'deliverable', 'reason',
  'risk_score', 'risk_level', 'syntax_valid', 'domain', 'mx_found', 'mx_implicit',
  'mx_provider', 'has_spf', 'dmarc_policy', 'domain_registered_at', 'domain_age_days',
  'disposable', 'role', 'free_provider', 'smtp_checked', 'smtp_checked_at',
  'mailbox_exists', 'catch_all', 'smtp_reason', 'mailbox_confidence', 'did_you_mean',
  'sources',
];

const SPAW_DOMAIN_FIELDS = [
  'domain', 'valid', 'mx_found', 'mx_implicit', 'catch_all', 'mx_provider', 'has_spf',
  'dmarc_policy', 'domain_registered_at', 'domain_age_days', 'disposable', 'free_provider',
  'sources',
];

/**
 * Verifies one email address, or every address in a range, with Spaw.
 *
 * @param {string|string[][]} value A cell holding an email address, or a one-column range of them.
 * @param {string} [fields] The field to return, "deliverable" by default. A comma-separated list such as "deliverable,reason,risk_score" returns one column per field.
 * @param {string} [options] Freshness controls: "refresh", "max_age=SECONDS" and/or "timeout=SECONDS", comma-separated.
 * @return {string|boolean|number|Array} The requested field, or one row per input row.
 * @customfunction
 */
function SPAW_VERIFY(value, fields, options) {
  const names = spawFieldNames_(fields, 'deliverable', SPAW_EMAIL_FIELDS);
  const freshness = spawOptions_(options);

  if (!Array.isArray(value)) {
    const address = spawCleanInput_(value);
    const values = spawValues_(address === '' ? null : spawVerifyOne_(address, freshness), names);

    return names.length === 1 ? values[0] : [values];
  }

  const addresses = value.map(function (row) {
    return spawCleanInput_(Array.isArray(row) ? row[0] : row);
  });
  const results = spawVerifyMany_(addresses, freshness);

  return addresses.map(function (address, index) {
    return spawValues_(address === '' ? null : results[index], names);
  });
}

/**
 * Inspects a domain's mail setup with Spaw: MX records, provider, SPF and
 * DMARC, registration age, disposable and free-provider flags. An email
 * address works too; its domain is used.
 *
 * @param {string|string[][]} domain A cell holding a domain (or an address), or a one-column range of them.
 * @param {string} [fields] The field to return, "mx_found" by default. A comma-separated list such as "mx_provider,has_spf,dmarc_policy" returns one column per field.
 * @return {string|boolean|number|Array} The requested field, or one row per input row.
 * @customfunction
 */
function SPAW_DOMAIN(domain, fields) {
  const names = spawFieldNames_(fields, 'mx_found', SPAW_DOMAIN_FIELDS);

  if (!Array.isArray(domain)) {
    const name = spawCleanDomain_(domain);
    const values = spawValues_(name === '' ? null : spawDomainOne_(name), names);

    return names.length === 1 ? values[0] : [values];
  }

  return domain.map(function (row) {
    const name = spawCleanDomain_(Array.isArray(row) ? row[0] : row);

    return spawValues_(name === '' ? null : spawDomainOne_(name), names);
  });
}

function spawVerifyOne_(address, freshness) {
  const cache = spawCache_();
  const key = spawCacheKey_('email', address.toLowerCase());
  const cached = freshness.bypassCache ? null : spawCacheGet_(cache, key);

  if (cached !== null) {
    return cached;
  }

  const body = spawRequest_('POST', '/email', spawMerge_({ email: address }, freshness.body));
  const data = body.data || null;

  spawCachePutAll_(cache, key === null ? {} : spawCacheEntry_(key, data));

  return data;
}

function spawVerifyMany_(addresses, freshness) {
  const results = addresses.map(function () {
    return null;
  });
  const cache = spawCache_();
  const positions = {};
  const normalized = [];

  addresses.forEach(function (address, index) {
    if (address === '') {
      return;
    }

    const norm = address.toLowerCase();

    if (!positions[norm]) {
      positions[norm] = [];
      normalized.push(norm);
    }

    positions[norm].push(index);
  });

  const keys = normalized.map(function (norm) {
    return spawCacheKey_('email', norm);
  });
  const cached = freshness.bypassCache ? {} : spawCacheGetAll_(cache, keys);
  const missing = [];

  normalized.forEach(function (norm, position) {
    const key = keys[position];

    if (key !== null && cached[key]) {
      spawAssign_(results, positions[norm], JSON.parse(cached[key]));
    } else {
      missing.push(norm);
    }
  });

  for (let start = 0; start < missing.length; start += SPAW_BATCH_SIZE) {
    const chunk = missing.slice(start, start + SPAW_BATCH_SIZE);
    const emails = chunk.map(function (norm) {
      return addresses[positions[norm][0]];
    });
    const body = spawRequest_('POST', '/email/batch', spawMerge_({ emails: emails }, freshness.body));
    const items = (body.data && body.data.results) || [];
    const entries = {};

    items.forEach(function (item, position) {
      const norm = chunk[position];
      const data = (item && item.data) || null;
      const key = spawCacheKey_('email', norm);

      spawAssign_(results, positions[norm], data);

      if (key !== null && data !== null) {
        entries[key] = JSON.stringify(data);
      }
    });

    spawCachePutAll_(cache, entries);

    if (items.length < chunk.length) {
      const reason = (body.meta && body.meta.stopped_reason) || 'incomplete_batch';
      const detail = reason === 'insufficient_credits'
        ? ' because your credit balance ran out; the verified rows are cached, so top up on the Billing page and recalculate'
        : '';

      throw new Error('Spaw: the batch stopped after ' + items.length + ' of ' + chunk.length + ' addresses' + detail + ' (' + reason.toUpperCase() + ')');
    }
  }

  return results;
}

function spawDomainOne_(name) {
  const cache = spawCache_();
  const key = spawCacheKey_('domain', name);
  const cached = spawCacheGet_(cache, key);

  if (cached !== null) {
    return cached;
  }

  const body = spawRequest_('GET', '/email/domain/' + encodeURIComponent(name));
  const data = body.data || null;

  spawCachePutAll_(cache, key === null ? {} : spawCacheEntry_(key, data));

  return data;
}

function spawRequest_(method, path, payload) {
  const options = {
    method: method,
    contentType: 'application/json',
    headers: {
      Authorization: 'Bearer ' + spawApiKey_(),
      Accept: 'application/json',
    },
    muteHttpExceptions: true,
  };

  if (payload !== undefined) {
    options.payload = JSON.stringify(payload);
  }

  let response = UrlFetchApp.fetch(SPAW_API_BASE + path, options);

  if (response.getResponseCode() === 429) {
    Utilities.sleep(spawRetryAfterMs_(response));
    response = UrlFetchApp.fetch(SPAW_API_BASE + path, options);
  }

  return spawParse_(response);
}

function spawParse_(response) {
  const status = response.getResponseCode();
  let body = null;

  try {
    body = JSON.parse(response.getContentText());
  } catch (error) {
    body = null;
  }

  if (body === null || typeof body !== 'object') {
    throw new Error('Spaw: unexpected answer from the API (HTTP_' + status + ')');
  }

  if (body.success === false || status >= 400) {
    const failure = body.error || {};
    const code = failure.code || 'HTTP_' + status;

    throw new Error('Spaw: ' + spawErrorMessage_(code, failure.message) + ' (' + code + ')');
  }

  return body;
}

function spawErrorMessage_(code, message) {
  switch (code) {
    case 'INSUFFICIENT_CREDITS':
      return 'your credit balance is empty; buy a credit pack on the Billing page of your Spaw dashboard';
    case 'RATE_LIMITED':
      return 'too many requests at once; verify the column as one range, or recalculate in a moment (answered rows are cached)';
    case 'UNAUTHENTICATED':
      return 'the API key was rejected; paste a secret key from https://spaw.co/api-keys';
    default:
      return message || 'the request failed';
  }
}

function spawRetryAfterMs_(response) {
  let headers = {};

  try {
    headers = response.getHeaders() || {};
  } catch (error) {
    headers = {};
  }

  const seconds = parseInt(headers['Retry-After'] || headers['retry-after'] || '1', 10);

  if (isNaN(seconds) || seconds < 1) {
    return 1000;
  }

  return Math.min(seconds, 3) * 1000;
}

function spawApiKey_() {
  let key = '';

  try {
    const stored = PropertiesService.getScriptProperties().getProperty('SPAW_API_KEY');

    if (stored) {
      key = String(stored).trim();
    }
  } catch (error) {
    key = '';
  }

  if (key === '') {
    key = String(SPAW_API_KEY || '').trim();
  }

  if (key.indexOf('pk_') === 0) {
    throw new Error('Spaw: publishable pk_ keys only work in the browser; paste a secret sk_live_ key from https://spaw.co/api-keys (NO_API_KEY)');
  }

  if (key === '' || key === 'sk_live_REPLACE_ME' || key.indexOf('sk_') !== 0) {
    throw new Error('Spaw: set your secret API key in SPAW_API_KEY or in the SPAW_API_KEY script property; keys come from https://spaw.co/api-keys (NO_API_KEY)');
  }

  return key;
}

/**
 * Parses the freshness options: "refresh", "max_age=SECONDS" and
 * "timeout=SECONDS", comma-separated. refresh and max_age also skip the
 * script cache, because the point is a newer answer than the one on hand.
 */
function spawOptions_(options) {
  const parsed = { body: {}, bypassCache: false };
  const raw = options === undefined || options === null ? '' : String(options).trim();

  if (raw === '') {
    return parsed;
  }

  raw.split(',').forEach(function (token) {
    const cleaned = token.trim().toLowerCase();

    if (cleaned === '') {
      return;
    }

    const separator = cleaned.search(/[=:]/);
    const name = separator === -1 ? cleaned : cleaned.slice(0, separator).trim();
    const value = separator === -1 ? '' : cleaned.slice(separator + 1).trim();

    if (name === 'refresh') {
      if (value === '' || value === 'true' || value === 'yes' || value === '1') {
        parsed.body.refresh = true;
        parsed.bypassCache = true;
      }

      return;
    }

    if (name === 'max_age' || name === 'timeout') {
      const seconds = parseInt(value, 10);

      if (isNaN(seconds) || seconds <= 0) {
        throw new Error('Spaw: "' + name + '" needs a number of seconds, for example "' + name + '=' + (name === 'max_age' ? '86400' : '10') + '" (UNKNOWN_OPTION)');
      }

      parsed.body[name] = seconds;

      if (name === 'max_age') {
        parsed.bypassCache = true;
      }

      return;
    }

    throw new Error('Spaw: unknown option "' + token.trim() + '"; use refresh, max_age=SECONDS or timeout=SECONDS (UNKNOWN_OPTION)');
  });

  return parsed;
}

function spawMerge_(target, extra) {
  Object.keys(extra).forEach(function (name) {
    target[name] = extra[name];
  });

  return target;
}

function spawFieldNames_(fields, fallback, known) {
  const raw = fields === undefined || fields === null || String(fields).trim() === ''
    ? fallback
    : String(fields);
  const names = raw.split(',').map(function (name) {
    return name.trim().toLowerCase();
  }).filter(function (name) {
    return name !== '';
  });

  names.forEach(function (name) {
    if (known.indexOf(name) === -1) {
      throw new Error('Spaw: unknown field "' + name + '"; use one of ' + known.join(', ') + ' (UNKNOWN_FIELD)');
    }
  });

  return names.length === 0 ? [fallback] : names;
}

function spawCleanInput_(value) {
  return typeof value === 'string' ? value.trim() : '';
}

function spawCleanDomain_(value) {
  const cleaned = spawCleanInput_(value);
  const at = cleaned.lastIndexOf('@');

  return (at === -1 ? cleaned : cleaned.slice(at + 1)).toLowerCase();
}

function spawValues_(data, names) {
  return names.map(function (name) {
    return spawScalar_(data === null || data === undefined ? null : data[name]);
  });
}

function spawScalar_(value) {
  if (value === null || value === undefined) {
    return '';
  }

  if (typeof value === 'object') {
    return JSON.stringify(value);
  }

  return value;
}

function spawAssign_(results, indexes, data) {
  indexes.forEach(function (index) {
    results[index] = data;
  });
}

function spawCache_() {
  try {
    return CacheService.getScriptCache();
  } catch (error) {
    return null;
  }
}

function spawCacheKey_(kind, normalized) {
  const key = SPAW_CACHE_PREFIX + kind + ':' + normalized;

  return key.length <= SPAW_CACHE_KEY_LIMIT ? key : null;
}

function spawCacheEntry_(key, data) {
  const entry = {};

  if (data !== null) {
    entry[key] = JSON.stringify(data);
  }

  return entry;
}

function spawCacheGet_(cache, key) {
  if (cache === null || key === null) {
    return null;
  }

  try {
    const hit = cache.get(key);

    return hit ? JSON.parse(hit) : null;
  } catch (error) {
    return null;
  }
}

function spawCacheGetAll_(cache, keys) {
  const wanted = keys.filter(function (key) {
    return key !== null;
  });

  if (cache === null || wanted.length === 0) {
    return {};
  }

  try {
    return cache.getAll(wanted) || {};
  } catch (error) {
    return {};
  }
}

function spawCachePutAll_(cache, entries) {
  if (cache === null || Object.keys(entries).length === 0) {
    return;
  }

  try {
    cache.putAll(entries, SPAW_CACHE_SECONDS);
  } catch (error) {
    // The cache is a convenience: a failed write only costs a repeat call.
  }
}