Google Sheets phone number checker

/**
 * MobileValidate for Google Sheets — version 1.0.0
 * https://mobilevalidate.com/integrations/google-sheets
 *
 * Paste this file into Extensions > Apps Script (replace the default Code.gs), save, reload the spreadsheet and use
 * the "MobileValidate" menu. Your API key is stored in your own Apps Script user properties
 * (PropertiesService.getUserProperties), never in a cell, so people you share the sheet with can't read it.
 * Note: Google runs custom functions with the spreadsheet OWNER's user properties, so =MV_LOOKUP in a shared sheet
 * uses (and bills) the owner's key. The menu commands always use the key of the person who runs them.
 *
 * Menu:
 *   - Set API key / Remove API key
 *   - Check selected numbers: real-time lookups for the selected cells (up to MAX_SELECTION numbers), results in the
 *     columns to the right. Shows the maximum cost and asks before anything runs.
 *   - Start bulk job for this column: the whole column (row 1 = header) as one bulk job at the bulk price. Shows the
 *     free estimate first, then writes the results next to each number by row.
 *   - Fetch results of the last bulk job: for jobs that took longer than one script run (6 minutes).
 * Custom function:
 *   =MV_LOOKUP(A2, "whatsapp")              one number, or a range such as A2:A20 (one request per 100 numbers)
 *   =MV_LOOKUP(A2, "carrier", "line_type")  one attribute of a data check
 *
 * Results use the same column names as the API's NDJSON/CSV download: <check>.status, <check>.registered,
 * <check>.<attribute>. Inconclusive answers (unknown, unsupported country, invalid, duplicate) are never charged.
 * Licensed MIT. Independent service; not affiliated with Google or the platforms named.
 */

var MV_VERSION = "1.0.0";
var MV_API = "https://api.mobilevalidate.com/v1";
/** Most numbers "Check selected numbers" sends (real-time price; larger lists belong in a bulk job). */
var MAX_SELECTION = 500;
/** Most identifiers per bulk job (API limit). */
var MAX_JOB_ROWS = 50000;
/** Seconds a menu command may spend waiting for answers (Apps Script stops a run after 6 minutes). */
var WAIT_BUDGET_S = 270;
/** MV_LOOKUP result cache (seconds; CacheService allows at most 21600). */
var CELL_CACHE_S = 21600;
var KEY_PROP = "MV_API_KEY";
var CHECKS_PROP = "MV_LAST_CHECKS";
var COUNTRY_PROP = "MV_DEFAULT_COUNTRY";
var JOB_PROP = "MV_LAST_JOB";
/** Short names accepted for checks (the API accepts the same aliases). */
var MV_ALIASES = {
  whatsapp: "whatsapp.registered", telegram: "telegram.registered", viber: "viber.registered", signal: "signal.registered",
  imessage: "imessage.registered", rcs: "rcs.registered", line: "line.registered", zalo: "zalo.registered",
  botim: "botim.registered", messenger: "messenger.registered", facebook: "facebook.registered",
  instagram: "instagram.registered", threads: "threads.registered", x: "x.registered", twitter: "x.registered",
  tiktok: "tiktok.registered", snapchat: "snapchat.registered", linkedin: "linkedin.registered",
  carrier: "network.carrier", spam: "number.spam", hlr: "number.hlr", mnp: "number.mnp", porting: "number.mnp"
};
/** The attribute MV_LOOKUP shows for a data check when no field is given. */
var MV_MAIN_ATTRIBUTE = { "network.carrier": "carrier", "network.carrier_us": "carrier", "number.hlr": "status", "number.mnp": "porting", "number.spam": "risk_level" };

// ── Pure helpers (no Google services; unit-tested in Node) ────────────────────────────────────────────────────────────

/** mv_live_ / mv_test_ followed by 36 letters or digits. */
function isValidApiKey(key) {
  return typeof key === "string" && /^mv_(live|test)_[A-Za-z0-9]{36}$/.test(key.trim());
}

/** "mv_live_ab…" → "mv_live_…7f3a" (shown in dialogs; never the whole key). */
function maskKey(key) {
  var k = String(key || "").trim();
  return k.length > 12 ? k.slice(0, 8) + "…" + k.slice(-4) : "…";
}

/** ISO 3166-1 alpha-2 or empty. */
function normalizeCountry(v) {
  var c = String(v == null ? "" : v).trim().toUpperCase();
  return /^[A-Z]{2}$/.test(c) ? c : "";
}

