Verify emails, phones, IPs and postal addresses in Google Sheets

A column of addresses becomes a column of verdicts without leaving the sheet. The script below adds six custom functions: SPAW_VERIFY checks one cell or a whole range against the Spaw API and returns any field of an email verification, SPAW_DOMAIN inspects a domain's mail setup, SPAW_PHONE validates and enriches phone numbers, SPAW_IP locates and risk-scores IP addresses, SPAW_ADDRESS reads and checks postal addresses, and SPAW_ENTITY reads a business identifier against company registers. It is plain Apps Script with no dependencies, it asks for no authorization because it only defines custom functions, and no message is ever sent to the addresses it checks.

Install

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

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

Formulas

The field argument (the second for SPAW_VERIFY, SPAW_DOMAIN and SPAW_IP, the third for SPAW_PHONE and SPAW_ADDRESS, whose second is the country, the fourth for SPAW_ENTITY) accepts any field of the email result, the domain result, the phone result, the IP result, the address result or the business identifier result, and a comma-separated list returns the fields side by side. Blank cells and cells holding anything but text answer an empty cell and never reach the API, except a whole number, which SPAW_PHONE and SPAW_ENTITY accept because a sheet stores an unformatted phone or company number that way. Field names are checked before any request, so a typo there shows an error instead of spending a credit. SPAW_DOMAIN also accepts an email address and uses its domain.

What to do with the verdict

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

Phone numbers

