Google Sheets add-on

/**
 * Email validation add-on for Google Sheets (email-validation.org): an Editor add-on with a cell function, a settings
 * sidebar and a menu that fills a status column for a sheet of email addresses.
 * README: integrations/google-sheets-addon/README.md
 *
 * @OnlyCurrentDoc  (the add-on reads and writes the spreadsheet it is used in, and no other)
 *
 * - =EV_VALIDATE(A2) returns the status and reason of one address (two cells side by side), or of a column of up to
 *   50 addresses (=EV_VALIDATE(A2:A20)). It is off until the key's owner turns it on for the spreadsheet in Settings
 *   (every editor's formulas there use that key). Answers are kept for 6 hours in the document's cache (keyed by a
 *   hash of the address), so a recalculation does not check (and charge) an address again.
 * - Extensions → Email validation → Validate all rows / Validate selected rows: up to 50 addresses to check are sent
 *   as single checks, 20 at a time; more go as one bulk list that the add-on uploads, starts, waits for and writes
 *   back by address (a list still running when Apps Script's time runs out is collected by the next run; a list is
 *   forgotten only once its results are written or it ended without results).
 * - Extensions → Email validation → Settings: the API key and the region (US or EU), kept in the user's own
 *   properties (PropertiesService user properties): per person, never in the sheet, never seen by other editors.
 *
 * Privacy: the add-on never logs, prints or shows an address; its messages name counts and columns only. An address is
 * sent only in the body of a POST to the API, never in a URL. The cell function's cache holds a hash of the address
 * and its status and reason only.
 *
 * Shared with the Google Sheets template (integrations/google-sheets/src/Code.js): the functions after the line
 * "Shared with the template" are copies of the template's, kept equal by test/drift.test.ts.
 */

const EV_TITLE = 'Email validation';
const EV_MENU_PATH = 'Extensions → Email validation';
const EV_HOSTS = Object.freeze({ us: 'https://api.email-validation.org', eu: 'https://api-eu.email-validation.org' });
const EV_KEY_PROPERTY = 'EV_API_KEY';
const EV_REGION_PROPERTY = 'EV_REGION';
const EV_KEY_PATTERN = /^ev_live_[0-9A-Za-z]{79}$/;
const EV_EMAIL_HEADER = /^\s*e-?mail(\s+address)?\s*$/i;
const EV_RESULT_HEADERS = Object.freeze([
  'Validation status',
  'Validation reason',
  'Validation action',
  'Did you mean',
]);
const EV_DEFAULT_BATCH = 20;
const EV_MAX_RETRIES = 3;
const EV_MAX_RETRY_WAIT_S = 10;
// Apps Script ends a run after 6 minutes. No batch starts after 4, and one batch with every retry takes well under
// 2, so every answer received is written.
const EV_RUN_BUDGET_MS = 4 * 60 * 1000;
const EV_STATUS_ORDER = Object.freeze(['valid', 'invalid', 'catch_all', 'disposable', 'unknown', 'error']);
// Free test addresses: each returns the result its name says, costs nothing and contacts no mail server.
const EV_SANDBOX_DOMAIN = 'sandbox.email-validation.org';
const EV_TEST_REASONS = Object.freeze([
  'accepted',
  'role_account',
  'no_mailbox',
  'likely_typo',
  'mailbox_full',
  'catch_all',
  'disposable',
  'greylisted',
]);
// More addresses to check than this go as one bulk list (config/integrations.ts GOOGLE_SHEETS_ADDON).
const EV_BULK_THRESHOLD = 50;
// Errors that every later request would get too: the run stops on them.
const EV_STOP_MESSAGES = Object.freeze({
  unauthorized: `The API key was refused (revoked, rolled or mistyped). Set it again: ${EV_MENU_PATH} → Settings.`,
  insufficient_credits: 'Your account is out of credits. Add credits in the dashboard, then run again.',
  key_daily_cap: 'This API key reached its daily credit cap. Run again tomorrow, or raise the cap in the dashboard.',
  forbidden: `The API refused this key: it needs the validate scope (and the bulk scope for lists of more than ${EV_BULK_THRESHOLD} addresses) and an active account. If your account processes data in the EU only, choose the EU region in ${EV_MENU_PATH} → Settings.`,
  forbidden_origin:
    'This API key is limited to certain websites (allowed origins). Use a key without allowed origins for Google Sheets.',
});
const EV_SLOW_DOWN = 'The API asked to slow down. Wait a minute, then run again.';
const EV_THROTTLED = 'The API paused checks for this account for a while (abuse_throttled). Run again later.';
const EV_SHEET_CHANGED =
  'The sheet changed during the run (rows or columns moved), so results were written only to rows that still hold their address. Run again to continue.';
const EV_NO_KEY = `Add your API key first: ${EV_MENU_PATH} → Settings.`;

// The most addresses one JSON list may hold, and its largest body (the API's limits for POST /v1/bulk with JSON).
const EV_BULK_MAX_EMAILS = 100000;
const EV_BULK_MAX_BYTES = 4 * 1024 * 1024;
// A list is waited for until 3 minutes into the run, which leaves 3 to download and write its results.
const EV_BULK_POLL_BUDGET_MS = 3 * 60 * 1000;
const EV_BULK_FIRST_WAIT_MS = 2000;
const EV_BULK_MAX_WAIT_MS = 15000;
// Lists and their results are deleted this many days after upload; a list can be started within one day less.
const EV_BULK_RETENTION_DAYS = 30;
const EV_BULK_START_DAYS = 29;
const EV_BULK_MAX_READY_UNPAID = 2;
const EV_BULK_PENDING_PREFIX = 'EV_BULK_';
const EV_JOB_ID = /^job_[0-9A-Za-z]{22}$/;
// A list in one of these states has ended without results to write; `completed` has them; any other state (also one
// the API adds later) is still going on, so the list is kept.
const EV_BULK_ENDED = Object.freeze(['failed', 'cancelled', 'expired']);

// The cell function: at most this many addresses per call, answers kept this long (CacheService's maximum). Sheets
// ends a custom function call after 30 seconds: each request asks the API to answer within 5 seconds (its shortest
// `timeout`; a check that runs out answers unknown, which is free), and no request starts after 20 seconds, so the
// last one ends by about 26.
const EV_FUNCTION_MAX = 50;
const EV_CACHE_SECONDS = 21600;
const EV_CACHE_PREFIX = 'ev1:';
const EV_FUNCTION_TIMEOUT_S = 5;
const EV_FUNCTION_BUDGET_MS = 20000;
const EV_FUNCTION_MAX_RETRY_S = 5;
// The user property that turns the cell function on for one spreadsheet: EV_FN_<spreadsheet id> = that id.
const EV_FUNCTION_PROPERTY_PREFIX = 'EV_FN_';
const EV_FUNCTION_OFF = `Off in this spreadsheet: the owner of the API key turns =EV_VALIDATE on in ${EV_MENU_PATH} → Settings (or use Validate all rows).`;

/** Runs when the add-on is installed: the menu is added at once, without reloading the sheet. */
function onInstall(e) {
  onOpen(e);
}