/**
 * A cell value → the string sent to the API, or null for an empty cell. Sheets turns "+447700900001" typed without a
 * leading apostrophe into the number 447700900001: digits-only values get a "+" back unless a default country is set
 * (then they are national numbers such as 07700 900001 with the 0 lost, which the API reads with default_country).
 */
function cellToNumber(v, defaultCountry) {
  if (v === null || v === undefined || v === "") return null;
  var s;
  if (typeof v === "number") {
    if (!isFinite(v) || v < 0) return null;
    s = v.toFixed(0);
  } else s = String(v).trim();
  if (!s) return null;
  s = s.replace(/^'+/, "");
  if (/^\d{8,15}$/.test(s) && !defaultCountry) return "+" + s;
  return s;
}

/** Does a cell look like a column title rather than a phone number? (fewer than 5 digits) */
function isHeaderLike(v) {
  if (v === null || v === undefined || v === "") return false;
  if (typeof v === "number") return false;
  return (String(v).match(/\d/g) || []).length < 5;
}

function chunk(arr, n) {
  var out = [];
  for (var i = 0; i < arr.length; i += n) out.push(arr.slice(i, i + n));
  return out;
}

/** Decimal USD string ("0.004") → integer micro-USD (4000). Money is never handled as a float. */
function decimalToMicro(s) {
  var m = /^(\d+)(?:\.(\d{1,6})\d*)?$/.exec(String(s == null ? "" : s).trim());
  if (!m) return 0;
  return parseInt(m[1], 10) * 1000000 + parseInt(((m[2] || "") + "000000").slice(0, 6), 10);
}

/** Integer micro-USD → "$0.004" (at least two decimals, trailing zeros trimmed beyond that). */
function formatUsd(micro) {
  var n = Math.max(0, Math.round(Number(micro) || 0));
  var whole = Math.floor(n / 1000000), frac = String(n % 1000000);
  while (frac.length < 6) frac = "0" + frac;
  frac = frac.replace(/0+$/, "");
  while (frac.length < 2) frac += "0";
  return "$" + whole + "." + frac;
}

/**
 * "whatsapp, carrier,hlr" → { codes: [...], unknown: [...] } against the service list (GET /v1/services data).
 * Accepts service codes, the short aliases and "<name>" for "<name>.registered". Duplicates are dropped.
 * `mode` "realtime" drops bulk-only services into `bulkOnly`.
 */
function parseChecks(input, services, mode) {
  var byCode = {};
  (services || []).forEach(function (s) { byCode[s.code] = s; });
  var codes = [], unknown = [], bulkOnly = [], notPhone = [];
  String(input || "").split(/[\s,;]+/).forEach(function (raw) {
    var w = raw.trim().toLowerCase();
    if (!w) return;
    var code = byCode[w] ? w : MV_ALIASES[w] && byCode[MV_ALIASES[w]] ? MV_ALIASES[w] : byCode[w + ".registered"] ? w + ".registered" : null;
    if (!code) { unknown.push(raw.trim()); return; }
    var s = byCode[code];
    if (s.input_type && s.input_type !== "phone") { notPhone.push(raw.trim()); return; }
    if (mode === "realtime" && s.realtime === false) { bulkOnly.push(code); return; }
    if (codes.indexOf(code) < 0) codes.push(code);
  });
  return { codes: codes, unknown: unknown, bulkOnly: bulkOnly, notPhone: notPhone };
}

/** Attribute names of a service (GET /v1/services gives objects with `key`; plain strings are accepted too). */
function attributeKeys(s) {
  return ((s && s.attributes) || []).map(function (a) { return typeof a === "string" ? a : a && a.key; }).filter(function (k) { return !!k; });
}

/** "network.carrier" → "carrier", "whatsapp.registered" → "whatsapp" (the short name people type). */
function shortName(code) {
  for (var k in MV_ALIASES) if (MV_ALIASES[k] === code) return k;
  return code.replace(/\.registered$/, "");
}

/** Result columns for the chosen checks, in the API's download naming. */
function columnsFor(codes, services) {
  var byCode = {};
  (services || []).forEach(function (s) { byCode[s.code] = s; });
  var cols = ["e164", "number_status"];
  codes.forEach(function (c) {
    var s = byCode[c] || {};
    var add = function (k) { if (cols.indexOf(k) < 0) cols.push(k); };
    add(c + ".status");
    if (s.result_kind === "boolean" || !s.result_kind) add(c + ".registered");
    attributeKeys(s).forEach(function (a) { add(c + "." + a); });
  });
  cols.push("checked_at");
  return cols;
}

/** One result item of POST /v1/lookup → the flat shape of the NDJSON download (same keys). */
function flattenLookupItem(item) {
  var flat = { e164: item.e164 == null ? null : item.e164, number_status: item.number_status == null ? null : item.number_status, checked_at: null };
  var checks = item.checks || {};
  Object.keys(checks).forEach(function (code) {
    var r = checks[code] || {};
    flat[code + ".status"] = r.status == null ? null : r.status;
    flat[code + ".registered"] = r.registered === undefined ? null : r.registered;
    if (r.business !== undefined) flat[code + ".business"] = r.business;
    var attrs = r.attributes || {};
    Object.keys(attrs).forEach(function (a) { flat[code + "." + a] = attrs[a]; });
    if (!flat.checked_at && r.checked_at) flat.checked_at = r.checked_at;
  });
  return flat;
}

/** A flat row → cell values for `cols` (null/undefined → empty cell; objects as JSON). */
function rowValues(flat, cols) {
  return cols.map(function (c) {
    var v = flat ? flat[c] : null;
    if (v === null || v === undefined) return "";
    if (typeof v === "object") return JSON.stringify(v);
    return v;
  });
}

/** NDJSON body → array of objects (blank lines ignored). */
function parseNdjson(text) {
  return String(text || "").split(/\r?\n/).filter(function (l) { return l.trim(); }).map(function (l) { return JSON.parse(l); });
}

/** Sheet rows [2,3,4,7,8] → "2-4,7-8" (stored between runs; compact for long columns). */
function encodeRuns(rows) {
  var out = [], i = 0;
  while (i < rows.length) {
    var start = rows[i], end = start;
    while (i + 1 < rows.length && rows[i + 1] === end + 1) { i++; end++; }
    out.push(start === end ? String(start) : start + "-" + end);
    i++;
  }
  return out.join(",");
}

function decodeRuns(s) {
  var out = [];
  String(s || "").split(",").forEach(function (part) {
    if (!part) return;
    var m = /^(\d+)(?:-(\d+))?$/.exec(part);
    if (!m) return;
    var a = parseInt(m[1], 10), b = m[2] ? parseInt(m[2], 10) : a;
    for (var r = a; r <= b; r++) out.push(r);
  });
  return out;
}

/** Download rows (with row_no, 1-based, input order) → { sheetRow: flat } using the sheet rows the numbers came from. */
function mapRowsByRowNo(rows, sheetRows) {
  var out = {};
  rows.forEach(function (r) {
    var sr = sheetRows[Number(r.row_no) - 1];
    if (sr !== undefined) out[sr] = r;
  });
  return out;
}

/** Maximum cost of real-time lookups: numbers × each check's real-time price (integer micro-USD). */
function estimateSelection(count, codes, services) {
  var byCode = {};
  (services || []).forEach(function (s) { byCode[s.code] = s; });
  var lines = codes.map(function (c) {
    var p = byCode[c] && byCode[c].prices && byCode[c].prices.realtime ? decimalToMicro(byCode[c].prices.realtime.amount) : 0;
    return { check: c, unitMicro: p, maxMicro: p * count };
  });
  return { count: count, lines: lines, maxMicro: lines.reduce(function (a, l) { return a + l.maxMicro; }, 0) };
}

/** Numbers per lookup request: at most 100, and numbers × checks at most 2,000. */
function lookupBatchSize(checkCount) {
  return Math.max(1, Math.min(100, Math.floor(2000 / Math.max(1, checkCount))));
}

/** Human text of a job estimate (POST /v1/jobs/estimate) for the confirmation dialog. */
function describeEstimate(est) {
  var lines = [
    "Rows: " + est.total + " (valid " + est.valid + ", invalid " + est.invalid + ", duplicates " + est.duplicate + ")",
    "Checks: " + (est.checks || []).join(", "),
    "Maximum cost: " + formatUsd(decimalToMicro(est.max_cost && est.max_cost.amount)) + " (reserved now; inconclusive answers are released and free)"
  ];
  (est.breakdown || []).forEach(function (b) {
    if (b.reason === "small_batch") lines.push("• " + b.check + ": " + b.checks + " numbers in small country groups at the real-time price (" + (b.countries || []).join(", ") + ")");
    if (b.reason === "country_minimum") lines.push("• " + b.check + ": minimum charge per country applies to " + (b.countries || []).join(", ") + " (" + formatUsd(decimalToMicro(b.minimum_charge && b.minimum_charge.amount)) + " each, only if a country returns an answer)");
  });
  (est.unsupported_checks || []).forEach(function (u) { lines.push("• " + u.check + " does not cover " + (u.countries || []).join(", ") + " (" + u.checks + " numbers, free)"); });
  return lines.join("\n");
}

/** API error body → one readable line. */
function errorText(status, body) {
  var e = body && body.error;
  if (!e) return "HTTP " + status;
  return e.code + ": " + e.message + (e.suggestion ? " " + e.suggestion : "");
}

/** One check result → the text MV_LOOKUP puts in the cell. `field` picks one attribute. */
function cellAnswer(result, field) {
  if (!result) return "";
  if (field) {
    var a = result.attributes || {};
    if (field === "status" && !(field in a)) return result.status || "";
    if (field === "registered") return result.registered === true ? "registered" : result.registered === false ? "not registered" : "unknown";
    var v = a[field];
    return v === null || v === undefined ? "" : typeof v === "object" ? JSON.stringify(v) : v;
  }
  if (result.status !== "completed") return result.status === "pending" ? "pending (recalculate later)" : result.status + (result.reason && String(result.reason).toLowerCase() !== result.status ? " (" + result.reason + ")" : "");
  var main = MV_MAIN_ATTRIBUTE[result.service];
  if (main) {
    var attrs = result.attributes || {};
    var mv = attrs[main];
    if (result.service.indexOf("network.carrier") === 0 && attrs.line_type) return [attrs.line_type, mv].filter(function (x) { return x; }).join(" · ");
    return mv === null || mv === undefined ? "no data" : String(mv);
  }
  if (result.business === true || (result.attributes && result.attributes.business === true)) return "registered (business)";
  return result.registered === true ? "registered" : result.registered === false ? "not registered" : "unknown";
}

/** Cache key of one MV_LOOKUP answer (≤ 250 characters). */
function cellCacheKey(number, check, field) {
  return ("mv1|" + check + "|" + (field || "") + "|" + number).slice(0, 250);
}

// ── HTTP ──────────────────────────────────────────────────────────────────────────────────────────────────────────────

function getApiKey_() {
  return PropertiesService.getUserProperties().getProperty(KEY_PROP) || "";
}

/** One API call. Retries rate limits and retryable errors (Retry-After) up to 3 times. Throws on errors. */
function api_(method, path, body, opts) {
  var key = (opts && opts.key) || getApiKey_();
  if (!key) throw new Error("No API key set. Use MobileValidate > Set API key (a free test key works: mobilevalidate.com/get-test-key).");
  var params = {
    method: method, muteHttpExceptions: true, followRedirects: false,
    headers: { Authorization: "Bearer " + key, "X-MV-Client": "google-sheets/" + MV_VERSION }
  };
  if (opts && opts.idempotencyKey) params.headers["Idempotency-Key"] = opts.idempotencyKey;
  if (body !== undefined && body !== null) { params.contentType = "application/json"; params.payload = JSON.stringify(body); }
  var url = path.indexOf("https://") === 0 ? path : MV_API + path.replace(/^\/v1/, "");
  for (var attempt = 0; ; attempt++) {
    var res = UrlFetchApp.fetch(url, params);
    var status = res.getResponseCode(), text = res.getContentText();
    if (status >= 200 && status < 300) return opts && opts.raw ? text : { status: status, body: text ? JSON.parse(text) : null };
    var parsed = null;
    try { parsed = JSON.parse(text); } catch (e) { parsed = null; }
    var retryable = status === 429 || status >= 500 || (parsed && parsed.error && parsed.error.retryable === true);
    if (retryable && attempt < 3 && !(opts && opts.noRetry)) {
      var h = res.getHeaders() || {};
      var ra = Number(h["Retry-After"] || h["retry-after"] || 0);
      Utilities.sleep(Math.min(20, Math.max(1, ra || Math.pow(2, attempt))) * 1000);
      continue;
    }
    var err = new Error(errorText(status, parsed));
    err.status = status; err.code = parsed && parsed.error ? parsed.error.code : null;
    throw err;
  }
}

/** GET /v1/services, cached for an hour. */
function services_() {
  var cache = CacheService.getScriptCache();
  var hit = cache.get("mv_services_v1");
  if (hit) return JSON.parse(hit);
  var data = api_("get", "/services").body.data || [];
  var slim = data.map(function (s) { return { code: s.code, name: s.name, input_type: s.input_type, result_kind: s.result_kind, attributes: attributeKeys(s), realtime: s.realtime, status: s.status, prices: s.prices }; })
    .filter(function (s) { return s.status === "active"; });
  try { cache.put("mv_services_v1", JSON.stringify(slim), 3600); } catch (e) { /* too large to cache: fine */ }
  return slim;
}

/** POST /v1/lookup for up to 100 numbers; polls a pending lookup until done or `deadline` (ms). Returns result items. */
function lookupBatch_(numbers, codes, country, deadline) {
  var body = { numbers: numbers, checks: codes, wait: 25 };
  if (country) body.default_country = country;
  var res = api_("post", "/lookup", body);
  var lk = res.body;
  while (lk.status === "pending" && lk.next && lk.next.poll_url && Date.now() < deadline) {
    lk = api_("get", lk.next.poll_url + (lk.next.poll_url.indexOf("?") < 0 ? "?" : "&") + "wait=25").body;
  }
  return lk.results || [];
}

// ── Menu ──────────────────────────────────────────────────────────────────────────────────────────────────────────────

function onOpen() {
  SpreadsheetApp.getUi().createMenu("MobileValidate")
    .addItem("Check selected numbers…", "mvCheckSelection")
    .addItem("Start bulk job for this column…", "mvBulkColumn")
    .addItem("Fetch results of the last bulk job", "mvFetchLastJob")
    .addSeparator()
    .addItem("Set API key…", "mvSetApiKey")
    .addItem("Set default country…", "mvSetDefaultCountry")
    .addItem("Remove API key", "mvRemoveApiKey")
    .addItem("Help", "mvHelp")
    .addToUi();
}

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

function mvSetApiKey() {
  var ui = SpreadsheetApp.getUi();
  var cur = getApiKey_();
  var r = ui.prompt("MobileValidate API key",
    (cur ? "Current key: " + maskKey(cur) + "\n\n" : "") +
    "Paste a key from app.mobilevalidate.com › API keys (mv_live_…), or a free test key (mv_test_…) from mobilevalidate.com/get-test-key.\n" +
    "It is saved in your own Apps Script user properties, not in the spreadsheet.", ui.ButtonSet.OK_CANCEL);
  if (r.getSelectedButton() !== ui.Button.OK) return;
  var key = r.getResponseText().trim();
  if (!isValidApiKey(key)) { ui.alert("That doesn't look like a MobileValidate key (mv_live_… or mv_test_…, 44 characters)."); return; }
  try {
    var acct = api_("get", "/account", null, { key: key, noRetry: true }).body;
    PropertiesService.getUserProperties().setProperty(KEY_PROP, key);
    ui.alert("Key saved (" + maskKey(key) + ").\n" + (acct.livemode ? "Live key. Balance: " + formatUsd(decimalToMicro(acct.balance && acct.balance.amount)) : "Test key: answers only the documented test numbers, never billed."));
  } catch (e) {
    ui.alert("The key was not saved: " + e.message);
  }
}

function mvRemoveApiKey() {
  PropertiesService.getUserProperties().deleteProperty(KEY_PROP);
  SpreadsheetApp.getUi().alert("API key removed from your user properties.");
}

function mvSetDefaultCountry() {
  var ui = SpreadsheetApp.getUi();
  var props = PropertiesService.getUserProperties();
  var r = ui.prompt("Default country", "Two-letter country code (e.g. GB, US) for numbers written without +country code. Leave empty for none.\nCurrent: " + (props.getProperty(COUNTRY_PROP) || "none"), ui.ButtonSet.OK_CANCEL);
  if (r.getSelectedButton() !== ui.Button.OK) return;
  var c = normalizeCountry(r.getResponseText());
  if (r.getResponseText().trim() && !c) { ui.alert("Use a two-letter code such as GB."); return; }
  if (c) props.setProperty(COUNTRY_PROP, c); else props.deleteProperty(COUNTRY_PROP);
  ui.alert(c ? "Default country: " + c : "No default country: numbers need a + and country code.");
}

/** Ask for the checks (remembered per user). Returns codes or null. */
function askChecks_(ui, services, mode) {
  var props = PropertiesService.getUserProperties();
  var last = props.getProperty(CHECKS_PROP) || "whatsapp, carrier";
  var avail = services.filter(function (s) { return s.input_type === "phone" && (mode !== "realtime" || s.realtime !== false); })
    .map(function (s) { return shortName(s.code); });
  var r = ui.prompt("Which checks?", "Comma-separated, for example: whatsapp, carrier, hlr\nLast used: " + last + "\n\nAvailable" + (mode === "realtime" ? " in real time" : "") + ": " + avail.join(", "), ui.ButtonSet.OK_CANCEL);
  if (r.getSelectedButton() !== ui.Button.OK) return null;
  var p = parseChecks(r.getResponseText().trim() || last, services, mode);
  var problems = [];
  if (p.unknown.length) problems.push("Unknown: " + p.unknown.join(", "));
  if (p.notPhone.length) problems.push("Not a phone check: " + p.notPhone.join(", "));
  if (p.bulkOnly.length) problems.push("Bulk only (use Start bulk job): " + p.bulkOnly.join(", "));
  if (problems.length || !p.codes.length) { ui.alert((problems.join("\n") || "Choose at least one check.")); return null; }
  props.setProperty(CHECKS_PROP, p.codes.map(shortName).join(", "));
  return p.codes;
}

/** First output column right of `afterCol` whose block (rows) is empty; else after the sheet's last column. */
function outputColumn_(sheet, firstRow, numRows, afterCol, width) {
  var block = sheet.getRange(firstRow, afterCol + 1, numRows, width).getValues();
  var empty = block.every(function (row) { return row.every(function (v) { return v === "" || v === null; }); });
  return empty ? afterCol + 1 : Math.max(sheet.getLastColumn(), afterCol) + 1;
}

function mvCheckSelection() {
  var ui = SpreadsheetApp.getUi();
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getActiveRange();
  if (!range) { ui.alert("Select the cells with the phone numbers first."); return; }
  var country = PropertiesService.getUserProperties().getProperty(COUNTRY_PROP) || "";
  var col = range.getColumn() + range.getNumColumns() - 1;
  var first = range.getRow();
  var values = range.getValues().map(function (r) { return r[0]; }); // first column of the selection
  var headerInside = isHeaderLike(values[0]);
  var items = [];
  values.forEach(function (v, i) {
    if (i === 0 && headerInside) return;
    var n = cellToNumber(v, country);
    if (n) items.push({ row: first + i, number: n });
  });
  if (!items.length) { ui.alert("No phone numbers in the selection."); return; }
  if (items.length > MAX_SELECTION) { ui.alert(items.length + " numbers selected. Up to " + MAX_SELECTION + " run here at the real-time price; use \"Start bulk job for this column\" for larger lists (bulk price)."); return; }
  var services = services_();
  var codes = askChecks_(ui, services, "realtime");
  if (!codes) return;
  var est = estimateSelection(items.length, codes, services);
  var acct = api_("get", "/account").body;
  var msg = items.length + " numbers × " + codes.length + " checks.\nMaximum cost: " + formatUsd(est.maxMicro) + " at the real-time price" +
    (acct.livemode ? " (balance " + formatUsd(decimalToMicro(acct.balance && acct.balance.amount)) + ")" : " (test key: never billed)") +
    ".\nYou pay only for conclusive answers; cached answers are free.\n\nRun the checks?";
  if (ui.alert("Check selected numbers", msg, ui.ButtonSet.YES_NO) !== ui.Button.YES) return;

  var cols = columnsFor(codes, services);
  var headerRow = headerInside ? first : first > 1 ? first - 1 : null;
  var outCol = outputColumn_(sheet, first, values.length, col, cols.length);
  var deadline = Date.now() + WAIT_BUDGET_S * 1000;
  var done = 0;
  chunk(items, lookupBatchSize(codes.length)).forEach(function (batch) {
    if (Date.now() > deadline) return;
    var results = lookupBatch_(batch.map(function (x) { return x.number; }), codes, country, deadline);
    batch.forEach(function (x, i) {
      var cell = sheet.getRange(x.row, outCol, 1, cols.length);
      cell.setNumberFormat("@");
      cell.setValues([rowValues(results[i] ? flattenLookupItem(results[i]) : null, cols)]);
    });
    done += batch.length;
    SpreadsheetApp.flush();
  });
  if (headerRow) {
    var h = sheet.getRange(headerRow, outCol, 1, cols.length);
    var existing = h.getValues()[0];
    if (headerInside || existing.every(function (v) { return v === ""; })) h.setValues([cols]).setFontWeight("bold");
  }
  ui.alert(done === items.length ? "Done: " + done + " numbers checked." : "Stopped after " + done + " of " + items.length + " numbers (time limit). Select the rest and run again.");
}

function mvBulkColumn() {
  var ui = SpreadsheetApp.getUi();
  var sheet = SpreadsheetApp.getActiveSheet();
  var cell = sheet.getActiveCell();
  var col = cell.getColumn();
  var last = sheet.getLastRow();
  if (last < 2) { ui.alert("Put the numbers in this column from row 2 (row 1 is the header)."); return; }
  var country = PropertiesService.getUserProperties().getProperty(COUNTRY_PROP) || "";
  var values = sheet.getRange(2, col, last - 1, 1).getValues();
  var numbers = [], rows = [];
  values.forEach(function (r, i) {
    var n = cellToNumber(r[0], country);
    if (n) { numbers.push(n); rows.push(i + 2); }
  });
  if (!numbers.length) { ui.alert("No phone numbers in this column (from row 2)."); return; }
  if (numbers.length > MAX_JOB_ROWS) { ui.alert("A job takes up to " + MAX_JOB_ROWS + " numbers; this column has " + numbers.length + "."); return; }
  var services = services_();
  var codes = askChecks_(ui, services, "bulk");
  if (!codes) return;
  var body = { numbers: numbers, checks: codes };
  if (country) body.default_country = country;
  var est = api_("post", "/jobs/estimate", body).body;
  if (ui.alert("Start bulk job?", describeEstimate(est) + "\n\nThe estimate is free. Start the job?", ui.ButtonSet.YES_NO) !== ui.Button.YES) return;
  body.max_cost = est.max_cost;
  var job = api_("post", "/jobs", body, { idempotencyKey: "sheets-" + Utilities.getUuid() }).body;
  saveJob_({ id: job.id, sheet: sheet.getSheetId(), col: col, checks: codes, rows: encodeRuns(rows) });
  finishJob_(job.id);
}

function mvFetchLastJob() {
  var st = loadJob_();
  if (!st) { SpreadsheetApp.getUi().alert("No bulk job started from this spreadsheet yet."); return; }
  finishJob_(st.id);
}

/** Wait for the job (within the time budget), then write its results next to the numbers by row_no. */
function finishJob_(id) {
  var ui = SpreadsheetApp.getUi();
  var st = loadJob_();
  var deadline = Date.now() + WAIT_BUDGET_S * 1000;
  var job = api_("get", "/jobs/" + id).body;
  while ((job.status === "queued" || job.status === "running" || job.status === "preflight" || job.status === "merging") && Date.now() < deadline) {
    job = api_("get", "/jobs/" + id + "?wait=25").body;
  }
  if (job.status !== "completed" && job.status !== "cancelled") {
    var p = job.progress || {};
    ui.alert("Job " + id + " is " + job.status + " (" + (p.done || 0) + " of " + (p.checks_total || "?") + " checks done). Use MobileValidate › Fetch results of the last bulk job in a few minutes.");
    return;
  }
  var sheet = null;
  SpreadsheetApp.getActiveSpreadsheet().getSheets().forEach(function (s) { if (s.getSheetId() === st.sheet) sheet = s; });
  if (!sheet) { ui.alert("The sheet of this job no longer exists."); return; }
  var rowsOut = parseNdjson(api_("get", "/jobs/" + id + "/download?format=ndjson", null, { raw: true }));
  var cols = columnsFor(st.checks, services_());
  var sheetRows = decodeRuns(st.rows);
  var byRow = mapRowsByRowNo(rowsOut, sheetRows);
  var lastRow = sheetRows[sheetRows.length - 1];
  var outCol = outputColumn_(sheet, 1, lastRow, st.col, cols.length);
  var grid = [cols];
  for (var r = 2; r <= lastRow; r++) grid.push(byRow[r] ? rowValues(byRow[r], cols) : cols.map(function () { return ""; }));
  var out = sheet.getRange(1, outCol, grid.length, cols.length);
  out.setNumberFormat("@");
  out.setValues(grid);
  sheet.getRange(1, outCol, 1, cols.length).setFontWeight("bold");
  var c = job.cost && job.cost.charged ? formatUsd(decimalToMicro(job.cost.charged.amount)) : "$0.00";
  ui.alert("Job " + id + " " + job.status + ": " + rowsOut.length + " rows written. Charged: " + c + ".");
}

/** Job state in document properties, split into ≤ 8 KB parts (a property value holds at most 9 KB). */
function saveJob_(st) {
  var props = PropertiesService.getDocumentProperties();
  var text = JSON.stringify(st), parts = {};
  var n = Math.ceil(text.length / 8000);
  for (var i = 0; i < n; i++) parts[JOB_PROP + "_" + i] = text.slice(i * 8000, (i + 1) * 8000);
  parts[JOB_PROP] = String(n);
  props.setProperties(parts);
}

function loadJob_() {
  var props = PropertiesService.getDocumentProperties();
  var n = Number(props.getProperty(JOB_PROP) || 0);
  if (!n) return null;
  var text = "";
  for (var i = 0; i < n; i++) text += props.getProperty(JOB_PROP + "_" + i) || "";
  try { return JSON.parse(text); } catch (e) { return null; }
}

function mvHelp() {
  SpreadsheetApp.getUi().alert("MobileValidate for Google Sheets " + MV_VERSION,
    "Guide and FAQ: https://mobilevalidate.com/integrations/google-sheets\nAPI docs: https://mobilevalidate.com/docs\nSupport: support@mobilevalidate.com",
    SpreadsheetApp.getUi().ButtonSet.OK);
}

// ── Custom function ───────────────────────────────────────────────────────────────────────────────────────────────────

/**
 * Checks a phone number with MobileValidate and returns the answer (registered / not registered / unknown, or the
 * main value of a data check). Answers are cached for 6 hours. For more than a few dozen numbers use the
 * MobileValidate menu instead: every cell is a separate call and Sheets may recalculate them.
 *
 * @param {string|Array<Array<string>>} number A phone number in international format, or a range of them.
 * @param {string} check The check, for example "whatsapp", "telegram", "carrier", "hlr", "mnp".
 * @param {string} field Optional: one attribute, for example "line_type", or "registered".
 * @return The answer, one per number.
 * @customfunction
 */
function MV_LOOKUP(number, check, field) {
  var isRange = Array.isArray(number);
  var flat = isRange ? [].concat.apply([], number) : [number];
  var code = String(check || "whatsapp").trim().toLowerCase();
  code = MV_ALIASES[code] || (code.indexOf(".") < 0 ? code + ".registered" : code);
  var fld = field ? String(field).trim() : "";
  var country = PropertiesService.getUserProperties().getProperty(COUNTRY_PROP) || "";
  var cache = CacheService.getDocumentCache() || CacheService.getScriptCache();
  var answers = new Array(flat.length), todo = [];
  flat.forEach(function (v, i) {
    var n = cellToNumber(v, country);
    if (!n) { answers[i] = ""; return; }
    var hit = cache.get(cellCacheKey(n, code, fld));
    if (hit !== null && hit !== undefined) answers[i] = hit; else todo.push({ i: i, n: n });
  });
  var deadline = Date.now() + 25000;
  chunk(todo, 100).forEach(function (batch) {
    if (Date.now() > deadline) { batch.forEach(function (x) { answers[x.i] = "not checked (time limit): use the menu"; }); return; }
    var results;
    try { results = lookupBatch_(batch.map(function (x) { return x.n; }), [code], country, deadline); }
    catch (e) { throw new Error("MobileValidate: " + e.message); }
    batch.forEach(function (x, j) {
      var item = results[j] || {};
      var r = item.checks ? item.checks[code] : null;
      var a = r ? cellAnswer(r, fld) : item.number_status || "unknown";
      answers[x.i] = a;
      if (r && r.status === "completed") { try { cache.put(cellCacheKey(x.n, code, fld), String(a), CELL_CACHE_S); } catch (e) { /* ignore */ } }
    });
  });
  if (!isRange) return answers[0];
  var cols = number[0].length, out = [];
  for (var r = 0; r < number.length; r++) out.push(answers.slice(r * cols, (r + 1) * cols));
  return out;
}