Verify emails in Google Sheets

/**
 * SimpleVerifier for Google Sheets.
 *
 * Verifies a column of email addresses or phone numbers with the SimpleVerifier
 * API, using the customer's own API key, and writes the results into new
 * columns next to it. Also adds SV_VERIFY, SV_VERIFY_DETAIL and SV_PHONE for single cells.
 *
 * @OnlyCurrentDoc
 */

var API_BASE = 'https://simpleverifier.com/api/v1';
var SITE = 'https://simpleverifier.com';
var KEY_PROPERTY = 'SV_API_KEY';
var JOB_PREFIX = 'SV_JOB_';

/** 100 calls a minute, under the API's 120 so formulas running at the same time still fit. */
var MIN_MS_PER_CALL = 600;
var BATCH_SIZE = 10;
/** Each sidebar call stops starting new batches after this, far inside the 6-minute limit. */
var RUN_BUDGET_MS = 50 * 1000;
var RATE_LIMIT_PAUSE_MS = 15 * 1000;
var MAX_RETRIES = 4;
var CACHE_SECONDS = 6 * 60 * 60;

var HEADERS = {
  email: ['Status', 'Risk', 'Send advice', 'Reason'],
  phone: ['Status', 'E.164', 'Type', 'Carrier'],
};

var MESSAGES = {
  noKey: 'Add your SimpleVerifier API key in Extensions > SimpleVerifier > Open first. Create one at ' + SITE + '/api-keys',
  badKey: 'Your API key was not accepted. Check it at ' + SITE + '/api-keys and save it again.',
  inactive: 'Your SimpleVerifier subscription is not active. Renew it at ' + SITE + '/pricing, then press Resume.',
  noApiPlan: 'Your plan does not include API access, which this add-on needs. API access comes with Pro, Business or Agency. See ' + SITE + '/pricing',
  rateLimited: 'Too many checks at once: the limit is 120 a minute per key. Wait a minute and try again.',
  unavailable: 'SimpleVerifier is briefly unavailable. Wait a minute, then press Resume.',
};

/**
 * A 402 can mean three different things (plan without API, lapsed subscription,
 * yearly quota used). The API says which in its error text, so show that text
 * rather than guessing.
 */
function paymentMessage_(body, fallback) {
  var text = body && body.error ? String(body.error) : '';
  return text || fallback || MESSAGES.inactive;
}

/** Seconds to wait after a 429: the API's own answer when it gives one, else a safe default. */
function retryAfterMs_(response, body) {
  var header = Number(response && response.getHeaders ? (response.getHeaders()['Retry-After'] || response.getHeaders()['retry-after']) : 0);
  var fromBody = Number(body && body.retry_after_seconds);
  var seconds = header > 0 ? header : fromBody > 0 ? fromBody : RATE_LIMIT_PAUSE_MS / 1000;
  return Math.min(Math.max(seconds, 1), 60) * 1000;
}

/**
 * The only statuses the add-on will show are valid, risky and invalid. Anything
 * else, and any "valid" the API did not back with a mailbox check, is risky:
 * an unconfirmed address is never reported as valid.
 */
function normaliseEmailBody_(body) {
  var status = String(body.status || '').toLowerCase();
  var reason = body.reason || '';
  var advice = body.send_advice || '';
  if (status === 'valid' && body.mailbox_checked === false) {
    status = 'risky';
    reason = 'Mailbox could not be confirmed' + (reason ? ' (' + reason + ')' : '');
    advice = 'care';
  } else if (status !== 'valid' && status !== 'invalid') {
    status = 'risky';
    reason = reason || 'Result could not be confirmed';
    advice = advice || 'care';
  }
  if (!advice) advice = status === 'valid' ? 'safe' : status === 'invalid' ? 'do_not_send' : 'care';
  return { status: status, reason: reason, advice: advice };
}

function riskFor_(status, advice) {
  if (advice === 'do_not_send' || status === 'invalid') return 'high';
  if (advice === 'safe' && status === 'valid') return 'low';
  return 'medium';
}

function onInstall(e) {
  onOpen(e);
}

function onOpen() {
  SpreadsheetApp.getUi()
    .createAddonMenu()
    .addItem('Open', 'showSidebar')
    .addToUi();
}