/**
 * Adds the add-on's menu under Extensions. Runs in AuthMode.NONE before the add-on is used in this file, so it calls
 * nothing but the menu.
 */
function onOpen(_e) {
  SpreadsheetApp.getUi()
    .createAddonMenu()
    .addItem('Settings', 'evShowSidebar')
    .addSeparator()
    .addItem('Validate all rows', 'evValidateAll')
    .addItem('Validate selected rows', 'evValidateSelection')
    .addItem('Add test addresses', 'evAddTestAddresses')
    .addToUi();
}

function evShowSidebar() {
  SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutputFromFile('Sidebar').setTitle(EV_TITLE));
}

function evValidateAll() {
  evAlert(evRun(false));
}

function evValidateSelection() {
  evAlert(evRun(true));
}

function evAddTestAddresses() {
  evAlert(evTestAddresses());
}

// ---- Called by the sidebar (google.script.run): each returns what the sidebar shows. ----

/**
 * The settings the sidebar shows: whether a key is saved (never the key itself), the region, whether =EV_VALIDATE is
 * on in this spreadsheet, and whether this sheet waits for a list.
 */
function evSettings() {
  const props = PropertiesService.getUserProperties();
  return {
    hasKey: Boolean((props.getProperty(EV_KEY_PROPERTY) || '').trim()),
    region: evRegion(props),
    cellFunction: evFunctionOn(props),
    pending: Boolean(evPendingJob(props, SpreadsheetApp.getActiveSheet())),
  };
}

/** The user property that turns =EV_VALIDATE on for the active spreadsheet. */
function evFunctionProperty() {
  return EV_FUNCTION_PROPERTY_PREFIX + SpreadsheetApp.getActiveSpreadsheet().getId();
}

/** Whether these properties turn =EV_VALIDATE on for the active spreadsheet (its id, stored under its id). */
function evFunctionOn(props) {
  const id = SpreadsheetApp.getActiveSpreadsheet().getId();
  return Boolean(id) && props.getProperty(EV_FUNCTION_PROPERTY_PREFIX + id) === id;
}

/**
 * Saves the key (when one is given, after checking its form), the region and, when the form says, whether
 * =EV_VALIDATE is on in this spreadsheet, all in the user's own properties.
 */
function evSaveSettings(form) {
  const props = PropertiesService.getUserProperties();
  const region = String((form && form.region) || 'us').toLowerCase();
  if (!Object.prototype.hasOwnProperty.call(EV_HOSTS, region)) return { ok: false, message: 'Choose US or EU.' };
  const key = String((form && form.key) || '').trim();
  if (key && !EV_KEY_PATTERN.test(key)) {
    return {
      ok: false,
      message:
        'That is not an API key: keys start with ev_live_ and are 87 characters long. Copy it again from the dashboard.',
    };
  }
  if (key) props.setProperty(EV_KEY_PROPERTY, key);
  props.setProperty(EV_REGION_PROPERTY, region);
  const saved = [key ? 'API key and region saved.' : 'Region saved.'];
  if (form && typeof form.cellFunction === 'boolean') {
    if (form.cellFunction) {
      props.setProperty(evFunctionProperty(), SpreadsheetApp.getActiveSpreadsheet().getId());
      saved.push('=EV_VALIDATE is on in this spreadsheet: every editor’s formulas here use your API key and credits.');
    } else {
      props.deleteProperty(evFunctionProperty());
      saved.push('=EV_VALIDATE is off in this spreadsheet.');
    }
  }
  return { ok: true, message: saved.join(' ') };
}

function evRemoveKey() {
  PropertiesService.getUserProperties().deleteProperty(EV_KEY_PROPERTY);
  return { ok: true, message: 'API key removed from your settings.' };
}

function evSidebarValidate(selectionOnly) {
  return evRun(Boolean(selectionOnly));
}

function evSidebarTestAddresses() {
  return evTestAddresses();
}

/**
 * Cancels the list this sheet is waiting for and forgets it. A list that has already finished cannot be cancelled: it
 * is kept, so the next run writes its results (no new charge).
 */
function evCancelPending() {
  const props = PropertiesService.getUserProperties();
  const sheet = SpreadsheetApp.getActiveSheet();
  const job = evPendingJob(props, sheet);
  if (!job) return 'This sheet is not waiting for a list.';
  const key = (props.getProperty(EV_KEY_PROPERTY) || '').trim();
  if (!key) return EV_NO_KEY;
  let answer;
  try {
    answer = evApi('delete', `${EV_HOSTS[job.region]}/v1/bulk/${job.id}`, key, null);
  } catch (_err) {
    return 'The API could not be reached. Try again in a minute.';
  }
  if (answer.http >= 200 && answer.http < 300) {
    props.deleteProperty(evPendingProperty(sheet));
    return 'The list was cancelled. Checks it had already done are charged and their results are not kept; its unused credits are returned. Rows it would have filled stay empty: the next run checks them again.';
  }
  if (answer.code === 'conflict') {
    // finalizing, completed, failed or expired: kept, so the next run writes its results or forgets it with the reason
    return 'The list has already finished and can no longer be cancelled: run Validate to write its results (no new charge), or to see why it ended without them.';
  }
  if (answer.code === 'not_found') {
    props.deleteProperty(evPendingProperty(sheet));
    return 'The list was not found for this API key (was the key changed?); this sheet no longer waits for it. Run Validate again to check the rows that are still empty.';
  }
  return `The list could not be cancelled (${answer.code}). Try again in a minute.`;
}

// ---- The cell function ----

/**
 * Checks email addresses with email-validation.org: the status and the reason of each, in two cells side by side.
 * Uses the API key in Extensions → Email validation → Settings; an address checked in the last 6 hours is not
 * checked (or charged) again.
 *
 * @param {string|Array<Array<string>>} email The address, or one column of up to 50 addresses.
 * @return The status and reason of each address.
 * @customfunction
 */
function EV_VALIDATE(email) {
  const rows = Array.isArray(email) ? email : [[email]];
  if (rows.some((r) => Array.isArray(r) && r.length > 1)) {
    return [['error', 'Pass one column of addresses.']];
  }
  const values = rows.map((r) => {
    const v = Array.isArray(r) ? r[0] : r;
    return v === null || v === undefined ? '' : String(v).trim();
  });
  const keys = values.map(evAddressKey);
  const distinct = Array.from(new Set(keys.filter((k) => k)));
  const fail = (reason) => values.map((v) => (v ? ['error', reason] : ['', '']));
  if (!distinct.length) return values.map(() => ['', '']);
  if (distinct.length > EV_FUNCTION_MAX) {
    return fail(`More than ${EV_FUNCTION_MAX} addresses: use ${EV_MENU_PATH} → Validate all rows.`);
  }
  const props = PropertiesService.getUserProperties();
  const key = (props.getProperty(EV_KEY_PROPERTY) || '').trim();
  if (!key) return fail(`Set your API key: ${EV_MENU_PATH} → Settings.`);
  // in a custom function the user properties may be the spreadsheet owner's (Google's docs): the owner's own opt-in
  // for this very spreadsheet must be there, so nobody else's formulas spend a key without its owner's word
  if (!evFunctionOn(props)) return fail(EV_FUNCTION_OFF);
  const answers = evCachedAnswers(distinct);
  const missing = distinct.filter((k) => !answers[k]);
  if (missing.length) {
    const first = new Map();
    keys.forEach((k, i) => {
      if (k && !first.has(k)) first.set(k, values[i]);
    });
    const fetched = evFunctionFetch(
      missing.map((k) => ({ key: k, address: first.get(k) })),
      key,
      EV_HOSTS[evRegion(props)],
    );
    for (const k of missing) answers[k] = fetched[k];
  }
  return keys.map((k) => (k ? answers[k] : ['', '']));
}