SPAW_PHONE takes the number a form collected, in any notation, and answers whether it is valid, with a typed reason when it is not: missing_country for a national number without the optional second argument, unassigned_block for a number in a block the regulator has not allocated, emergency_number or short_number for numbers that are not subscriber numbers. The other fields carry the E.164 form to store and dedupe by, the national and international forms, the country and its facts, line_type and carrier with the source that settled them (numbering_plan, or block_allocation from the regulators' tables, which say who the numbers were allocated to and never who serves them after a port), block_status, the is_fictional, is_virtual and is_disposable flags, and a published risk_score with its risk_signals. A plain numeric cell is accepted as a number. A range is sent to the batch endpoint 1,000 numbers per request, and a second column in the range gives each row its own country, which wins over the second argument. Numbers on your suppression list are answered free. To decide which rows get a code, ask for the decision rather than the evidence: =SPAW_PHONE(B2:B500, "GB", "ok_to_send,blocked_by") puts your account's SMS policy beside every number, TRUE or FALSE, with the rule that refused a row in the second column (invalid, opted_out for a single cell holding a number your suppression list holds as opted out, country, line_type, a signal such as disposable, risk_score, or suppressed for a number on your suppression list: a range is sent as a batch and honours that list, while a single cell is one lookup and never does). The policy is set once on the account, not in the sheet, so a sheet, a batch call and a bulk run apply the same rules. It is a fraud and deliverability decision, not consent and not a do-not-call scrub. A phone lookup costs 1 credit when the number is valid; invalid numbers, unassigned blocks and repeats within seven days are free. The script deliberately has no function for reporting SMS outcomes: custom functions must stay free of side effects, because Sheets recalculates them whenever it likes, so a report would be sent again and again; use the feedback endpoint or a connector for that.

IP addresses

SPAW_IP takes the address a signup, order or log line came from and answers where it is (country, city, timezone, currency), what network it belongs to and what kind of operator runs it, and tri-state flags for datacenter and cloud ranges, Tor exits, VPN exits and iCloud Private Relay, with hosting_provider and privacy_service naming the provider when its own published list contains the address. is_anonymous is true when any of Tor, VPN or relay is, risk_score is a published sum and risk_signals lists the weights applied, so a filter on the risk level needs no interpretation. A range is sent 1,000 addresses per request. The optional third argument privacy asks Spaw to keep nothing about the lookup, not even the seven-day repeat marker, and skips the script cache, so every such lookup is fresh and billed. One credit covers five IP lookups that resolve a location, a network or a positive flag: the lookup that opens a block reports credits_used 1, the next four 0, and meta.ip_lookups_prepaid counts what is left. Reserved or private ranges, unallocated space, unknown addresses and repeats within seven days are free.

Postal addresses

SPAW_ADDRESS takes the address a form or an order collected, on one line, and reads it the way its country writes addresses. The second argument is the two-letter country, and it is not optional: it decides which format the address is read under, which parts that country requires and which postcode directory answers, and it is never guessed. A single cell without one is refused before any request; a row inside a range without one comes back valid FALSE with reason missing_country, free. A range may carry a country per row in its second column, which wins over the argument, and every row is sent with its own country, 100 addresses per request.

The answer carries the components and the formatted address, whether the postcode matches the country's pattern and whether the directory still lists it (postcode_status), whether the town written on the address is the town the directory publishes for that postcode, what kind of delivery point it is (address_type: street, PO box, private mailbox, general delivery, rural route, military, parcel locker, bulk mail), how close a national address register comes to it (match_level against the register named), the postcode centroid, how many companies are registered there, and a published risk_score with the risk_signals behind it.

To decide which rows ship, ask for the decision rather than the evidence: =SPAW_ADDRESS(F2:F500, "GB", "ok_to_ship,blocked_by") puts your account's shipping policy beside every address, TRUE or FALSE, with the rule that refused a row in the second column (invalid, a signal such as placeholder, risk_score or unconfirmed, or suppressed for an address on your suppression list: a range is sent as a batch and honours that list, while a single cell is one lookup and never does). The policy is set once on the account, not in the sheet, so a sheet, a batch call and a bulk run apply the same rules.

An empty cell means the signal was not evaluated, never "no". postcode_status and the coordinates are empty where no postcode directory covers the country. exists is TRUE only on a premise match and FALSE only under a register whose coverage is complete: a register whose coverage is partial, such as the US National Address Database or the French address base, cannot make an address's absence mean anything, so it answers empty. is_mail_drop is a heuristic and not a postal service's own agency flag: it fires when a mailbox operator's own published branch list names the address, or when so many companies are registered there that it is one in practice, and mail_drop_source says which of the two.

Two answers are licensed and not enabled: whether post actually arrives (deliverability_checked comes back FALSE, and is_deliverable, is_cmra, is_vacant and is_residential stay empty) and the list of addresses inside a postcode, which has no formula here at all. A valid address costs 1 credit; invalid addresses and repeats within seven days are free.

Business identifiers

SPAW_ENTITY reads the identifier a supplier, a customer or a counterparty gave you against the registers Spaw syncs. The second argument says which register it belongs to, lei or company_number, and it is never guessed: reading a company number as a Legal Entity Identifier would answer a confident "no". A company number also needs the country of its register as the third argument, because company numbers are only unique inside their own register; a LEI is global by construction and the country is not sent with one. GB, the UK register, is the one installed today, and another country answers unsupported_register, free. A column is read as one type and sent to the batch endpoint 500 identifiers per request, so put each register in its own column.

The answer carries the legal name, whether the entity is still live (status, with the register's own wording in status_detail, which separates a strike-off from a liquidation), the country and jurisdiction it was formed in, its category and legal form, when it was registered and when the register last updated the record, and for a LEI checksum_valid, registration_status and next_renewal_at. Where a LEI names its entry in a national register Spaw also holds, flags names what the two disagree about and agreements quotes both sides, so a disagreement is something you can read rather than take on trust. dissolved_in_national_register is the strongest signal the product carries: a dissolved company no longer exists as a legal person and cannot enter a contract.

The one caveat, and it matters more than anything else here: the UK company file is a snapshot of the live register, so a company that is struck off is removed from the file rather than marked dissolved — the statuses that file carries are Active, Active - Proposal to Strike off, Liquidation, In Administration and similar, and never Dissolved. A UK dissolution therefore reaches you as found false with reason unknown_identifier rather than as that flag, which never fires on the only national register installed today. Check found before you trust the flag, and read status_detail for the earlier warning: Active - Proposal to Strike off folds to a status of active, because the company is still on the register. A LEI lookup of the same company is quieter still: GLEIF keeps its record, so found stays true and no flag fires. What moves is agreements, which comes back empty while other_register still names uk-companies-house — the counterpart row is gone. That pair is the signal on this path, and lei_registration_not_current follows later, when the lapsed registration is not renewed. A monitored list turns the disappearance into an alert of its own, left_register.

checksum_valid is a typing check and nothing more — it catches a mistyped or transposed character, while a perfectly formed LEI can never have been issued, which is what the register lookup answers. registration_status is about the identifier rather than the entity: a LEI must be reconfirmed each year and a great many are not, so LAPSED means the reference data has not been rechecked recently, not that the company has gone.

No registered address is returned, ever, and none is stored: a registered address is very often somebody's home, and an identifier alone does not say whether a company or a sole trader stands behind it. Both registers are bulk files held on the Spaw server, so no third party sits in the request path. The LEI reference data is published under a CC0 public-domain dedication and refreshed daily; the UK company data is used under the Open Government Licence v3.0 and republished monthly, so an answer can be up to five weeks behind the live register and sources carries the date of the copy it was read from. Spaw is not affiliated with, endorsed by or connected to either publisher, and an answer here is Spaw's rather than theirs. A business lookup costs 1 credit when a register carried the identifier; unknown, malformed and unsupported identifiers and repeats within seven days are free.

Cost and limits

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

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

The script

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

/**
 * Spaw email verification, phone validation, IP intelligence, postal
 * address and business identifier checks for Google Sheets.
 *
 * Custom functions that verify email addresses, inspect domains, validate
 * phone numbers, look up IP addresses, read postal addresses and check
 * business identifiers with the Spaw API (https://spaw.co) straight from a
 * formula:
 *
 *   =SPAW_VERIFY(A2)                              the verdict for one address
 *   =SPAW_VERIFY(A2, "reason")                    one field of the answer
 *   =SPAW_VERIFY(A2:A500, "deliverable,reason")   a range, several columns
 *   =SPAW_VERIFY(A2, "deliverable", "refresh")    a fresh, billable answer
 *   =SPAW_DOMAIN("acme.com", "mx_provider")       domain-level signals
 *   =SPAW_PHONE(D2)                               whether a phone number is valid
 *   =SPAW_PHONE(D2, "US", "e164,line_type")       a national number, several columns
 *   =SPAW_PHONE(D2:E500, "", "valid,reason")      a range with a country column, 1,000 per request
 *   =SPAW_IP(C2)                                  the risk level of an IP address
 *   =SPAW_IP(C2:C500, "country,is_vpn,risk_score") a range of addresses, several columns
 *   =SPAW_ADDRESS(F2, "GB")                       whether a postal address stands up
 *   =SPAW_ADDRESS(F2, "GB", "formatted,postcode_status") the tidied address and its postcode
 *   =SPAW_ADDRESS(F2:G500, "", "valid,reason")    a range with a country column, 100 per request
 *   =SPAW_ENTITY(H2, "lei")                       whether a register carries a business identifier
 *   =SPAW_ENTITY(H2, "company_number", "GB", "name,status") a company number and its register
 *   =SPAW_ENTITY(H2:H500, "lei", "", "status,flags") a column of identifiers, 500 per request
 *
 * Install
 *   1. In your spreadsheet open Extensions > Apps Script.
 *   2. Replace the contents of Code.gs with this file and save the project.
 *   3. Create a secret API key at https://spaw.co/api-keys and paste it into
 *      SPAW_API_KEY below, or store it as the script property SPAW_API_KEY
 *      (Project Settings > Script Properties). The script property wins when
 *      both are set.
 *   4. Back in the sheet, try =SPAW_VERIFY("[email protected]").
 *
 * Anyone with edit access to the spreadsheet can open the script and read the
 * key, so create a key just for this sheet and revoke it on the API keys page
 * to rotate it. Publishable pk_ keys are browser keys and do not work here.
 *
 * Every answered lookup (a deliverable or risky verdict) costs 1 credit,
 * and one credit covers five IP lookups; repeats within seven days and the
 * spaw.test test addresses are free, and undeliverable answers are free
 * within a fair-use allowance. A mailbox the handshake could not confirm
 * answers "risky" with reason "unverified", free. Answers are kept in the script cache for
 * six hours so a recalculation does not call the API again. A range is
 * verified 50 addresses per request, which is the fastest and cheapest way to
 * check a column.
 *
 * The optional third argument asks for freshness: "refresh" sets the earlier
 * answer aside and bills the lookup as fresh when answered, "max_age=86400"
 * does so only when the earlier answer is older than that many seconds, and
 * "timeout=10" caps the mailbox handshake at that many seconds. Combine them
 * with commas: "max_age=3600,timeout=10".
 *
 * SPAW_PHONE validates and enriches phone numbers: the E.164 and display
 * forms, the country and its facts, the line type and carrier with the source
 * that settled them (the numbering plan, or the block the regulator allocated,
 * which is never port-aware), the block status, the fictional, virtual and
 * disposable flags, and a published risk score. Pass a two-letter country as
 * the second argument for numbers written without a calling code, or give the
 * range a second column holding a country per row. A valid number costs 1
 * credit; invalid numbers, unassigned blocks and repeats within seven days are
 * free. A range is sent 1,000 numbers per request; numbers on your suppression
 * list are answered free. The script never reports SMS outcomes: custom
 * functions must stay free of side effects, so use the API or a connector for
 * that.
 *
 * SPAW_IP locates and risk-scores IPv4 and IPv6 addresses: country, city,
 * timezone and currency, the network and its operator type, tri-state flags
 * for datacenter, Tor, VPN and iCloud Private Relay with the provider named,
 * and a published risk score. One credit covers five lookups that resolve a
 * location, a network or a positive flag; reserved and unallocated ranges,
 * unknown addresses and repeats within seven days are free. A range is sent 1,000 addresses per
 * request. Its optional third argument "privacy" keeps nothing about the
 * lookup on Spaw's side (not even the repeat marker) and skips the script
 * cache, so every such lookup is fresh and billed.
 *
 * SPAW_ADDRESS reads a postal address the way its country writes them and
 * checks it against open data: the components and the formatted address,
 * whether the postcode exists and is still live, its centroid, whether the
 * town matches it, what kind of delivery point it is, how close a national
 * address register comes to it, how many companies are registered there,
 * and a published risk score. The country decides which format the address
 * is read under and is never guessed: pass a two-letter code as the second
 * argument, or give the range a second column holding one per row. A single
 * cell without a country is refused before any request; a row in a range
 * without one is answered "missing_country", free. A range is sent 100
 * addresses per request, each row carrying its own country. A valid address
 * costs 1 credit; invalid addresses and repeats within seven days are free.
 *
 * Two address answers are licensed and not enabled: whether post actually
 * arrives (deliverability_checked comes back false, and is_deliverable,
 * is_cmra, is_vacant and is_residential stay empty) and the list of
 * addresses inside a postcode, which has no function here at all. An empty
 * cell always means the signal was not evaluated, never "no".
 *
 * SPAW_ENTITY reads a business identifier against the registers Spaw syncs:
 * a Legal Entity Identifier, or a company number against the register of the
 * country named. The second argument says which of the two the identifier is
 * and is never guessed; a company number also needs the country of its
 * register as the third argument, because company numbers are only unique
 * inside their own register, while a LEI is global by construction and
 * ignores it. GB is the register installed today; another country answers
 * "unsupported_register", free. Both registers are bulk files held on the
 * Spaw server, so no third party sits in the request path. The answer gives
 * the legal name, whether the entity is still live and the register's own
 * wording for it, where it was formed, and for a LEI its check digit and
 * registration status, which is about the identifier rather than the entity:
 * a LEI must be reconfirmed each year and a great many are not. Where a LEI
 * names its entry in a national register Spaw also holds, both records are
 * compared and both sides are quoted in "agreements". A range goes 500
 * identifiers per request, all read as the same type. 1 credit when a
 * register answers; unknown, malformed and unsupported identifiers and
 * repeats within seven days are free.
 *
 * No registered address is ever returned, by design: a registered address is
 * very often somebody's home, and an identifier alone does not say whether a
 * company or a sole trader stands behind it. The LEI reference data is
 * published under a CC0 public-domain dedication and the UK company data
 * under the Open Government Licence v3.0; Spaw is not affiliated with,
 * endorsed by or connected to either publisher, and an answer here is Spaw's
 * rather than theirs. The UK file is republished monthly, so an answer can be
 * five weeks behind the live register: "sources" carries the date of the copy
 * it was read from.
 */

const SPAW_API_KEY = 'sk_live_REPLACE_ME';

const SPAW_API_BASE = 'https://spaw.co/api/v1';
const SPAW_BATCH_SIZE = 50;
const SPAW_IP_BATCH_SIZE = 1000;
const SPAW_PHONE_BATCH_SIZE = 1000;
const SPAW_ADDRESS_BATCH_SIZE = 100;
const SPAW_ENTITY_BATCH_SIZE = 500;
const SPAW_CACHE_SECONDS = 21600;
const SPAW_CACHE_PREFIX = 'spaw:v1:';
const SPAW_CACHE_KEY_LIMIT = 250;

const SPAW_EMAIL_FIELDS = [
  'email', 'normalized_email', 'is_alias', 'is_gibberish', 'looks_generated',
  'local_part_pattern', 'first_name', 'last_name', 'deliverable', 'reason',
  'risk_score', 'risk_level', 'risk_signals', 'syntax_valid', 'domain', 'domain_category',
  'mx_found', 'mx_implicit', 'mx_provider', 'mx_provider_type', 'mx_blocklisted',
  'mx_network_type', 'has_spf', 'dmarc_policy', 'domain_registered_at', 'domain_age_days',
  'domain_expires_at', 'parked', 'disposable', 'is_relay', 'relay_service', 'role',
  'role_type', 'free_provider', 'smtp_checked', 'smtp_checked_at', 'mailbox_exists',
  'previously_invalid', 'previously_invalid_at', 'catch_all', 'smtp_reason',
  'mailbox_confidence', 'widely_seen', 'did_you_mean', 'ok_to_send', 'blocked_by', 'sources',
];

const SPAW_DOMAIN_FIELDS = [
  'domain', 'valid', 'domain_category', 'mx_found', 'mx_implicit', 'catch_all', 'mx_provider',
  'mx_provider_type', 'mx_blocklisted', 'mx_network_type', 'has_spf', 'spf_all',
  'spf_lookup_count', 'dmarc_policy', 'dmarc_pct', 'dmarc_rua', 'dkim_selectors', 'mta_sts',
  'tls_rpt', 'bimi', 'domain_registered_at', 'domain_age_days', 'domain_expires_at',
  'domain_updated_at', 'registrar', 'registrant_redacted', 'parked', 'disposable', 'is_relay',
  'relay_service', 'free_provider', 'sources',
];

const SPAW_PHONE_FIELDS = [
  'valid', 'reason', 'e164', 'national', 'international', 'rfc3966', 'country',
  'country_name', 'calling_code', 'continent', 'is_eu', 'currency', 'line_type',
  'line_type_source', 'carrier', 'carrier_source', 'block_status', 'region',
  'is_geographic', 'timezones', 'extension', 'is_fictional', 'is_virtual',
  'is_disposable', 'disposable_seen_at', 'risk_score', 'risk_level',
  'risk_signals', 'hlr_checked', 'reachable', 'ported', 'current_carrier',
  'roaming', 'carrier_code', 'carrier_brand', 'block_pooled', 'block_assigned_at',
  'region_level', 'local_time', 'utc_offsets', 'did_you_mean',
  'range_seen_by_you', 'client_velocity', 'destination_new_for_you',
  'complaints_90d', 'complaints_last_at', 'ok_to_send', 'blocked_by', 'sources',
];

const SPAW_IP_FIELDS = [
  'ip', 'version', 'reason', 'network', 'continent', 'country', 'country_name', 'is_eu', 'is_eea',
  'privacy_regime', 'sanctions', 'fatf_list', 'region', 'city', 'latitude', 'longitude',
  'location_source', 'location_confidence', 'timezone', 'timezone_source', 'currency', 'calling_code',
  'asn', 'org', 'asn_type', 'connection_type', 'is_mobile', 'is_satellite', 'registry',
  'registry_country', 'allocated_at', 'is_datacenter', 'hosting_provider', 'is_corporate_proxy',
  'corporate_proxy', 'is_crawler', 'crawler', 'crawler_kind', 'is_blocklisted', 'blocklist',
  'is_attack_source', 'attack_source', 'is_tor', 'tor_last_seen', 'is_vpn', 'is_relay',
  'privacy_service', 'is_anonymous', 'account_list',
  'risk_score', 'risk_level', 'risk_signals', 'seen_by_you', 'sources',
];

const SPAW_ADDRESS_FIELDS = [
  'valid', 'reason', 'country', 'country_name', 'continent', 'is_eu', 'is_eea', 'privacy_regime',
  'formatted', 'formatted_lines', 'formatted_local', 'organization', 'building', 'street', 'house_number',
  'unit', 'po_box', 'dependent_locality', 'locality', 'administrative_area', 'postal_code',
  'postal_code_formatted', 'postal_code_valid', 'postal_code_type', 'postcode_status',
  'locality_matches_postcode', 'administrative_area_matches_postcode', 'address_type',
  'address_type_source', 'is_po_box', 'is_private_mailbox', 'exists', 'match_level', 'register',
  'register_coverage', 'premise_key', 'building_use', 'is_multi_unit', 'unit_check', 'street_suggestion',
  'latitude', 'longitude', 'location_source', 'companies_registered', 'is_mail_drop', 'mail_drop_provider',
  'mail_drop_source', 'deliverability_checked', 'is_deliverable', 'is_cmra', 'is_vacant',
  'is_residential', 'confirmation', 'changes', 'looks_placeholder', 'seen_by_you', 'risk_score', 'risk_level',
  'risk_signals', 'ok_to_ship', 'blocked_by', 'sources',
];

const SPAW_ENTITY_FIELDS = [
  'identifier', 'identifier_type', 'found', 'reason', 'name', 'status', 'status_detail',
  'country', 'jurisdiction', 'category', 'legal_form', 'registered_at', 'record_updated_at',
  'checksum_valid', 'registration_status', 'next_renewal_at', 'other_register',
  'other_identifier', 'flags', 'agreements', 'sources',
];

const SPAW_ENTITY_TYPES = ['lei', 'company_number'];

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

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

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

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

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

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

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

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

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

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

/**
 * Validates and enriches one phone number, or every number in a range, with
 * Spaw: the E.164 and display forms, country facts, line type and carrier with
 * the source that settled them, the regulator's block status, the fictional,
 * virtual and disposable flags, and the risk score. A range may carry a
 * second column with a country per row, which wins over the country argument.
 *
 * @param {string|string[][]} value A cell holding a phone number, or a range of them; a second column holds a two-letter country per row.
 * @param {string} [country] A two-letter ISO 3166-1 code (US, GB, PL) for numbers written without a calling code; leave empty for numbers in international format.
 * @param {string} [fields] The field to return, "valid" by default. A comma-separated list such as "e164,line_type,carrier" returns one column per field.
 * @return {string|boolean|number|Array} The requested field, or one row per input row.
 * @customfunction
 */
function SPAW_PHONE(value, country, fields) {
  const names = spawFieldNames_(fields, 'valid', SPAW_PHONE_FIELDS);
  const region = spawCleanCountry_(country);

  if (!Array.isArray(value)) {
    const number = spawCleanPhone_(value);
    const values = spawValues_(number === '' ? null : spawPhoneOne_(number, region), names);

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

  const entries = value.map(function (row) {
    const cells = Array.isArray(row) ? row : [row];
    const rowRegion = cells.length > 1 ? spawCleanCountry_(cells[1]) : '';

    return { phone: spawCleanPhone_(cells[0]), region: rowRegion === '' ? region : rowRegion };
  });
  const results = spawPhoneMany_(entries);

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

/**
 * Locates and risk-scores one IP address, or every address in a range, with
 * Spaw: location, network, operator type, datacenter / Tor / VPN / relay
 * flags with the provider named, and the risk score.
 *
 * @param {string|string[][]} value A cell holding an IPv4 or IPv6 address, or a one-column range of them.
 * @param {string} [fields] The field to return, "risk_level" by default. A comma-separated list such as "country,is_vpn,risk_score" returns one column per field.
 * @param {string} [options] "privacy" to keep nothing about the lookup on Spaw's side and skip the cache; every such lookup is fresh and billed.
 * @return {string|boolean|number|Array} The requested field, or one row per input row.
 * @customfunction
 */
function SPAW_IP(value, fields, options) {
  const names = spawFieldNames_(fields, 'risk_level', SPAW_IP_FIELDS);
  const privacy = spawIpOptions_(options);

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

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

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

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

/**
 * Reads one postal address, or every address in a range, the way its
 * country writes them: the components and the formatted address, the
 * postcode's status and centroid, the delivery-point type, how close a
 * national address register comes to it, the companies registered there,
 * and the risk score. A range may carry a second column with a country per
 * row, which wins over the country argument.
 *
 * An empty cell means the signal was not evaluated, never "no": the
 * postcode status is empty where no directory covers the country, and
 * `exists` is empty unless the building was found or a register whose
 * coverage is complete does not carry it. `is_mail_drop` is a heuristic
 * from mailbox operators' own published branch lists and the number of
 * companies registered at the address, not a postal service's own flag.
 *
 * @param {string|string[][]} value A cell holding a postal address on one line, or a range of them; a second column holds a two-letter country per row.
 * @param {string} [country] A two-letter ISO 3166-1 code (GB, US, PL). It decides which country's format the address is read under, and one cell needs it.
 * @param {string} [fields] The field to return, "valid" by default. A comma-separated list such as "formatted,postcode_status,risk_level" returns one column per field.
 * @return {string|boolean|number|Array} The requested field, or one row per input row.
 * @customfunction
 */
function SPAW_ADDRESS(value, country, fields) {
  const names = spawFieldNames_(fields, 'valid', SPAW_ADDRESS_FIELDS);
  const region = spawCleanCountry_(country);

  if (!Array.isArray(value)) {
    const address = spawCleanInput_(value);

    if (address !== '' && region === '') {
      throw new Error('Spaw: SPAW_ADDRESS needs a two-letter country as its second argument, for example =SPAW_ADDRESS(F2, "GB") (MISSING_COUNTRY)');
    }

    const values = spawValues_(address === '' ? null : spawAddressOne_(address, region), names);

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

  const entries = value.map(function (row) {
    const cells = Array.isArray(row) ? row : [row];
    const rowRegion = cells.length > 1 ? spawCleanCountry_(cells[1]) : '';

    return { address: spawCleanInput_(cells[0]), region: rowRegion === '' ? region : rowRegion };
  });
  const results = spawAddressMany_(entries);

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

/**
 * Reads one business identifier against the registers Spaw syncs, or every
 * identifier in a column: a Legal Entity Identifier, or a company number
 * against the register of the country named. The answer gives the legal
 * name, whether the entity is still live, where it was formed, and for a
 * LEI its check digit and registration status, plus what the two registers
 * agree and disagree about. It never carries a registered address.
 *
 * @param {string|string[][]} value A cell holding a LEI or a company number, or a one-column range of them.
 * @param {string} type Which register the identifier belongs to: "lei" or "company_number". It is never guessed.
 * @param {string} [country] A two-letter ISO 3166-1 code naming the register a company number belongs to. Required for a company number, ignored for a LEI.
 * @param {string} [fields] The field to return, "found" by default. A comma-separated list such as "name,status,registration_status" returns one column per field.
 * @return {string|boolean|number|Array} The requested field, or one row per input row.
 * @customfunction
 */
function SPAW_ENTITY(value, type, country, fields) {
  const names = spawFieldNames_(fields, 'found', SPAW_ENTITY_FIELDS);
  const register = spawEntityType_(type);
  const region = spawCleanCountry_(country);

  // A company number without its register has nothing to be looked up in,
  // so it is refused here rather than spending a request on a 422.
  if (register === 'company_number' && region === '') {
    throw new Error('Spaw: a company number needs the country of its register as the third argument, for example =SPAW_ENTITY(H2, "company_number", "GB") (MISSING_COUNTRY)');
  }

  if (!Array.isArray(value)) {
    const identifier = spawCleanIdentifier_(value);
    const values = spawValues_(identifier === '' ? null : spawEntityOne_(identifier, register, region), names);

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

  const identifiers = value.map(function (row) {
    return spawCleanIdentifier_(Array.isArray(row) ? row[0] : row);
  });
  const results = spawEntityMany_(identifiers, register, region);

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

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

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

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

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

  return data;
}

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

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

    const norm = address.toLowerCase();

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

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

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

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

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

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

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

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

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

    spawCachePutAll_(cache, entries);

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

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

  return results;
}

function spawIpOne_(address, privacy) {
  const cache = spawCache_();
  const key = spawCacheKey_('ip', address.toLowerCase());
  const cached = privacy.bypassCache ? null : spawCacheGet_(cache, key);

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

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

  if (!privacy.bypassCache) {
    spawCachePutAll_(cache, key === null ? {} : spawCacheEntry_(key, data));
  }

  return data;
}

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

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

    const norm = address.toLowerCase();

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

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

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

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

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

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

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

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

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

    spawCachePutAll_(cache, entries);

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

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

  return results;
}

function spawPhoneOne_(number, region) {
  const cache = spawCache_();
  const key = spawPhoneCacheKey_(number, region);
  const cached = spawCacheGet_(cache, key);

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

  const payload = { phone: number };

  if (region !== '') {
    payload.country = region;
  }

  const body = spawRequest_('POST', '/phone', payload);
  const data = body.data || null;

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

  return data;
}

/**
 * A range goes to the batch endpoint 1,000 numbers per request, served
 * from the cache where it can be. Each entry carries its own region (the
 * row's country column, else the country argument); a number is sent as
 * an object with its country when it has one, so every row parses under
 * the right numbering plan.
 */
function spawPhoneMany_(entries) {
  const results = entries.map(function () {
    return null;
  });
  const cache = spawCache_();
  const positions = {};
  const unique = [];

  entries.forEach(function (entry, index) {
    if (entry.phone === '') {
      return;
    }

    const norm = entry.region + ':' + entry.phone.toLowerCase();

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

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

  const keys = unique.map(function (norm) {
    const entry = entries[positions[norm][0]];

    return spawPhoneCacheKey_(entry.phone, entry.region);
  });
  const cached = spawCacheGetAll_(cache, keys);
  const missing = [];

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

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

  for (let start = 0; start < missing.length; start += SPAW_PHONE_BATCH_SIZE) {
    const chunk = missing.slice(start, start + SPAW_PHONE_BATCH_SIZE);
    const phones = chunk.map(function (norm) {
      const entry = entries[positions[norm][0]];

      return entry.region === '' ? entry.phone : { phone: entry.phone, country: entry.region };
    });
    const body = spawRequest_('POST', '/phone/batch', { phones: phones });
    const items = (body.data && body.data.results) || [];
    const cacheEntries = {};

    items.forEach(function (item, position) {
      const norm = chunk[position];
      const entry = entries[positions[norm][0]];
      const data = (item && item.data) || null;
      const key = spawPhoneCacheKey_(entry.phone, entry.region);

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

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

    spawCachePutAll_(cache, cacheEntries);

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

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

  return results;
}

function spawPhoneCacheKey_(number, region) {
  return spawCacheKey_('phone', region + ':' + number.toLowerCase());
}

function spawAddressOne_(address, region) {
  const cache = spawCache_();
  const key = spawAddressCacheKey_(address, region);
  const cached = spawCacheGet_(cache, key);

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

  const payload = { address: address };

  if (region !== '') {
    payload.country = region;
  }

  const body = spawRequest_('POST', '/address', payload);
  const data = body.data || null;

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

  return data;
}

/**
 * A range goes to the batch endpoint 100 addresses per request, served
 * from the cache where it can be. Each entry carries its own region (the
 * row's country column, else the country argument); an address is sent as
 * an object with its country when it has one, so every row is read under
 * the right country's format.
 */
function spawAddressMany_(entries) {
  const results = entries.map(function () {
    return null;
  });
  const cache = spawCache_();
  const positions = {};
  const unique = [];

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

    const norm = entry.region + ':' + entry.address.toLowerCase();

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

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

  const keys = unique.map(function (norm) {
    const entry = entries[positions[norm][0]];

    return spawAddressCacheKey_(entry.address, entry.region);
  });
  const cached = spawCacheGetAll_(cache, keys);
  const missing = [];

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

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

  for (let start = 0; start < missing.length; start += SPAW_ADDRESS_BATCH_SIZE) {
    const chunk = missing.slice(start, start + SPAW_ADDRESS_BATCH_SIZE);
    const addresses = chunk.map(function (norm) {
      const entry = entries[positions[norm][0]];

      return entry.region === '' ? entry.address : { address: entry.address, country: entry.region };
    });
    const body = spawRequest_('POST', '/address/batch', { addresses: addresses });
    const items = (body.data && body.data.results) || [];
    const cacheEntries = {};

    items.forEach(function (item, position) {
      const norm = chunk[position];
      const entry = entries[positions[norm][0]];
      const data = (item && item.data) || null;
      const key = spawAddressCacheKey_(entry.address, entry.region);

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

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

    spawCachePutAll_(cache, cacheEntries);

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

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

  return results;
}

function spawAddressCacheKey_(address, region) {
  return spawCacheKey_('address', region + ':' + address.toLowerCase());
}

function spawEntityOne_(identifier, register, region) {
  const cache = spawCache_();
  const key = spawEntityCacheKey_(identifier, register, region);
  const cached = spawCacheGet_(cache, key);

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

  const body = spawRequest_('POST', '/entity', spawEntityItem_(identifier, register, region));
  const data = body.data || null;

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

  return data;
}

/**
 * A column goes to the batch endpoint 500 identifiers per request, served
 * from the cache where it can be. Every row is read as the same type, so
 * the register and its country are the arguments rather than a column.
 */
function spawEntityMany_(identifiers, register, region) {
  const results = identifiers.map(function () {
    return null;
  });
  const cache = spawCache_();
  const positions = {};
  const unique = [];

  identifiers.forEach(function (identifier, index) {
    if (identifier === '') {
      return;
    }

    const norm = identifier.toLowerCase();

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

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

  const keys = unique.map(function (norm) {
    return spawEntityCacheKey_(identifiers[positions[norm][0]], register, region);
  });
  const cached = spawCacheGetAll_(cache, keys);
  const missing = [];

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

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

  for (let start = 0; start < missing.length; start += SPAW_ENTITY_BATCH_SIZE) {
    const chunk = missing.slice(start, start + SPAW_ENTITY_BATCH_SIZE);
    const sent = chunk.map(function (norm) {
      return spawEntityItem_(identifiers[positions[norm][0]], register, region);
    });
    const body = spawRequest_('POST', '/entity/batch', { identifiers: sent });
    const items = (body.data && body.data.results) || [];
    const cacheEntries = {};

    items.forEach(function (item, position) {
      const norm = chunk[position];
      const identifier = identifiers[positions[norm][0]];
      const data = (item && item.data) || null;
      const key = spawEntityCacheKey_(identifier, register, region);

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

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

    spawCachePutAll_(cache, cacheEntries);

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

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

  return results;
}

/**
 * One item as both endpoints take it. The country is sent only with a
 * company number: a LEI is global by construction and the API ignores it.
 */
function spawEntityItem_(identifier, register, region) {
  const item = { identifier: identifier, type: register };

  if (register === 'company_number') {
    item.country = region;
  }

  return item;
}

function spawEntityCacheKey_(identifier, register, region) {
  return spawCacheKey_('entity', register + ':' + region + ':' + identifier.toLowerCase());
}

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

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

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

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

  return data;
}

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

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

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

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

  return spawParse_(response);
}

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

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

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

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

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

  return body;
}

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

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

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

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

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

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

function spawApiKey_() {
  let key = '';

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

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

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

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

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

  return key;
}

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

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

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

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

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

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

      return;
    }

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

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

      parsed.body[name] = seconds;

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

      return;
    }

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

  return parsed;
}

/**
 * Parses the SPAW_IP options: "privacy" asks the API to keep nothing about
 * the lookup and skips the script cache, because the point is that no copy
 * of the answer is kept anywhere.
 */
function spawIpOptions_(options) {
  const parsed = { body: {}, bypassCache: false };
  const raw = options === undefined || options === null ? '' : String(options).trim();

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

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

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

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

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

      return;
    }

    throw new Error('Spaw: unknown option "' + token.trim() + '"; SPAW_IP accepts privacy (UNKNOWN_OPTION)');
  });

  return parsed;
}

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

  return target;
}

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

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

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

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

/**
 * A phone number cell may hold text or a plain number (Sheets stores
 * 4155550142 as a number); anything else answers an empty cell.
 */
function spawCleanPhone_(value) {
  if (typeof value === 'number') {
    return isFinite(value) && Math.floor(value) === value ? String(value) : '';
  }

  return spawCleanInput_(value);
}

/**
 * A company number cell often holds a plain number, because Sheets stores
 * 1234567 that way and the register pads it back to eight characters. A
 * LEI is always text.
 */
function spawCleanIdentifier_(value) {
  if (typeof value === 'number') {
    return isFinite(value) && Math.floor(value) === value ? String(value) : '';
  }

  return spawCleanInput_(value);
}

/**
 * Which register the identifier belongs to. There is no default: an
 * identifier does not say which register issued it, and reading a company
 * number as a LEI would answer a confident "no".
 */
function spawEntityType_(value) {
  const cleaned = value === undefined || value === null ? '' : String(value).trim().toLowerCase();

  if (SPAW_ENTITY_TYPES.indexOf(cleaned) === -1) {
    throw new Error('Spaw: the second argument must be one of ' + SPAW_ENTITY_TYPES.join(', ') + ', for example =SPAW_ENTITY(H2, "lei") (UNKNOWN_TYPE)');
  }

  return cleaned;
}

function spawCleanCountry_(value) {
  const cleaned = value === undefined || value === null ? '' : String(value).trim().toUpperCase();

  if (cleaned !== '' && !/^[A-Z]{2}$/.test(cleaned)) {
    throw new Error('Spaw: the country must be a two-letter ISO 3166-1 code such as US or GB (UNKNOWN_COUNTRY)');
  }

  return cleaned;
}

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

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

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

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

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

  return value;
}

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

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

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

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

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

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

  return entry;
}

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

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

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

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

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

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

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

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