function showSidebar() {
  var html = HtmlService.createHtmlOutputFromFile('Sidebar').setTitle('SimpleVerifier');
  SpreadsheetApp.getUi().showSidebar(html);
}

// ---------------------------------------------------------------------------
// API key
// ---------------------------------------------------------------------------

function getApiKey_() {
  return PropertiesService.getUserProperties().getProperty(KEY_PROPERTY) || '';
}

/** Checks a key without spending a verification: auth runs before the missing-email check. */
function checkKey_(key) {
  var response = UrlFetchApp.fetch(API_BASE + '/verify', {
    method: 'get',
    headers: { Authorization: 'Bearer ' + key },
    muteHttpExceptions: true,
  });
  var code = response.getResponseCode();
  if (code === 400 || code === 200) return;
  if (code === 401) throw new Error(MESSAGES.badKey);
  if (code === 402) throw new Error(paymentMessage_(parseBody_(response), MESSAGES.noApiPlan));
  if (code === 429) throw new Error(MESSAGES.rateLimited);
  throw new Error(MESSAGES.unavailable);
}

function saveApiKey(rawKey) {
  var key = String(rawKey || '').trim();
  if (!/^sv_live_[A-Za-z0-9_-]+$/.test(key)) {
    throw new Error('That does not look like a SimpleVerifier key. Keys start with sv_live_.');
  }
  checkKey_(key);
  PropertiesService.getUserProperties().setProperty(KEY_PROPERTY, key);
  return getState();
}

function removeApiKey() {
  PropertiesService.getUserProperties().deleteProperty(KEY_PROPERTY);
  return getState();
}

// ---------------------------------------------------------------------------
// Sidebar state
// ---------------------------------------------------------------------------

function columnLetter_(index) {
  var letters = '';
  var n = index;
  while (n > 0) {
    var rem = (n - 1) % 26;
    letters = String.fromCharCode(65 + rem) + letters;
    n = Math.floor((n - 1) / 26);
  }
  return letters;
}

function describeSelection_() {
  var range = SpreadsheetApp.getActiveRange();
  if (!range) return null;
  var firstRow = Math.max(range.getRow(), 2);
  var lastRow = range.getLastRow();
  return {
    sheet: range.getSheet().getName(),
    column: range.getColumn(),
    letter: columnLetter_(range.getColumn()),
    firstRow: firstRow,
    lastRow: lastRow,
    rows: Math.max(lastRow - firstRow + 1, 0),
    multipleColumns: range.getNumColumns() > 1,
  };
}

function listColumns_(sheet) {
  var lastColumn = Math.min(sheet.getLastColumn(), 200);
  if (lastColumn < 1) return [];
  var headers = sheet.getRange(1, 1, 1, lastColumn).getDisplayValues()[0];
  return headers.map(function (header, i) {
    var letter = columnLetter_(i + 1);
    return { index: i + 1, label: header ? letter + ': ' + header : letter };
  });
}

function getState() {
  var key = getApiKey_();
  var sheet = SpreadsheetApp.getActiveSheet();
  return {
    hasKey: Boolean(key),
    keyEnding: key ? key.slice(-4) : '',
    selection: describeSelection_(),
    columns: listColumns_(sheet),
    sheet: sheet.getName(),
    job: jobSummary_(loadJob_()),
  };
}

// ---------------------------------------------------------------------------
// Jobs: a saved position in a column, so a long list survives the time limit
// ---------------------------------------------------------------------------

function jobKey_() {
  return JOB_PREFIX + SpreadsheetApp.getActiveSpreadsheet().getId();
}

function loadJob_() {
  var raw = PropertiesService.getUserProperties().getProperty(jobKey_());
  if (!raw) return null;
  try {
    return JSON.parse(raw);
  } catch (err) {
    return null;
  }
}

function saveJob_(job) {
  PropertiesService.getUserProperties().setProperty(jobKey_(), JSON.stringify(job));
}

function discardJob() {
  PropertiesService.getUserProperties().deleteProperty(jobKey_());
  return getState();
}

function jobSummary_(job) {
  if (!job) return null;
  var total = job.endRow - job.startRow + 1;
  var done = Math.min(job.nextRow - job.startRow, total);
  return {
    mode: job.mode,
    sheet: job.sheetName,
    column: columnLetter_(job.inputCol),
    total: total,
    done: done,
    finished: job.nextRow > job.endRow,
    counts: job.counts,
  };
}