/**
 * The cache of the cell function: this document's cache (every editor's recalculation shares it), or null. Never the
 * script cache, which an add-on shares with every user in every spreadsheet.
 */
function evCache() {
  return CacheService.getDocumentCache();
}

/** A cache key that does not hold the address: a hash of the address key. */
function evCacheKey(addressKey) {
  const digest = Utilities.computeDigest(Utilities.DigestAlgorithm.SHA_256, addressKey, Utilities.Charset.UTF_8);
  return EV_CACHE_PREFIX + Utilities.base64EncodeWebSafe(digest);
}

/** { <address key>: [status, reason] } for the addresses the cache holds. */
function evCachedAnswers(addressKeys) {
  const out = {};
  const cacheKeys = addressKeys.map(evCacheKey);
  let found = {};
  try {
    const cache = evCache();
    found = (cache && cache.getAll(cacheKeys)) || {};
  } catch (_err) {
    found = {};
  }
  addressKeys.forEach((k, i) => {
    const text = found[cacheKeys[i]];
    if (!text) return;
    try {
      const cells = JSON.parse(text);
      if (Array.isArray(cells) && cells.length === 2 && cells.every((c) => typeof c === 'string')) out[k] = cells;
    } catch (_err) {
      // an unreadable entry is checked again
    }
  });
  return out;
}

/**
 * Keeps the charged answers. Errors and unknown results (free, and worth checking again) are not kept: the next
 * recalculation checks them again.
 */
function evRemember(answers) {
  const values = {};
  for (const k of Object.keys(answers)) {
    const status = answers[k][0];
    if (status !== 'error' && status !== 'unknown') values[evCacheKey(k)] = JSON.stringify(answers[k]);
  }
  if (!Object.keys(values).length) return;
  try {
    const cache = evCache();
    if (cache) cache.putAll(values, EV_CACHE_SECONDS);
  } catch (_err) {
    // a full or unavailable cache only means the next recalculation checks again
  }
}

/**
 * Checks addresses for the cell function, 20 at a time and at most one batch a second, with no request started after
 * 20 seconds; a 429 or 503 (never charged) is sent again once when its Retry-After is short. Each batch's answers are
 * cached as soon as they arrive, so a call Sheets ends anyway keeps what was charged. { <address key>: [status, reason] }.
 */
function evFunctionFetch(items, key, host) {
  const started = Date.now();
  const out = {};
  for (let next = 0; next < items.length; next += EV_DEFAULT_BATCH) {
    const batch = items.slice(next, next + EV_DEFAULT_BATCH);
    if (Date.now() - started > EV_FUNCTION_BUDGET_MS) {
      for (const item of batch) out[item.key] = ['error', 'timeout'];
      continue;
    }
    const batchStarted = Date.now();
    let todo = batch;
    for (let attempt = 0; todo.length; attempt++) {
      let responses;
      try {
        responses = UrlFetchApp.fetchAll(todo.map((item) => evFunctionRequest(host, key, item.address)));
      } catch (_err) {
        for (const item of todo) out[item.key] = ['error', 'network_error'];
        break;
      }
      const again = [];
      const answered = {};
      let wait = 0;
      responses.forEach((response, j) => {
        const answer = evRead(response);
        const cells = answer.cells[0] === 'error' ? ['error', answer.code] : answer.cells.slice(0, 2);
        out[todo[j].key] = cells;
        answered[todo[j].key] = cells;
        if (answer.retryable && attempt === 0) {
          again.push(todo[j]);
          wait = Math.max(wait, answer.retryAfter === null ? evBackoff(0) : answer.retryAfter);
        }
      });
      evRemember(answered);
      if (!again.length || wait > EV_FUNCTION_MAX_RETRY_S) break;
      if (Date.now() - started + wait * 1000 > EV_FUNCTION_BUDGET_MS) break;
      Utilities.sleep(Math.ceil(wait * 1000));
      todo = again;
    }
    const pause = 1000 - (Date.now() - batchStarted);
    if (next + EV_DEFAULT_BATCH < items.length && pause > 0) Utilities.sleep(pause);
  }
  return out;
}

/** The template's request, with the API's shortest check time limit (the cell function must answer within 30 s). */
function evFunctionRequest(host, key, address) {
  const request = evRequest(host, key, address);
  request.payload = JSON.stringify({ email: address, timeout: EV_FUNCTION_TIMEOUT_S });
  return request;
}

// ---- Menu and sidebar runs ----

/** Adds a sheet of free test addresses to try the add-on on. */
function evTestAddresses() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const name = 'Test addresses';
  let sheet = spreadsheet.getSheetByName(name);
  if (!sheet) {
    sheet = spreadsheet.insertSheet(name);
    const rows = [['Email']].concat(EV_TEST_REASONS.map((reason) => [`${reason}@${EV_SANDBOX_DOMAIN}`]));
    sheet.getRange(1, 1, rows.length, 1).setValues(rows);
  }
  spreadsheet.setActiveSheet(sheet);
  return `The sheet "${name}" holds test addresses: each returns a fixed result for free. Choose ${EV_MENU_PATH} → Validate all rows, or type =EV_VALIDATE(A2) in a cell, to try them.`;
}

function evRegion(props) {
  const region = (props.getProperty(EV_REGION_PROPERTY) || 'us').trim().toLowerCase();
  return Object.prototype.hasOwnProperty.call(EV_HOSTS, region) ? region : 'us';
}

/**
 * One run over the active sheet (or the selected rows of it); returns the message for the user. A sheet waiting for
 * a list is first given that list's results.
 */
function evRun(selectionOnly) {
  const props = PropertiesService.getUserProperties();
  const key = (props.getProperty(EV_KEY_PROPERTY) || '').trim();
  if (!key) return EV_NO_KEY;
  const lock = LockService.getDocumentLock();
  if (!lock.tryLock(1000)) return 'A validation run is already going on in this spreadsheet. Wait for it to finish.';
  try {
    const sheet = SpreadsheetApp.getActiveSheet();
    const started = Date.now();
    const pending = evPendingJob(props, sheet);
    if (pending) return evBulkContinue(sheet, props, pending, key, started);
    const host = EV_HOSTS[evRegion(props)];
    const range = selectionOnly ? SpreadsheetApp.getActiveRange() : null;
    const plan = evPlan(sheet, range);
    if (typeof plan === 'string') return plan;
    if (plan.items.length <= EV_BULK_THRESHOLD) return evCheck(sheet, plan, key, host, EV_DEFAULT_BATCH);
    return evBulkStart(sheet, props, plan, key, evRegion(props), started);
  } finally {
    lock.releaseLock();
  }
}