function sheetById_(sheetId) {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  for (var i = 0; i < sheets.length; i++) {
    if (sheets[i].getSheetId() === sheetId) return sheets[i];
  }
  return null;
}

function normaliseCountry_(country) {
  var code = String(country || 'US').trim().toUpperCase();
  if (!/^[A-Z]{2}$/.test(code)) throw new Error('Country must be a two-letter code, such as US or GB.');
  return code;
}

/**
 * options: { mode: 'email' | 'phone', source: 'selection' | 'column', column?: number,
 *            country?: string, live?: boolean }
 */
function startJob(options) {
  if (!getApiKey_()) throw new Error(MESSAGES.noKey);
  var mode = options.mode === 'phone' ? 'phone' : 'email';
  var sheet = SpreadsheetApp.getActiveSheet();
  var inputCol;
  var startRow;
  var endRow;

  if (options.source === 'selection') {
    var selection = describeSelection_();
    if (!selection || selection.rows < 1) {
      throw new Error('Select the cells to verify first. Row 1 is treated as the header row.');
    }
    inputCol = selection.column;
    startRow = selection.firstRow;
    endRow = selection.lastRow;
  } else {
    inputCol = Number(options.column);
    if (!inputCol || inputCol < 1) throw new Error('Choose a column to verify.');
    startRow = 2;
    endRow = sheet.getLastRow();
  }
  endRow = Math.min(endRow, sheet.getLastRow());
  if (endRow < startRow) throw new Error('There is nothing to verify below the header row.');

  var headers = HEADERS[mode];
  sheet.insertColumnsAfter(inputCol, headers.length);
  sheet.getRange(1, inputCol + 1, 1, headers.length).setValues([headers]).setFontWeight('bold');

  var job = {
    mode: mode,
    sheetId: sheet.getSheetId(),
    sheetName: sheet.getName(),
    inputCol: inputCol,
    outCol: inputCol + 1,
    startRow: startRow,
    endRow: endRow,
    nextRow: startRow,
    country: mode === 'phone' ? normaliseCountry_(options.country) : '',
    live: options.live !== false,
    retries: 0,
    counts: {},
  };
  saveJob_(job);
  return jobSummary_(job);
}

function requestFor_(job, value, key) {
  var url = job.mode === 'email'
    ? API_BASE + '/verify?email=' + encodeURIComponent(value)
    : API_BASE + '/phone?phone=' + encodeURIComponent(value) +
      '&country=' + encodeURIComponent(job.country) + '&live=' + (job.live ? 'true' : 'false');
  return {
    url: url,
    method: 'get',
    headers: { Authorization: 'Bearer ' + key },
    muteHttpExceptions: true,
  };
}

function parseBody_(response) {
  try {
    return JSON.parse(response.getContentText());
  } catch (err) {
    return {};
  }
}

function emailRow_(body) {
  var result = normaliseEmailBody_(body);
  return [result.status, riskFor_(result.status, result.advice), result.advice, result.reason];
}

function phoneRow_(body) {
  var type = body.live_lookup && body.phone_line_type ? body.phone_line_type : body.phone_type;
  return [body.phone_status || '', body.phone_e164 || '', type || '', body.phone_carrier || ''];
}

function blankRow_(mode) {
  return HEADERS[mode].map(function () {
    return '';
  });
}

function errorRow_(mode, message) {
  var row = blankRow_(mode);
  row[0] = 'error';
  row[1] = message;
  return row;
}

/**
 * Runs one batch of up to BATCH_SIZE rows. Returns the rows to write and, when a
 * call failed in a way that stops the batch, the index of the first row that
 * must be retried and why.
 */
function runBatch_(job, inputs, key) {
  var rows = [];
  var indexes = [];
  var requests = [];
  var firstSeen = {};
  var duplicateOf = {};
  inputs.forEach(function (value, i) {
    var trimmed = String(value).trim();
    rows.push(blankRow_(job.mode));
    if (!trimmed) return;
    var dedupeKey = trimmed.toLowerCase();
    if (firstSeen[dedupeKey] !== undefined) {
      duplicateOf[i] = firstSeen[dedupeKey];
      return;
    }
    firstSeen[dedupeKey] = i;
    indexes.push(i);
    requests.push(requestFor_(job, trimmed, key));
  });
  if (!requests.length) return { rows: rows, stopAt: -1, stopReason: '', message: '', waitMs: 0 };

  var responses = UrlFetchApp.fetchAll(requests);
  for (var r = 0; r < responses.length; r++) {
    var i = indexes[r];
    var code = responses[r].getResponseCode();
    var body = parseBody_(responses[r]);
    if (code === 200) {
      rows[i] = job.mode === 'email' ? emailRow_(body) : phoneRow_(body);
      var status = rows[i][0] || 'unknown';
      job.counts[status] = (job.counts[status] || 0) + 1;
      continue;
    }
    if (code === 400) {
      rows[i] = errorRow_(job.mode, body.error || 'The API could not read this value.');
      job.counts.error = (job.counts.error || 0) + 1;
      continue;
    }
    var reason = code === 401 ? 'badKey' : code === 402 ? 'inactive' : code === 429 ? 'rateLimited' : 'unavailable';
    return {
      rows: rows,
      stopAt: i,
      stopReason: reason,
      message: code === 402 ? paymentMessage_(body, MESSAGES.noApiPlan) : '',
      waitMs: code === 429 ? retryAfterMs_(responses[r], body) : RATE_LIMIT_PAUSE_MS,
    };
  }
  Object.keys(duplicateOf).forEach(function (dup) {
    rows[dup] = rows[duplicateOf[dup]].slice();
    var dupStatus = rows[dup][0] || 'unknown';
    job.counts[dupStatus] = (job.counts[dupStatus] || 0) + 1;
  });
  return { rows: rows, stopAt: -1, stopReason: '', message: '', waitMs: 0 };
}

function checkOutputColumns_(sheet, job) {
  var headers = HEADERS[job.mode];
  var current = sheet.getRange(1, job.outCol, 1, headers.length).getDisplayValues()[0];
  if (current.join('|') !== headers.join('|')) {
    throw new Error('The result columns have moved since this run started. Press Discard and start again.');
  }
}

/** Called repeatedly by the sidebar until the job reports finished. */
function processNext() {
  var job = loadJob_();
  if (!job) return null;
  var key = getApiKey_();
  if (!key) throw new Error(MESSAGES.noKey);
  var sheet = sheetById_(job.sheetId);
  if (!sheet) throw new Error('The sheet "' + job.sheetName + '" was deleted. Press Discard and start again.');
  checkOutputColumns_(sheet, job);

  var deadline = Date.now() + RUN_BUDGET_MS;
  while (job.nextRow <= job.endRow && Date.now() < deadline) {
    var count = Math.min(BATCH_SIZE, job.endRow - job.nextRow + 1);
    var started = Date.now();
    var inputs = sheet.getRange(job.nextRow, job.inputCol, count, 1).getDisplayValues().map(function (row) {
      return row[0];
    });
    var result = runBatch_(job, inputs, key);
    var written = result.stopAt === -1 ? count : result.stopAt;
    if (written > 0) {
      sheet.getRange(job.nextRow, job.outCol, written, HEADERS[job.mode].length)
        .setValues(result.rows.slice(0, written));
      job.nextRow += written;
    }

    if (result.stopAt !== -1) {
      if (result.stopReason === 'badKey' || result.stopReason === 'inactive') {
        saveJob_(job);
        throw new Error(result.message || MESSAGES[result.stopReason]);
      }
      job.retries += 1;
      saveJob_(job);
      if (job.retries > MAX_RETRIES) {
        job.retries = 0;
        saveJob_(job);
        throw new Error(MESSAGES[result.stopReason]);
      }
      Utilities.sleep(result.waitMs || RATE_LIMIT_PAUSE_MS);
      continue;
    }

    job.retries = 0;
    saveJob_(job);
    var wait = started + count * MIN_MS_PER_CALL - Date.now();
    if (wait > 0) Utilities.sleep(wait);
  }

  return jobSummary_(job);
}

// ---------------------------------------------------------------------------
// Custom functions
// ---------------------------------------------------------------------------

function singleValue_(input, name) {
  if (Array.isArray(input)) throw new Error(name + ' takes one cell. For a column, use Extensions > SimpleVerifier > Open.');
  return String(input === null || input === undefined ? '' : input).trim();
}

function documentCache_() {
  try {
    return CacheService.getDocumentCache();
  } catch (err) {
    return null;
  }
}