// ---- Bulk lists (more than 50 addresses) ----

/** The user property that remembers the list a sheet waits for (one per spreadsheet and sheet). */
function evPendingProperty(sheet) {
  return `${EV_BULK_PENDING_PREFIX}${SpreadsheetApp.getActiveSpreadsheet().getId()}_${sheet.getSheetId()}`;
}

/** The list this sheet waits for: { id, region }, or null. Holds no address. */
function evPendingJob(props, sheet) {
  const text = props.getProperty(evPendingProperty(sheet));
  if (!text) return null;
  try {
    const job = JSON.parse(text);
    if (job && EV_JOB_ID.test(job.id) && Object.prototype.hasOwnProperty.call(EV_HOSTS, job.region)) return job;
  } catch (_err) {
    // unreadable: forgotten below
  }
  props.deleteProperty(evPendingProperty(sheet));
  return null;
}

/** Asks before a list is sent: it may use as many credits as it holds addresses. */
function evConfirmList(count) {
  const ui = SpreadsheetApp.getUi();
  const n = count.toLocaleString('en-US');
  const answer = ui.alert(
    EV_TITLE,
    `Check ${n} addresses as one bulk list? Each address checked uses 1 credit (1 credit = 1 validated address), so the list uses at most ${n} credits. Unknown results, malformed addresses and test addresses on the sandbox domain are free.`,
    ui.ButtonSet.YES_NO,
  );
  return answer === ui.Button.YES;
}

/** Sends the planned addresses as one list (after the user agreed), then waits for it like a later run would. */
function evBulkStart(sheet, props, plan, key, region, started) {
  if (plan.items.length > EV_BULK_MAX_EMAILS) {
    return `This sheet holds ${plan.items.length.toLocaleString('en-US')} addresses to check; one run sends at most ${EV_BULK_MAX_EMAILS.toLocaleString('en-US')}. Select fewer rows, then run Validate selected rows.`;
  }
  const payload = JSON.stringify({ emails: plan.items.map((item) => item.address) });
  if (Utilities.newBlob(payload).getBytes().length > EV_BULK_MAX_BYTES) {
    return 'These addresses are too large for one list. Select fewer rows, then run Validate selected rows.';
  }
  if (!evConfirmList(plan.items.length)) return 'Nothing was sent.';
  // the confirmation may have stayed open while others edited the sheet: copies go only to rows still in place
  const copied = evPrepareColumns(sheet, plan);
  const answer = evApiRetrying('post', `${EV_HOSTS[region]}/v1/bulk?start=false`, key, payload, started);
  if (answer.network) {
    return 'The API could not be reached. If the list arrived, it waits unstarted in the dashboard (no credits used): cancel it there, then run again in a minute.';
  }
  const job = answer.body;
  if (answer.http !== 202 || !job || !EV_JOB_ID.test(String(job.id))) {
    return evBulkRefused(answer, 'The list could not be sent');
  }
  const pending = { id: job.id, region: region };
  props.setProperty(evPendingProperty(sheet), JSON.stringify(pending));
  return evBulkContinue(sheet, props, pending, key, started, copied);
}

/**
 * Writes the result headers (adding columns when the sheet has no room) and the results copied from other rows, to
 * rows that still hold their address (as the template's evCheck does). Returns how many rows took a copy.
 */
function evPrepareColumns(sheet, plan) {
  const width = EV_RESULT_HEADERS.length;
  const missing = plan.resultColumn + width - 1 - sheet.getMaxColumns();
  if (missing > 0) sheet.insertColumnsAfter(sheet.getMaxColumns(), missing);
  sheet.getRange(1, plan.resultColumn, 1, width).setValues([EV_RESULT_HEADERS.slice()]);
  if (!plan.copies.length) return 0;
  const current = evRowsStillInPlace(sheet, plan, plan.copies);
  const cells = [];
  for (const item of plan.copies) {
    for (const row of item.rows) if (current[row]) cells.push([row, item.cells]);
  }
  evWrite(sheet, plan.resultColumn, cells);
  return cells.length;
}

/**
 * Waits for the list (starting it once it is ready), then writes its results. When Apps Script's time runs short
 * the list keeps running and the next run collects it.
 */
function evBulkContinue(sheet, props, pending, key, started, copiedBefore) {
  const host = EV_HOSTS[pending.region];
  const forget = () => props.deleteProperty(evPendingProperty(sheet));
  let wait = EV_BULK_FIRST_WAIT_MS;
  let job = null;
  for (;;) {
    const answer = evApiRetrying('get', `${host}/v1/bulk/${pending.id}`, key, null, started);
    if (answer.network) return 'The API could not be reached. The list keeps running: run Validate again later.';
    if (answer.code === 'not_found') {
      forget();
      return 'The list this sheet was waiting for was not found for this API key (was the key changed?). Run again to check the rows that are still empty.';
    }
    if (answer.http !== 200 || !answer.body) return evBulkRefused(answer, 'The list could not be read');
    job = answer.body;
    if (job.status === 'completed') break;
    if (EV_BULK_ENDED.indexOf(job.status) >= 0) {
      forget();
      return evBulkEnded(job);
    }
    if (job.status === 'ready') {
      const start = evApiRetrying('post', `${host}/v1/bulk/${pending.id}/start`, key, null, started);
      if (start.network) return 'The API could not be reached. The list waits: run Validate again later.';
      if (start.code === 'conflict') {
        return `The list could not be started (conflict): a list can be started within ${EV_BULK_START_DAYS} days of upload, and this one is older or has changed state. Run Validate again: a list that ended is then forgotten with the reason.`;
      }
      if (start.http !== 200) {
        const why = evBulkRefused(start, 'The list could not be started');
        return `${why}\nThe list waits (it can be started within ${EV_BULK_START_DAYS} days of upload): run Validate again to start it.`;
      }
      job = start.body || job;
    }
    if (Date.now() - started + wait > EV_BULK_POLL_BUDGET_MS) {
      const done = Number(job.processed) || 0;
      const total = Number(job.unique_emails) || 0;
      const progress = total ? ` (${done.toLocaleString('en-US')} of ${total.toLocaleString('en-US')} done)` : '';
      return `The list is still being checked${progress}. Apps Script limits how long a run may take: run Validate again in a few minutes (within ${EV_BULK_RETENTION_DAYS} days) to write its results.`;
    }
    Utilities.sleep(wait);
    wait = Math.min(EV_BULK_MAX_WAIT_MS, wait * 2);
  }
  const download = evApiRetrying(
    'get',
    `${host}/v1/bulk/${pending.id}/results?columns=email_only`,
    key,
    null,
    started,
    true,
  );
  if (download.network)
    return 'The API could not be reached. The list is done: run Validate again to write its results.';
  if (download.http !== 200) return evBulkRefused(download, 'The results could not be downloaded');
  const results = evResultsByKey(download.text);
  if (!results) return 'The results could not be read. Run Validate again to download them again.';
  const written = evBulkWrite(sheet, results, job, copiedBefore || 0);
  // kept when the sheet cannot take the results yet (its layout changed): they are paid, and the next run writes them
  if (written.done) forget();
  return written.message;
}