/**
 * Custom functions can read user properties but cannot ask for authorization or
 * show UI, so every failure is thrown as a message the cell shows on hover.
 */
function callForCell_(url, cacheKey) {
  var cache = documentCache_();
  var cached = cache ? cache.get(cacheKey) : null;
  if (cached) return JSON.parse(cached);

  var key = getApiKey_();
  if (!key) throw new Error(MESSAGES.noKey);

  var options = { method: 'get', headers: { Authorization: 'Bearer ' + key }, muteHttpExceptions: true };
  var response = UrlFetchApp.fetch(url, options);
  if (response.getResponseCode() === 429) {
    Utilities.sleep(Math.min(retryAfterMs_(response, parseBody_(response)), 8000));
    response = UrlFetchApp.fetch(url, options);
  }

  var code = response.getResponseCode();
  var body = parseBody_(response);
  if (code === 401) throw new Error(MESSAGES.badKey);
  if (code === 402) throw new Error(paymentMessage_(body, MESSAGES.noApiPlan));
  if (code === 429) throw new Error(MESSAGES.rateLimited + ' For long lists, use the sidebar.');
  if (code === 400) throw new Error(body.error || 'The API could not read this value.');
  if (code !== 200) throw new Error('SimpleVerifier is briefly unavailable. Try again in a minute.');

  if (cache) cache.put(cacheKey, JSON.stringify(body), CACHE_SECONDS);
  return body;
}

/**
 * Verifies one email address with SimpleVerifier and returns its status: valid,
 * risky or invalid. Only "valid" means the mail server confirmed the mailbox.
 *
 * @param {string} email The email address, or a cell that holds one.
 * @return The status.
 * @customfunction
 */
function SV_VERIFY(email) {
  var row = emailRowForCell_(email, 'SV_VERIFY');
  return row === '' ? '' : row[0];
}

/**
 * Like SV_VERIFY, but returns four cells: status, risk (low, medium or high),
 * send advice (safe, care, retry or do_not_send) and the reason.
 *
 * @param {string} email The email address, or a cell that holds one.
 * @return Status, risk, send advice and reason.
 * @customfunction
 */
function SV_VERIFY_DETAIL(email) {
  var row = emailRowForCell_(email, 'SV_VERIFY_DETAIL');
  return row === '' ? '' : [row];
}

function emailRowForCell_(email, name) {
  var value = singleValue_(email, name);
  if (!value) return '';
  var body = callForCell_(API_BASE + '/verify?email=' + encodeURIComponent(value), 'e:' + value.toLowerCase());
  return emailRow_(body);
}

/**
 * Validates one phone number with SimpleVerifier. Returns the status, E.164
 * form, line type and carrier (carrier only on the Email + Phone plan) in four cells.
 *
 * @param {string} phone The phone number, or a cell that holds one.
 * @param {string} country Two-letter country for numbers written without +, such as "GB". Defaults to "US".
 * @return The status, E.164 number, type and carrier.
 * @customfunction
 */