/** Why a list ended without results to write (failed, cancelled, expired). */
function evBulkEnded(job) {
  const code = job.error_code ? ` (${evCell(job.error_code)})` : '';
  if (job.status === 'expired') {
    return `The list expired: lists and their results are deleted ${EV_BULK_RETENTION_DAYS} days after upload, so its results can no longer be written. Run again to check the rows that are still empty.`;
  }
  if (job.status === 'cancelled') {
    return 'The list was cancelled. Checks it had already done are charged and their results are not kept; its unused credits are returned. Run again to check the rows that are still empty.';
  }
  return `The list failed${code}: its unused credits are returned. Run again to check the rows that are still empty.`;
}

/**
 * Writes a finished list's results to every row of the sheet (not only the rows the run was given) that still needs a
 * status and holds an address of the list, as the sheet is now: rows may have moved since the list was sent, and
 * every result of the list is paid for. { message, done }: done is false when the sheet cannot take the results yet
 * (no address column, a column in the way, …), so the list is kept for the next run.
 */
function evBulkWrite(sheet, results, job, copiedBefore) {
  const plan = evPlan(sheet, null);
  const credits = Number(job.credits_charged);
  const used = Number.isFinite(credits)
    ? `The list used ${credits.toLocaleString('en-US')} ${credits === 1 ? 'credit' : 'credits'}.`
    : '';
  if (typeof plan === 'string') {
    // every row already has a status: nothing left to write
    if (plan.indexOf('Nothing to validate') === 0) {
      return { message: [`The list is done. ${plan}`, used].filter(Boolean).join('\n'), done: true };
    }
    return {
      message: `The list is done, but its results cannot be written yet: ${plan}\nThen run Validate to write them (no new charge; the results are kept ${EV_BULK_RETENTION_DAYS} days after upload).`,
      done: false,
    };
  }
  const copied = evPrepareColumns(sheet, plan) + copiedBefore;
  const counts = {};
  const cells = [];
  let checked = 0;
  let left = 0;
  for (const item of plan.items) {
    const answer = results.get(item.key);
    if (!answer) {
      left++;
      continue;
    }
    checked++;
    counts[answer[0]] = (counts[answer[0]] || 0) + 1;
    for (const row of item.rows) cells.push([row, answer]);
  }
  evWrite(sheet, plan.resultColumn, cells);
  // rows that took a copied result before the list was sent are counted as copied, not as skipped
  const skipped = Math.max(0, plan.skipped - copiedBefore);
  return {
    message: [evSummary(counts, checked, left, skipped, copied, ''), used].filter(Boolean).join('\n'),
    done: true,
  };
}

/** The results CSV (columns=email_only) as a Map of address key → the four result cells; null when unreadable. */
function evResultsByKey(text) {
  const rows = evParseCsv(text);
  if (!rows || !rows.length) return null;
  const header = rows[0];
  const at = ['ev_status', 'ev_reason', 'ev_action', 'ev_did_you_mean'].map((h) => header.indexOf(h));
  if (header[0] !== 'email' || at.some((i) => i < 0)) return null;
  const out = new Map();
  for (let r = 1; r < rows.length; r++) {
    const row = rows[r];
    if (row.length < header.length) continue;
    // the API writes an address that starts like a formula with an apostrophe in front (CSV safety)
    const address = /^'[=+\-@\t\r＝＋－＠]/.test(row[0]) ? row[0].slice(1) : row[0];
    const key = evAddressKey(address);
    if (key && !out.has(key))
      out.set(
        key,
        at.map((i) => evCell(evUnescape(row[i]))),
      );
  }
  return out;
}

/** One of our result cells without the CSV-safety apostrophe the API puts before a formula character. */
function evUnescape(value) {
  return /^'[=+\-@\t\r＝＋－＠]/.test(value) ? value.slice(1) : value;
}

/** RFC 4180 CSV (quoted fields, doubled quotes, CRLF or LF line ends) → rows of fields; null when malformed. */
function evParseCsv(text) {
  const rows = [];
  let row = [];
  let field = '';
  let quoted = false;
  let i = 0;
  const s = String(text || '');
  while (i < s.length) {
    const c = s[i];
    if (quoted) {
      if (c === '"') {
        if (s[i + 1] === '"') {
          field += '"';
          i += 2;
          continue;
        }
        quoted = false;
        i++;
        continue;
      }
      field += c;
      i++;
      continue;
    }
    if (c === '"' && field === '') {
      quoted = true;
    } else if (c === ',') {
      row.push(field);
      field = '';
    } else if (c === '\n' || c === '\r') {
      if (c === '\r' && s[i + 1] === '\n') i++;
      row.push(field);
      rows.push(row);
      row = [];
      field = '';
    } else {
      field += c;
    }
    i++;
  }
  if (quoted) return null;
  if (field !== '' || row.length) {
    row.push(field);
    rows.push(row);
  }
  return rows;
}

/** The message for a list request the API refused. */
function evBulkRefused(answer, what) {
  const code = answer.code;
  if (code === 'forbidden') {
    return `${what} (forbidden): lists need an API key with the bulk scope and an active account. If your account processes data in the EU only, choose the EU region in ${EV_MENU_PATH} → Settings (where lists are not yet available, select at most ${EV_BULK_THRESHOLD} addresses to check them one by one).`;
  }
  if (Object.prototype.hasOwnProperty.call(EV_STOP_MESSAGES, code)) return EV_STOP_MESSAGES[code];
  if (code === 'conflict') {
    return `${what} (conflict). An account without a purchase may have at most ${EV_BULK_MAX_READY_UNPAID} lists waiting to start: start or cancel one in the dashboard, then run again.`;
  }
  if (code === 'payload_too_large')
    return `${what}: it is too large. Select fewer rows, then run Validate selected rows.`;
  if (code === 'rate_limited' || code === 'service_unavailable') return EV_SLOW_DOWN;
  if (code === 'abuse_throttled') return EV_THROTTLED;
  return `${what} (${evCell(code)}). Run again in a few minutes.`;
}

/** evApi, with 429 and 503 answers (never charged) sent again after Retry-After, while the run has time. */
function evApiRetrying(method, url, key, payload, started, raw) {
  let answer = null;
  for (let attempt = 0; attempt <= EV_MAX_RETRIES; attempt++) {
    try {
      answer = evApi(method, url, key, payload, raw);
    } catch (_err) {
      return { network: true, http: 0, code: 'network_error', body: null, text: '' };
    }
    const retryable = (answer.http === 429 || answer.http === 503) && answer.code !== 'key_daily_cap';
    if (!retryable || attempt === EV_MAX_RETRIES) break;
    const wait = answer.retryAfter === null ? evBackoff(attempt) : answer.retryAfter;
    if (wait > EV_MAX_RETRY_WAIT_S || Date.now() - started + wait * 1000 > EV_BULK_POLL_BUDGET_MS) break;
    Utilities.sleep(Math.ceil(wait * 1000));
  }
  return answer;
}

/**
 * One request to the API: { http, code, body, text, retryAfter }. `code` is the error code of an error answer ('' on
 * success). `raw` keeps the body as text (the results CSV) instead of reading it as JSON.
 */
function evApi(method, url, key, payload, raw) {
  const options = {
    method: method,
    headers: { Authorization: `Bearer ${key}` },
    muteHttpExceptions: true,
    followRedirects: false,
  };
  if (payload !== null && payload !== undefined) {
    options.contentType = 'application/json';
    options.payload = payload;
  }
  const response = UrlFetchApp.fetch(url, options);
  const http = response.getResponseCode();
  const text = response.getContentText();
  let body = null;
  if (!raw || http < 200 || http >= 300) {
    try {
      body = JSON.parse(text);
    } catch (_err) {
      body = null;
    }
  }
  let code = '';
  if (http < 200 || http >= 300) {
    code = body && body.error && typeof body.error.code === 'string' ? body.error.code : 'unexpected_response';
  }
  return {
    network: false,
    http: http,
    code: code,
    body: raw ? null : body,
    text: raw ? text : '',
    retryAfter: http === 429 || http === 503 ? evRetryAfter(response.getHeaders()) : null,
  };
}

function evAlert(text) {
  const ui = SpreadsheetApp.getUi();
  ui.alert(EV_TITLE, text, ui.ButtonSet.OK);
}

// ---- Shared with the template (integrations/google-sheets/src/Code.js): keep equal (test/drift.test.ts) ----

/**
 * Finds the address column (headed Email, E-mail or Email address in row 1), the result columns and the addresses
 * still to check. Rows that already have a status are skipped; a row whose status is "error" is checked again. Each
 * address is checked once: rows holding it in any mix of case share one check, and a row whose address already has a
 * result in another row of the sheet takes a copy of that result instead of a new (charged) check.
 * Returns a message for the user instead when there is nothing to do.
 */
function evPlan(sheet, selection) {
  const lastRow = sheet.getLastRow();
  const lastColumn = sheet.getLastColumn();
  if (lastRow < 2) {
    return 'This sheet has no addresses: put the header Email in row 1 and one address per row below it.';
  }
  const headers = sheet
    .getRange(1, 1, 1, lastColumn)
    .getValues()[0]
    .map((h) => String(h).trim());
  const emailColumn = headers.findIndex((h) => EV_EMAIL_HEADER.test(h)) + 1;
  if (!emailColumn) return 'No address column found: head the column that holds your addresses Email (in row 1).';
  const found = headers.indexOf(EV_RESULT_HEADERS[0]) + 1;
  const resultColumn = found || lastColumn + 1;
  const width = EV_RESULT_HEADERS.length;
  if (found) {
    for (let i = 1; i < width; i++) {
      const h = headers[found - 1 + i];
      if (h && h !== EV_RESULT_HEADERS[i]) {
        return `Column ${found + i} is in the way: the ${width} columns from "${EV_RESULT_HEADERS[0]}" on hold the results. Move that column, then run again.`;
      }
    }
    if (emailColumn >= found && emailColumn < found + width) {
      return 'The address column is inside the result columns: move it, then run again.';
    }
    // the result columns that exist yet (a range past the sheet's last column is an error)
    const present = Math.min(width, sheet.getMaxColumns() - found + 1);
    const below = sheet.getRange(2, found, lastRow - 1, present).getValues();
    for (let i = 1; i < present; i++) {
      if (!headers[found - 1 + i] && below.some((r) => String(r[i]).trim() !== '')) {
        return `Column ${found + i} is in the way: it has no header but holds data where the results go. Move that column, then run again.`;
      }
    }
  }
  let first = 2;
  let last = lastRow;
  if (selection) {
    first = Math.max(2, selection.getRow());
    last = Math.min(lastRow, selection.getLastRow());
    if (first > last) return 'Select the rows to validate first (below the header row).';
  }
  // the whole sheet is read, also for a selection, so a result anywhere in it is reused rather than paid for again
  const emails = sheet.getRange(2, emailColumn, lastRow - 1, 1).getValues();
  const results = found
    ? sheet.getRange(2, resultColumn, lastRow - 1, Math.min(width, sheet.getMaxColumns() - found + 1)).getValues()
    : null;
  const resultOf = (i) => (results ? String(results[i][0]).trim() : '');
  const known = new Map();
  for (let i = 0; i < emails.length; i++) {
    const key = evAddressKey(emails[i][0]);
    const status = resultOf(i);
    if (key && status && status !== 'error' && !known.has(key)) {
      const cells = [];
      for (let c = 0; c < width; c++) cells.push(evCell(c < results[i].length ? results[i][c] : ''));
      known.set(key, cells);
    }
  }
  const byKey = new Map();
  const copies = new Map();
  let skipped = 0;
  for (let row = first; row <= last; row++) {
    const address = String(emails[row - 2][0]).trim();
    const key = evAddressKey(address);
    if (!key) continue;
    const status = resultOf(row - 2);
    if (status && status !== 'error') {
      skipped++;
      continue;
    }
    const into = known.has(key) ? copies : byKey;
    const item = into.get(key);
    if (item) item.rows.push(row);
    else into.set(key, { address: address, key: key, rows: [row], cells: known.get(key) });
  }
  if (!byKey.size && !copies.size) {
    return skipped
      ? 'Nothing to validate: every address in these rows already has a status. Clear a status to check that address again.'
      : 'Nothing to validate: these rows hold no addresses.';
  }
  return {
    emailColumn: emailColumn,
    emailHeader: headers[emailColumn - 1],
    resultColumn: resultColumn,
    items: Array.from(byKey.values()),
    copies: Array.from(copies.values()),
    skipped: skipped,
  };
}

/** The form under which two cells hold the same address, as the API compares them: trimmed, NFC, lowercased. */
function evAddressKey(value) {
  return String(value).trim().normalize('NFC').toLowerCase();
}