function SV_PHONE(phone, country) {
  var value = singleValue_(phone, 'SV_PHONE');
  if (!value) return '';
  var region = normaliseCountry_(country);
  var url = API_BASE + '/phone?phone=' + encodeURIComponent(value) + '&country=' + region;
  var body = callForCell_(url, 'p:' + region + ':' + value);
  return [phoneRow_(body)];
}

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
    <style>
      body { padding: 12px; }
      section { margin-bottom: 18px; }
      h2 { font-size: 13px; font-weight: bold; margin: 0 0 8px; }
      label { display: block; margin: 6px 0; }
      .hint { color: #5f6368; font-size: 11px; line-height: 1.4; }
      .row { display: flex; gap: 6px; align-items: center; margin-top: 6px; }
      .row input[type=text], .row input[type=password], select { flex: 1; width: 100%; box-sizing: border-box; }
      .bar { background: #e8eaed; border-radius: 3px; height: 8px; margin: 8px 0; overflow: hidden; }
      .bar > div { background: #1a73e8; height: 100%; width: 0; transition: width .3s; }
      .message { border-radius: 4px; font-size: 12px; line-height: 1.4; margin-top: 10px; padding: 8px; }
      .message.error { background: #fce8e6; color: #a50e0e; }
      .message.ok { background: #e6f4ea; color: #137333; }
      .hidden { display: none; }
      a { color: #1a73e8; }
    </style>
  </head>
  <body>
    <section id="key-section">
      <h2>API key</h2>
      <div id="key-saved" class="hidden">
        <div>Key saved, ending in <b id="key-ending"></b>.</div>
        <div class="row">
          <button id="change-key">Change</button>
          <button id="remove-key">Remove</button>
        </div>
      </div>
      <div id="key-form" class="hidden">
        <div class="row">
          <input type="password" id="key-input" placeholder="sv_live_..." autocomplete="off">
          <button class="action" id="save-key">Save</button>
        </div>
        <p class="hint">
          Create a key on your <a href="https://simpleverifier.com/api-keys" target="_blank">API keys page</a>.
          It is stored in your Google account for this add-on only and is never shown again.
        </p>
      </div>
    </section>

    <section id="verify-section" class="hidden">
      <h2>What is in the column?</h2>
      <label><input type="radio" name="mode" value="email" checked> Email addresses</label>
      <label><input type="radio" name="mode" value="phone"> Phone numbers</label>
      <div id="phone-options" class="hidden">
        <label for="country">Country for numbers written without +</label>
        <div class="row"><input type="text" id="country" value="US" maxlength="2" style="text-transform:uppercase"></div>
        <label><input type="checkbox" id="live" checked> Look up carrier and line type (Email + Phone plan)</label>
      </div>

      <h2 style="margin-top:14px">Which cells?</h2>
      <label><input type="radio" name="source" value="selection" checked> My selection</label>
      <p class="hint" id="selection-text"></p>
      <label><input type="radio" name="source" value="column"> A whole column</label>
      <div class="row hidden" id="column-row"><select id="column"></select></div>
      <p class="hint">Row 1 is treated as the header. Results go into new columns to the right.</p>
      <div class="row">
        <button class="action" id="start">Verify</button>
        <button id="refresh">Refresh selection</button>
      </div>
    </section>

    <section id="job-section" class="hidden">
      <h2 id="job-title"></h2>
      <div class="bar"><div id="bar"></div></div>
      <div id="job-progress"></div>
      <p class="hint" id="job-counts"></p>
      <p class="hint" id="job-keep-open">Keep this sidebar open while it runs. If you close it, open it again and press Resume.</p>
      <div class="row">
        <button class="action hidden" id="resume">Resume</button>
        <button class="hidden" id="pause">Pause</button>
        <button id="discard">Discard</button>
      </div>
    </section>

    <div id="message" class="message hidden"></div>

    <script>
      var state = null;
      var running = false;
      var paused = false;

      function $(id) { return document.getElementById(id); }
      function show(id, visible) { $(id).classList.toggle('hidden', !visible); }

      function linkify(text) {
        var escaped = String(text).replace(/&/g, '&amp;').replace(/</g, '&lt;').replace(/>/g, '&gt;');
        return escaped.replace(/https:\/\/simpleverifier\.com\/[\w\-\/]*/g, function (url) {
          return '<a href="' + url + '" target="_blank">' + url + '</a>';
        });
      }

      function say(text, kind) {
        var box = $('message');
        if (!text) { show('message', false); return; }
        box.className = 'message ' + (kind || 'ok');
        box.innerHTML = linkify(text);
      }

      function fail(err) {
        running = false;
        setBusy(false);
        say(err && err.message ? err.message : String(err), 'error');
        renderJob(state && state.job);
      }

      function call(name, arg) {
        return new Promise(function (resolve, reject) {
          var runner = google.script.run.withSuccessHandler(resolve).withFailureHandler(reject);
          if (arg === undefined) runner[name](); else runner[name](arg);
        });
      }

      function setBusy(busy) {
        ['save-key', 'start', 'refresh', 'discard', 'resume'].forEach(function (id) { $(id).disabled = busy; });
      }

      function mode() { return document.querySelector('input[name=mode]:checked').value; }
      function source() { return document.querySelector('input[name=source]:checked').value; }

      function render(next) {
        state = next;
        show('key-saved', state.hasKey);
        show('key-form', !state.hasKey);
        $('key-ending').textContent = state.keyEnding;
        show('verify-section', state.hasKey && !state.job);

        var sel = state.selection;
        if (!sel || sel.rows < 1) {
          $('selection-text').textContent = 'Select the cells to verify, then press Refresh selection.';
        } else {
          $('selection-text').textContent = sel.rows + ' rows in column ' + sel.letter + ' of ' + sel.sheet +
            ' (rows ' + sel.firstRow + ' to ' + sel.lastRow + ')' +
            (sel.multipleColumns ? '. Only the first column is used.' : '.');
        }

        var select = $('column');
        var current = select.value;
        select.innerHTML = '';
        state.columns.forEach(function (col) {
          var option = document.createElement('option');
          option.value = col.index;
          option.textContent = col.label;
          select.appendChild(option);
        });
        if (current) select.value = current;

        renderJob(state.job);
      }

      function renderJob(job) {
        show('job-section', Boolean(job) && state && state.hasKey);
        if (!job) return;
        var noun = job.mode === 'phone' ? 'phone numbers' : 'emails';
        $('job-title').textContent = job.finished
          ? 'Done: ' + noun + ' in column ' + job.column
          : 'Verifying ' + noun + ' in column ' + job.column;
        $('bar').style.width = (job.total ? Math.round(job.done / job.total * 100) : 100) + '%';
        $('job-progress').textContent = job.done.toLocaleString() + ' of ' + job.total.toLocaleString() + ' rows checked';
        $('job-counts').textContent = Object.keys(job.counts || {}).map(function (k) {
          return k + ': ' + job.counts[k].toLocaleString();
        }).join(' · ');
        show('job-keep-open', !job.finished);
        show('resume', !job.finished && !running);
        show('pause', !job.finished && running);
        $('discard').textContent = job.finished ? 'Verify another column' : 'Discard';
      }

      function loop() {
        if (paused) {
          running = false;
          setBusy(false);
          say('Paused. Press Resume to carry on where it stopped.', 'ok');
          renderJob(state.job);
          return;
        }
        call('processNext').then(function (job) {
          state.job = job;
          renderJob(job);
          if (!job || job.finished) {
            running = false;
            setBusy(false);
            renderJob(job);
            say('Finished. Results are in the new columns next to column ' + (job ? job.column : '') + '.', 'ok');
            return;
          }
          loop();
        }).catch(fail);
      }

      function run() {
        running = true;
        paused = false;
        setBusy(true);
        say('');
        renderJob(state.job);
        loop();
      }

      function refresh() {
        return call('getState').then(render).catch(fail);
      }

      $('save-key').onclick = function () {
        setBusy(true);
        say('Checking your key...');
        call('saveApiKey', $('key-input').value).then(function (next) {
          $('key-input').value = '';
          setBusy(false);
          say('Key saved.', 'ok');
          render(next);
        }).catch(fail);
      };

      $('change-key').onclick = function () {
        show('key-saved', false);
        show('key-form', true);
        $('key-input').focus();
      };

      $('remove-key').onclick = function () {
        call('removeApiKey').then(function (next) { say('Key removed.', 'ok'); render(next); }).catch(fail);
      };

      document.querySelectorAll('input[name=mode]').forEach(function (input) {
        input.onchange = function () { show('phone-options', mode() === 'phone'); };
      });
      document.querySelectorAll('input[name=source]').forEach(function (input) {
        input.onchange = function () { show('column-row', source() === 'column'); };
      });

      $('refresh').onclick = refresh;

      $('start').onclick = function () {
        var options = {
          mode: mode(),
          source: source(),
          column: Number($('column').value),
          country: $('country').value,
          live: $('live').checked,
        };
        setBusy(true);
        say('');
        call('startJob', options).then(function (job) {
          state.job = job;
          show('verify-section', false);
          run();
        }).catch(fail);
      };

      $('resume').onclick = run;
      $('pause').onclick = function () {
        paused = true;
        say('Pausing after the current batch...');
      };
      $('discard').onclick = function () {
        call('discardJob').then(function (next) { say(''); render(next); }).catch(fail);
      };

      refresh();
    </script>
  </body>
</html>

{
  "timeZone": "Etc/UTC",
  "dependencies": {},
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "oauthScopes": [
    "https://www.googleapis.com/auth/spreadsheets.currentonly",
    "https://www.googleapis.com/auth/script.external_request",
    "https://www.googleapis.com/auth/script.container.ui"
  ],
  "urlFetchWhitelist": [
    "https://simpleverifier.com/api/v1/"
  ]
}