/** Checks the planned addresses in batches and writes each batch's results as soon as they arrive. */
function evCheck(sheet, plan, key, host, batchSize) {
  const started = Date.now();
  const width = EV_RESULT_HEADERS.length;
  const missing = plan.resultColumn + width - 1 - sheet.getMaxColumns();
  if (missing > 0) sheet.insertColumnsAfter(sheet.getMaxColumns(), missing);
  sheet.getRange(1, plan.resultColumn, 1, width).setValues([EV_RESULT_HEADERS.slice()]);
  const counts = {};
  let next = 0;
  let stop = '';
  let copied = 0;
  if (plan.copies.length) {
    // results already in the sheet for the same address: copied, never checked (and charged) again
    const current = evRowsStillInPlace(sheet, plan, plan.copies);
    const cells = [];
    for (const item of plan.copies) {
      for (const row of item.rows) {
        if (current[row]) cells.push([row, item.cells]);
      }
    }
    evWrite(sheet, plan.resultColumn, cells);
    copied = cells.length;
    if (current.changed) stop = EV_SHEET_CHANGED;
  }
  while (!stop && next < plan.items.length) {
    if (Date.now() - started > EV_RUN_BUDGET_MS) {
      stop = 'Apps Script limits how long a run may take, so this run stopped after 4 minutes.';
      break;
    }
    const batch = plan.items.slice(next, next + batchSize);
    const batchStarted = Date.now();
    let answers;
    try {
      answers = evFetchBatch(batch, key, host);
    } catch (_err) {
      // No answer (network failure): the requests may have reached the API, so they are not sent again by
      // themselves. Their rows stay empty and the next run checks them.
      stop = 'The API could not be reached, so the last rows were left empty.';
      break;
    }
    next += batch.length;
    // A menu run does not lock the sheet: write only where the sheet still looks as it did when the run started.
    const current = evRowsStillInPlace(sheet, plan, batch);
    const cells = [];
    batch.forEach((item, i) => {
      const status = answers[i].cells[0];
      counts[status] = (counts[status] || 0) + 1;
      for (const row of item.rows) if (current[row]) cells.push([row, answers[i].cells]);
    });
    evWrite(sheet, plan.resultColumn, cells);
    if (current.changed) {
      stop = EV_SHEET_CHANGED;
      break;
    }
    const stopped = answers.find((a) => a.stop);
    if (stopped) {
      stop = stopped.stop;
      break;
    }
    if (answers.some((a) => a.retryable)) {
      stop = answers.some((a) => a.code === 'abuse_throttled') ? EV_THROTTLED : EV_SLOW_DOWN;
      break;
    }
    const wait = 1000 - (Date.now() - batchStarted);
    if (next < plan.items.length && wait > 0) Utilities.sleep(wait);
  }
  return evSummary(counts, next, plan.items.length - next, plan.skipped, copied, stop);
}

/**
 * Which rows of the batch still hold their address under the same headers: { <row>: true, …, changed }. When the
 * header row moved, no row is in place.
 */
function evRowsStillInPlace(sheet, plan, batch) {
  const out = { changed: false };
  const headers = sheet.getRange(1, 1, 1, Math.max(plan.emailColumn, plan.resultColumn)).getValues()[0];
  if (
    String(headers[plan.emailColumn - 1]).trim() !== plan.emailHeader ||
    headers[plan.resultColumn - 1] !== EV_RESULT_HEADERS[0]
  ) {
    out.changed = true;
    return out;
  }
  let first = Infinity;
  let last = 0;
  for (const item of batch) {
    for (const row of item.rows) {
      first = Math.min(first, row);
      last = Math.max(last, row);
    }
  }
  const values = sheet.getRange(first, plan.emailColumn, last - first + 1, 1).getValues();
  for (const item of batch) {
    for (const row of item.rows) {
      if (evAddressKey(values[row - first][0]) === item.key) out[row] = true;
      else out.changed = true;
    }
  }
  return out;
}

/** Sends one batch in parallel; 429 and 503 answers (never charged) are sent again after Retry-After. */
function evFetchBatch(batch, key, host) {
  const answers = new Array(batch.length);
  let todo = batch.map((_item, i) => i);
  for (let attempt = 0; todo.length; attempt++) {
    let responses;
    try {
      responses = UrlFetchApp.fetchAll(todo.map((i) => evRequest(host, key, batch[i].address)));
    } catch (err) {
      // a failed first attempt is the caller's to report; on a retry, keep the answers already received (the rows
      // being retried keep their 429 or 503 error and are checked again on the next run)
      if (attempt === 0) throw err;
      break;
    }
    const again = [];
    let wait = 0;
    responses.forEach((response, j) => {
      const answer = evRead(response);
      answers[todo[j]] = answer;
      if (answer.retryable && attempt < EV_MAX_RETRIES) {
        again.push(todo[j]);
        wait = Math.max(wait, answer.retryAfter === null ? evBackoff(attempt) : answer.retryAfter);
      }
    });
    // an answer that stops the run (a refused key, no credits) ends the batch: retrying the rest would only repeat it
    const stops = todo.some((i) => answers[i].stop);
    if (!again.length || stops || wait > EV_MAX_RETRY_WAIT_S) break;
    Utilities.sleep(Math.ceil(wait * 1000));
    todo = again;
  }
  return answers;
}

function evRequest(host, key, address) {
  return {
    url: `${host}/v1/validate`,
    method: 'post',
    contentType: 'application/json',
    headers: { Authorization: `Bearer ${key}` },
    payload: JSON.stringify({ email: address }),
    muteHttpExceptions: true,
    followRedirects: false,
  };
}

/** One answer → the four result cells, and whether it may be retried or stops the run. */
function evRead(response) {
  const http = response.getResponseCode();
  let body = null;
  try {
    body = JSON.parse(response.getContentText());
  } catch (_err) {
    body = null;
  }
  if (http >= 200 && http < 300) {
    if (body && typeof body.status === 'string') {
      return {
        cells: [body.status, body.reason, body.action, body.did_you_mean].map(evCell),
        code: '',
        retryable: false,
        retryAfter: null,
        stop: '',
      };
    }
    return evError('unexpected_response', http, response);
  }
  const code = body && body.error && typeof body.error.code === 'string' ? body.error.code : 'unexpected_response';
  return evError(code, http, response);
}

function evError(code, http, response) {
  const retryable = (http === 429 || http === 503) && code !== 'key_daily_cap';
  return {
    cells: ['error', evCell(code), '', ''],
    code: code,
    retryable: retryable,
    retryAfter: retryable ? evRetryAfter(response.getHeaders()) : null,
    stop: Object.prototype.hasOwnProperty.call(EV_STOP_MESSAGES, code) ? EV_STOP_MESSAGES[code] : '',
  };
}

/** Retry-After in whole seconds (digits or an HTTP date), or null. */
function evRetryAfter(headers) {
  const name = Object.keys(headers || {}).find((h) => h.toLowerCase() === 'retry-after');
  if (!name) return null;
  const value = String(headers[name]).trim();
  if (/^\d+$/.test(value)) return Number(value);
  const date = Date.parse(value);
  return Number.isNaN(date) ? null : Math.max(0, Math.ceil((date - Date.now()) / 1000));
}

/** Seconds to wait before attempt + 1 when there is no Retry-After: 0.25–0.5 s, then 0.5–1 s, 1–2 s. */
function evBackoff(attempt) {
  const base = Math.min(8, 0.5 * 2 ** attempt);
  return base / 2 + (Math.random() * base) / 2;
}

/** A value as cell text that Sheets never reads as a formula. */
function evCell(value) {
  const text = value === null || value === undefined ? '' : String(value);
  return /^[=+\-@]/.test(text) ? `'${text}` : text;
}

/** Writes [row, cells] pairs, one call per run of consecutive rows. */
function evWrite(sheet, column, cells) {
  cells.sort((a, b) => a[0] - b[0]);
  let start = 0;
  for (let i = 1; i <= cells.length; i++) {
    if (i < cells.length && cells[i][0] === cells[i - 1][0] + 1) continue;
    sheet
      .getRange(cells[start][0], column, i - start, EV_RESULT_HEADERS.length)
      .setValues(cells.slice(start, i).map((c) => c[1]));
    start = i;
  }
}

function evSummary(counts, checked, left, skipped, copied, stop) {
  const known = EV_STATUS_ORDER.filter((s) => counts[s]);
  const other = Object.keys(counts).filter((s) => EV_STATUS_ORDER.indexOf(s) < 0);
  const parts = known.concat(other).map((s) => `${s} ${counts[s]}`);
  const lines = [
    `Checked ${checked} ${checked === 1 ? 'address' : 'addresses'}${parts.length ? ` (${parts.join(', ')})` : ''}.`,
  ];
  if (counts.error) lines.push('Rows marked error are checked again on the next run.');
  if (skipped)
    lines.push(
      `${skipped} ${skipped === 1 ? 'row' : 'rows'} already had a status and ${skipped === 1 ? 'was' : 'were'} skipped.`,
    );
  if (copied)
    lines.push(
      `${copied} ${copied === 1 ? 'row' : 'rows'} took the result another row already had for the same address (no new check).`,
    );
  if (stop) lines.push(stop);
  if (left) lines.push(`${left} ${left === 1 ? 'address is' : 'addresses are'} left: run again to continue.`);
  return lines.join('\n');
}

<!doctype html>
<html lang="en">
  <head>
    <base target="_top" />
    <meta charset="utf-8" />
    <style>
      body { font: 13px/1.45 Arial, sans-serif; color: #202124; margin: 12px; }
      h2 { font-size: 13px; margin: 18px 0 6px; }
      h2:first-child { margin-top: 0; }
      label { display: block; margin: 4px 0; }
      input[type='password'] { box-sizing: border-box; width: 100%; padding: 6px; font: inherit; }
      button { font: inherit; padding: 6px 10px; margin: 4px 4px 0 0; cursor: pointer; }
      button.primary { background: #1a73e8; border: 1px solid #1a73e8; color: #fff; border-radius: 4px; }
      button.plain { background: #fff; border: 1px solid #dadce0; color: #1a73e8; border-radius: 4px; }
      button:disabled { opacity: 0.5; cursor: default; }
      .note { color: #5f6368; font-size: 12px; }
      #message { white-space: pre-line; margin-top: 12px; padding: 8px; background: #f1f3f4; border-radius: 4px; }
      #message:empty { display: none; }
      [hidden] { display: none; }
    </style>
  </head>
  <body>
    <h2>API key</h2>
    <p id="key-state" class="note">Loading…</p>
    <input id="key" type="password" autocomplete="off" spellcheck="false" placeholder="ev_live_…" aria-label="API key" />
    <p class="note">
      Kept in your own settings for this add-on (per person, never in the sheet). Create a key in the dashboard; for
      lists of more than 50 addresses it needs the bulk scope.
    </p>

    <h2>Region</h2>
    <label><input type="radio" name="region" value="us" checked /> US (api.email-validation.org)</label>
    <label><input type="radio" name="region" value="eu" /> EU (api-eu.email-validation.org, EU data residency)</label>
    <h2>In cells</h2>
    <label
      ><input id="cell-function" type="checkbox" /> Allow =EV_VALIDATE in this spreadsheet: every editor’s formulas here
      use my API key and credits.</label
    >
    <p class="note">Off by default, and only for this spreadsheet. Validate rows below always uses your own key.</p>
    <button id="save" class="primary" type="button">Save</button>
    <button id="remove" class="plain" type="button">Remove key</button>

    <h2>Validate</h2>
    <p class="note">
      Fills Validation status, reason, action and Did you mean next to the column headed Email. Up to 50 addresses to
      check are sent one by one; more go as one bulk list (you are asked first). In a cell: =EV_VALIDATE(A2).
    </p>
    <button class="primary" type="button" data-run="all">Validate all rows</button>
    <button class="plain" type="button" data-run="selection">Validate selected rows</button>
    <button class="plain" type="button" id="test">Add test addresses</button>
    <button class="plain" type="button" id="cancel" hidden>Cancel the waiting list</button>

    <h2>Cost</h2>
    <p class="note">
      1 credit = 1 validated address. An address that appears in several rows is checked once, and a row that already
      has a status is skipped. Free: every result with status unknown, API errors, malformed addresses rejected before
      any check and test addresses on the sandbox domain.
    </p>

    <div id="message" role="status" aria-live="polite"></div>

    <script>
      (() => {
        var message = document.getElementById('message');
        var buttons = Array.prototype.slice.call(document.querySelectorAll('button'));
        function busy(on, text) {
          buttons.forEach((b) => { b.disabled = on; });
          if (text) message.textContent = text;
        }
        function show(settings) {
          document.getElementById('key-state').textContent = settings.hasKey
            ? 'A key is saved. Paste another one to replace it.'
            : 'No key saved yet.';
          var radio = document.querySelector(`input[name=region][value=${settings.region}]`);
          if (radio) radio.checked = true;
          document.getElementById('cell-function').checked = Boolean(settings.cellFunction);
          document.getElementById('cancel').hidden = !settings.pending;
        }
        function refresh() {
          google.script.run.withSuccessHandler(show).evSettings();
        }
        function call(name, arg, text) {
          busy(true, text);
          google.script.run
            .withSuccessHandler((result) => {
              busy(false);
              message.textContent = typeof result === 'string' ? result : result.message;
              refresh();
            })
            .withFailureHandler(() => {
              busy(false);
              message.textContent = 'Something went wrong. Try again.';
            })
            [name](arg);
        }
        document.getElementById('save').addEventListener('click', () => {
          var key = document.getElementById('key');
          var region = document.querySelector('input[name=region]:checked').value;
          var value = key.value;
          key.value = '';
          var cellFunction = document.getElementById('cell-function').checked;
          call('evSaveSettings', { key: value, region: region, cellFunction: cellFunction }, 'Saving…');
        });
        document.getElementById('remove').addEventListener('click', () => {
          call('evRemoveKey', null, 'Removing…');
        });
        document.getElementById('test').addEventListener('click', () => {
          call('evSidebarTestAddresses', null, 'Adding…');
        });
        document.getElementById('cancel').addEventListener('click', () => {
          call('evCancelPending', null, 'Cancelling…');
        });
        Array.prototype.forEach.call(document.querySelectorAll('[data-run]'), (b) => {
          b.addEventListener('click', () => {
            call('evSidebarValidate', b.getAttribute('data-run') === 'selection', 'Checking… (a long list can take a few minutes)');
          });
        });
        refresh();
      })();
    </script>
  </body>
</html>

{
  "timeZone": "Etc/UTC",
  "runtimeVersion": "V8",
  "exceptionLogging": "NONE",
  "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://api.email-validation.org/", "https://api-eu.email-validation.org/"]
}