Checked dans Excel et Google Sheets

/*
 * Checked.be in Google Sheets. Version 1.0.0.0.
 * Install: Extensions, Apps Script, replace everything with this file, save,
 * reload the spreadsheet, then Checked, Set API key (Checked, API-sleutel instellen).
 * Help: https://checked.be/nl/developers/spreadsheets
 */

var CHECKED_ORIGIN = 'https://checked.be';
/*
 * Checked.be in Excel and Google Sheets: the shared, pure part (2026-09-27).
 *
 * One file, three hosts: the Excel add-in loads it before its functions
 * (excel-functions.js), the Google Sheets script is this file followed by the
 * Apps Script part (served assembled at /integrations/sheets/checked.gs), and
 * the test suite runs it under Node (SpreadsheetFunctionsJsTests). Nothing in
 * here does I/O or knows its host: numbers in, words and values out.
 *
 * The contract lives in C# (Services/Integrations/SpreadsheetFunctions.cs):
 * the function names, the field keys and the figure keys. A test holds this
 * file to it. No API key is ever written in this file or in any served file:
 * each user stores their own key on their own side.
 */
(function (root, factory) {
  var core = factory();
  if (typeof module === 'object' && module.exports) module.exports = core;
  else root.CheckedCore = core;
}(typeof globalThis !== 'undefined' ? globalThis : this, function () {
  'use strict';

  /** Companies in one call of POST /api/v1/companies/batch. */
  var BATCH_MAX = 100;
  /** Batch calls a key may make a minute (ApiCosts.BatchPerMinute). */
  var CALLS_PER_MINUTE = 10;
  var WINDOW_MS = 60000;
  /** The blocks every spreadsheet call asks for, so one answer serves every function. */
  var INCLUDE = ['score', 'risk', 'financials'];

  /** The field keys of CHECKED(number; field), each with the words a user may type. */
  var FIELDS = {
    name: ['name', 'naam', 'nom', 'denomination', 'benaming'],
    status: ['status', 'toestand', 'statut', 'situation', 'situatie'],
    in_register: ['in_register', 'ingeschreven', 'inscrite', 'inscrit', 'registered'],
    legal_form: ['legal_form', 'rechtsvorm', 'forme_juridique', 'forme', 'form'],
    nace: ['nace', 'nacecode', 'code_nace', 'nace_code'],
    activity: ['activity', 'activiteit', 'activite', 'sector', 'secteur'],
    zip: ['zip', 'postcode', 'code_postal', 'postal_code'],
    city: ['city', 'gemeente', 'commune', 'plaats', 'stad', 'ville'],
    start_date: ['start_date', 'startdatum', 'begindatum', 'date_de_debut', 'date_debut', 'founded'],
    score: ['score', 'checked_score', 'score_checked'],
    score_band: ['score_band', 'band', 'klasse', 'scoreklasse', 'classe', 'classe_du_score'],
    risk: ['risk', 'risico', 'faillissementsrisico', 'risque', 'risque_de_faillite', 'bankruptcy_risk'],
    probability: ['probability', 'faillissementskans', 'kans', 'probabilite', 'probabilite_de_faillite', 'bankruptcy_probability'],
    last_fiscal_year: ['last_fiscal_year', 'laatste_boekjaar', 'dernier_exercice', 'boekjaar', 'exercice'],
    filings: ['filings', 'jaarrekeningen', 'comptes_annuels', 'comptes'],
    url: ['url', 'link', 'dossier', 'lien']
  };

  /** The figure keys of CHECKED_FIGURE(number; figure; year), the keys of /financials. */
  var FIGURES = {
    revenue: ['revenue', 'turnover', 'omzet', 'chiffre_d_affaires', 'chiffre_affaires', 'ca', 'sales'],
    ebitda: ['ebitda'],
    netprofit: ['netprofit', 'net_profit', 'net_result', 'nettoresultaat', 'resultaat', 'winst', 'resultat_net', 'benefice', 'profit'],
    equity: ['equity', 'eigen_vermogen', 'capitaux_propres', 'fonds_propres'],
    totalassets: ['totalassets', 'total_assets', 'balanstotaal', 'total_du_bilan', 'total_bilan'],
    totaldebt: ['totaldebt', 'total_debt', 'schulden', 'totale_schulden', 'dettes'],
    staffcosts: ['staffcosts', 'staff_costs', 'personeelskosten', 'frais_de_personnel'],
    fte: ['fte', 'vte', 'etp', 'employees', 'werknemers', 'personeel', 'personnel'],
    solvency: ['solvency', 'solvabiliteit', 'solvabilite'],
    currentratio: ['currentratio', 'current_ratio', 'liquiditeit', 'liquidite'],
    debtequity: ['debtequity', 'debt_equity', 'schuldgraad', 'endettement'],
    netmargin: ['netmargin', 'net_margin', 'nettomarge', 'marge_nette'],
    ebitdamargin: ['ebitdamargin', 'ebitda_margin', 'ebitdamarge', 'marge_ebitda'],
    roe: ['roe', 'rendement_eigen_vermogen'],
    roa: ['roa', 'rendement_activa']
  };

  /** The Checked score's bands, as the dossier names them (HealthBarometerCard). */
  var SCORE_BANDS = {
    excellent: ['Uitstekend', 'Excellent', 'Excellent'],
    good: ['Gezond', 'Sain', 'Healthy'],
    fair: ['Aanvaardbaar', 'Acceptable', 'Fair'],
    weak: ['Zwak', 'Faible', 'Weak'],
    poor: ['Kritiek', 'Critique', 'Critical']
  };

  /** Why there is no Checked score: the state keys of the API's score block. */
  var SCORE_STATES = {
    no_figures: ['Geen cijfers', 'Pas de chiffres', 'No figures'],
    insufficient_evidence: ['Te weinig cijfers', 'Trop peu de chiffres', 'Too few figures'],
    stale_evidence: ['Cijfers te oud', 'Chiffres trop anciens', 'Figures too old'],
    financial_institution: ['Financiële instelling', 'Institution financière', 'Financial institution'],
    investment_holding: ['Beleggingsholding', "Holding d'investissement", 'Investment holding'],
    not_registered: ['Inactief', 'Inactif', 'Inactive']
  };

  /** The bands of the bankruptcy probability (the API's risk block). */
  var BANDS = {
    Low: ['Laag', 'Faible', 'Low'],
    Moderate: ['Matig', 'Modéré', 'Moderate'],
    Elevated: ['Verhoogd', 'Élevé', 'Elevated'],
    High: ['Hoog', 'Haut', 'High'],
    Severe: ['Kritiek', 'Critique', 'Severe']
  };

  /** Why there is no probability band: the state keys of the API's risk block. */
  var STATES = {
    not_measured: ['Niet gemeten', 'Non mesuré', 'Not measured'],
    stale_score: ['Niet actueel', 'Pas à jour', 'Not current'],
    not_registered: ['Inactief', 'Inactif', 'Inactive'],
    open_procedure: ['Lopende procedure', 'Procédure en cours', 'Procedure open'],
    previous_model: ['Wordt herberekend', 'En cours de recalcul', 'Being recalculated']
  };

  var MESSAGES = {
    no_key: ['Geen API-sleutel ingesteld. Kies Checked, API-sleutel instellen.', 'Aucune clé API. Choisissez Checked, Définir la clé API.', 'No API key set. Choose Checked, Set API key.'],
    bad_key: ['De API-sleutel wordt niet aanvaard. Stel een geldige sleutel in.', 'La clé API est refusée. Définissez une clé valide.', 'The API key is not accepted. Set a valid key.'],
    bad_kbo: ['Geen geldig ondernemingsnummer.', "Numéro d'entreprise non valable.", 'Not a valid enterprise number.'],
    unknown_kbo: ['Onbekend ondernemingsnummer.', "Numéro d'entreprise inconnu.", 'Unknown enterprise number.'],
    bad_field: ['Onbekend veld. Zie de lijst op checked.be.', 'Champ inconnu. Voir la liste sur checked.be.', 'Unknown field. See the list on checked.be.'],
    bad_figure: ['Onbekend cijfer. Zie de lijst op checked.be.', 'Chiffre inconnu. Voir la liste sur checked.be.', 'Unknown figure. See the list on checked.be.'],
    bad_year: ['Geen geldig boekjaar.', 'Exercice non valable.', 'Not a valid fiscal year.'],
    rate_limited: ['Limiet van de API-sleutel bereikt. Probeer over een minuut opnieuw.', 'Limite de la clé API atteinte. Réessayez dans une minute.', 'The API key limit is reached. Try again in a minute.'],
    unavailable: ['De bron antwoordt nu niet. Probeer later opnieuw.', 'La source ne répond pas. Réessayez plus tard.', 'The source does not answer. Try again later.'],
    busy: ['Checked is nog bezig. Herbereken over enkele seconden.', 'Checked est encore occupé. Recalculez dans quelques secondes.', 'Checked is still busy. Recalculate in a few seconds.'],
    natural_person: ['Natuurlijk persoon (naam niet getoond)', 'Personne physique (nom non affiché)', 'Natural person (name not shown)'],
    not_in_register: ['Inactief', 'Inactif', 'Inactive'],
    active: ['Actief', 'Actif', 'Active']
  };

  function langIndex(lang) { return lang === 'fr' ? 1 : lang === 'en' ? 2 : 0; }

  /** A three-way wording in the given language (nl, fr or en). */
  function words(tri, lang) { return tri[langIndex(lang)]; }

  /** The message for an error code, in the given language. */
  function message(code, lang) { return words(MESSAGES[code] || MESSAGES.unavailable, lang); }

  /** "nl_BE", "fr-FR", "en" and friends to nl, fr or en. Dutch by default. */
  function langOf(locale) {
    var l = String(locale || '').toLowerCase().slice(0, 2);
    return l === 'fr' ? 'fr' : l === 'en' ? 'en' : 'nl';
  }

  /**
   * The ten digits of an enterprise number in any common form, or null: dotted,
   * with BE, with spaces, the old nine digits, or a spreadsheet number that lost
   * its leading zero (400378485). Only 0 and 1 open an enterprise number, the
   * server's rule (ApiKbo.Normalize).
   */
  function normalizeKbo(input) {
    if (input === null || input === undefined) return null;
    var s = typeof input === 'number' ? (isFinite(input) ? String(Math.round(input)) : '') : String(input);
    if (s.length > 40) return null;
    var d = s.replace(/\D/g, '');
    if (d.length === 9) d = '0' + d;
    if (d.length !== 10) return null;
    return d.charAt(0) === '0' || d.charAt(0) === '1' ? d : null;
  }

  /** True for an empty cell: no error, just an empty answer. */
  function isBlank(input) {
    return input === null || input === undefined || (typeof input === 'string' && input.trim() === '');
  }

  function keyOf(text) {
    return String(text || '').trim().toLowerCase()
      .normalize('NFD').replace(/[̀-ͯ]/g, '')
      .replace(/[^a-z0-9]+/g, '_').replace(/^_+|_+$/g, '');
  }

  function resolve(table, text) {
    var k = keyOf(text);
    if (!k) return null;
    for (var key in table) {
      if (Object.prototype.hasOwnProperty.call(table, key) && table[key].indexOf(k) >= 0) return key;
    }
    return null;
  }

  /** The canonical field key a user typed, or null. */
  function resolveField(text) { return resolve(FIELDS, text); }

  /** The canonical figure key a user typed, or null. */
  function resolveFigure(text) { return resolve(FIGURES, text); }

  /** The body of one batch call. */
  function requestBody(numbers, lang) {
    return { enterprise_numbers: numbers, include: INCLUDE.slice(), lang: lang };
  }

  /** A list cut into batches of at most BATCH_MAX. */
  function chunk(list, size) {
    var n = size || BATCH_MAX, out = [];
    for (var i = 0; i < list.length; i += n) out.push(list.slice(i, i + n));
    return out;
  }

  /** Distinct normalized numbers, in order, from any mix of cells. */
  function distinctNumbers(inputs) {
    var seen = {}, out = [];
    for (var i = 0; i < inputs.length; i++) {
      var k = normalizeKbo(inputs[i]);
      if (k && !seen[k]) { seen[k] = true; out.push(k); }
    }
    return out;
  }

  /** One company of a batch answer, kept small for the host's cache. */
  function compact(c) {
    var score = c.score || null, risk = c.risk || null, fin = c.financials || null;
    var years = [];
    if (fin && fin.years) {
      for (var i = 0; i < fin.years.length; i++) {
        var y = fin.years[i], v = {};
        var metrics = (y.figures || []).concat(y.ratios || []);
        for (var j = 0; j < metrics.length; j++) {
          if (metrics[j].value !== null && metrics[j].value !== undefined) v[metrics[j].key] = metrics[j].value;
        }
        years.push({ y: y.fiscal_year, c: y.monetary_currency || null, v: v });
      }
    }
    return {
      n: c.enterprise_number,
      name: c.name || null,
      np: !!c.natural_person,
      reg: !!c.in_register,
      lf: c.legal_form_label || c.legal_form || null,
      sc: c.register_situation_code || null,
      sl: c.register_situation_label || null,
      sd: c.start_date || null,
      nace: c.nace || null,
      act: c.nace_label || null,
      zip: c.zip || null,
      city: c.city || null,
      lfy: c.last_fiscal_year_end || null,
      fc: typeof c.filing_count === 'number' ? c.filing_count : null,
      url: c.dossier_url || null,
      ss: score ? score.state : null,
      sv: score && typeof score.score === 'number' ? score.score : null,
      sb: score ? score.band : null,
      rs: risk ? risk.state : null,
      rb: risk ? risk.band : null,
      rp: risk && typeof risk.probability === 'number' ? risk.probability : null,
      fy: years
    };
  }

  /**
   * A batch answer as a map: number to compact record, or to null for a
   * well-formed number the register does not hold.
   */
  function ingest(response) {
    var out = {};
    var found = (response && response.found) || [];
    for (var i = 0; i < found.length; i++) out[found[i].enterprise_number] = compact(found[i]);
    var missing = (response && response.not_found) || [];
    for (var j = 0; j < missing.length; j++) out[missing[j]] = null;
    return out;
  }

  function capitalize(s) { return s ? s.charAt(0).toUpperCase() + s.slice(1) : s; }

  /** The words for the company's status: registered and ordinary, or what the register records. */
  function statusText(rec, lang) {
    if (!rec.reg) return message('not_in_register', lang);
    if (!rec.sc || rec.sc === '000') return message('active', lang);
    return capitalize(rec.sl) || rec.sc;
  }

  /** The Checked score's band in words, or why there is no score. */
  function scoreBandText(rec, lang) {
    if (rec.sb && SCORE_BANDS[rec.sb]) return words(SCORE_BANDS[rec.sb], lang);
    return words(SCORE_STATES[rec.ss] || SCORE_STATES.no_figures, lang);
  }

  /** The bankruptcy probability's band in words, or why there is none. */
  function riskText(rec, lang) {
    if (rec.rb && BANDS[rec.rb]) return words(BANDS[rec.rb], lang);
    return words(STATES[rec.rs] || STATES.not_measured, lang);
  }

  /** Dates the host may turn into date cells. */
  var DATE_FIELDS = { start_date: true };

  /**
   * One field of one company. Null means "nothing to show" (the host writes an
   * empty cell): a figure that was never filed is not a zero.
   */
  function value(rec, field, lang) {
    switch (field) {
      case 'name': return rec.np ? message('natural_person', lang) : rec.name;
      case 'status': return statusText(rec, lang);
      case 'in_register': return rec.reg;
      case 'legal_form': return rec.lf;
      case 'nace': return rec.nace;
      case 'activity': return rec.act;
      case 'zip': return rec.zip;
      case 'city': return rec.city;
      case 'start_date': return rec.sd;
      case 'score': return rec.sv;
      case 'score_band': return rec.np ? null : scoreBandText(rec, lang);
      case 'risk': return rec.np ? null : riskText(rec, lang);
      case 'probability': return rec.rp === null || rec.rp === undefined ? null : Math.round(rec.rp * 10000) / 100;
      case 'last_fiscal_year': return rec.lfy ? Number(String(rec.lfy).slice(0, 4)) : null;
      case 'filings': return rec.fc;
      case 'url': return rec.url;
      default: return null;
    }
  }

  /**
   * One key figure: of the given fiscal year, or of the newest year when none is
   * given. Ratios in percent are as /financials writes them (35.2 is 35.2 percent).
   */
  function figure(rec, key, year) {
    var years = rec.fy || [];
    for (var i = 0; i < years.length; i++) {
      if (year === null || year === undefined || years[i].y === year) {
        var v = years[i].v[key];
        return v === undefined ? null : v;
      }
    }
    return null;
  }

  /** A fiscal year typed in a cell (2024, "2024"), or null for none, or NaN when invalid. */
  function yearOf(input) {
    if (isBlank(input)) return null;
    var n = typeof input === 'number' ? input : Number(String(input).trim());
    if (!isFinite(n) || Math.floor(n) !== n || n < 1990 || n > 2100) return NaN;
    return n;
  }

  /**
   * How long to wait before the next batch call so a key stays under its limit:
   * CALLS_PER_MINUTE in any WINDOW_MS. 0 means go now.
   */
  function waitMs(callTimes, now) {
    var recent = (callTimes || []).filter(function (t) { return now - t < WINDOW_MS; }).sort(function (a, b) { return a - b; });
    if (recent.length < CALLS_PER_MINUTE) return 0;
    return recent[recent.length - CALLS_PER_MINUTE] + WINDOW_MS - now;
  }

  /** The error code an HTTP answer means, or null for a 200. */
  function errorOf(status) {
    if (status === 200) return null;
    if (status === 401 || status === 403) return 'bad_key';
    if (status === 429) return 'rate_limited';
    return 'unavailable';
  }

  return {
    BATCH_MAX: BATCH_MAX,
    CALLS_PER_MINUTE: CALLS_PER_MINUTE,
    WINDOW_MS: WINDOW_MS,
    INCLUDE: INCLUDE,
    FIELDS: FIELDS,
    FIGURES: FIGURES,
    DATE_FIELDS: DATE_FIELDS,
    langOf: langOf,
    message: message,
    normalizeKbo: normalizeKbo,
    isBlank: isBlank,
    resolveField: resolveField,
    resolveFigure: resolveFigure,
    requestBody: requestBody,
    chunk: chunk,
    distinctNumbers: distinctNumbers,
    compact: compact,
    ingest: ingest,
    value: value,
    figure: figure,
    yearOf: yearOf,
    waitMs: waitMs,
    errorOf: errorOf
  };
}));

/*
 * The Google Sheets part: custom functions, the Checked menu, the cache and the
 * batching. Served after checked-core.js as one Apps Script file.
 *
 * Your API key is kept in this script's properties (Checked, API-sleutel
 * instellen), never in a cell and never in an address. Everyone who can edit
 * this spreadsheet's script can read those properties: use a key of your own
 * and remove it (Checked, API-sleutel verwijderen) before you share the file.
 */

var CHECKED_KEY_PROPERTY = 'CHECKED_API_KEY';
var CHECKED_LANG_PROPERTY = 'CHECKED_LANG';
var CHECKED_GEN_PROPERTY = 'CHECKED_CACHE_GEN';
/** Six hours, the longest the script cache keeps a value. */
var CHECKED_FOUND_TTL = 21600;
/** An unknown number is asked again after an hour. */
var CHECKED_MISSING_TTL = 3600;
/** How long a cell waits for its neighbours to join its batch. */
var CHECKED_JOIN_MS = 800;
/** A custom function has thirty seconds; stop well before. */
var CHECKED_BUDGET_MS = 25000;

/**
 * One field of a company: name, status, in_register, legal_form, nace, activity, zip,
 * city, start_date, score, score_band, risk, probability, last_fiscal_year, filings or url.
 *
 * @param {string|number|Array<Array<string|number>>} number Enterprise number, or a range of them.
 * @param {string} field The field, for example "status" or "score".
 * @return The value, or a column of values for a range.
 * @customfunction
 */
function CHECKED(number, field) {
  var key = CheckedCore.resolveField(field);
  if (!key) throw new Error(CheckedCore.message('bad_field', checkedLang_()));
  return checkedEach_(number, function (rec, lang) { return checkedCell_(CheckedCore.value(rec, key, lang), key); });
}

/**
 * The name of a company.
 *
 * @param {string|number|Array<Array<string|number>>} number Enterprise number, or a range of them.
 * @return The name.
 * @customfunction
 */
function CHECKED_NAME(number) {
  return checkedEach_(number, function (rec, lang) { return checkedCell_(CheckedCore.value(rec, 'name', lang), 'name'); });
}

/**
 * The status of a company in the register: active, inactive, or what the register records.
 *
 * @param {string|number|Array<Array<string|number>>} number Enterprise number, or a range of them.
 * @return The status in words.
 * @customfunction
 */
function CHECKED_STATUS(number) {
  return checkedEach_(number, function (rec, lang) { return checkedCell_(CheckedCore.value(rec, 'status', lang), 'status'); });
}

/**
 * The Checked score, 0 to 100, as in the company file: higher is healthier. Empty without a score.
 *
 * @param {string|number|Array<Array<string|number>>} number Enterprise number, or a range of them.
 * @return The score.
 * @customfunction
 */
function CHECKED_SCORE(number) {
  return checkedEach_(number, function (rec, lang) { return checkedCell_(CheckedCore.value(rec, 'score', lang), 'score'); });
}

/**
 * A key figure from the annual accounts: revenue (turnover), ebitda, netprofit, equity, totalassets,
 * totaldebt, staffcosts, fte, solvency, currentratio, debtequity, netmargin, ebitdamargin, roe or roa.
 *
 * @param {string|number|Array<Array<string|number>>} number Enterprise number, or a range of them.
 * @param {string} figure The figure, for example "turnover".
 * @param {number} year Optional fiscal year; the newest when left out.
 * @return The figure, empty when it was not filed.
 * @customfunction
 */
function CHECKED_FIGURE(number, figure, year) {
  var lang = checkedLang_();
  var key = CheckedCore.resolveFigure(figure);
  if (!key) throw new Error(CheckedCore.message('bad_figure', lang));
  var y = CheckedCore.yearOf(year);
  if (typeof y === 'number' && isNaN(y)) throw new Error(CheckedCore.message('bad_year', lang));
  return checkedEach_(number, function (rec) { return checkedCell_(CheckedCore.figure(rec, key, y), key); });
}

// ---------------------------------------------------------------- the menu

function onOpen() {
  var lang = checkedLang_();
  var t = function (nl, fr, en) { return lang === 'fr' ? fr : lang === 'en' ? en : nl; };
  SpreadsheetApp.getUi().createMenu('Checked')
    .addItem(t('API-sleutel instellen', 'Définir la clé API', 'Set API key'), 'checkedSetKey')
    .addItem(t('Verbinding testen', 'Tester la connexion', 'Test the connection'), 'checkedTest')
    .addItem(t('Gegevens vernieuwen', 'Actualiser les données', 'Refresh the data'), 'checkedRefresh')
    .addSeparator()
    .addItem(t('Taal: Nederlands', 'Langue : néerlandais', 'Language: Dutch'), 'checkedLangNl')
    .addItem(t('Taal: Frans', 'Langue : français', 'Language: French'), 'checkedLangFr')
    .addItem(t('Taal: Engels', 'Langue : anglais', 'Language: English'), 'checkedLangEn')
    .addSeparator()
    .addItem(t('API-sleutel verwijderen', 'Supprimer la clé API', 'Remove API key'), 'checkedRemoveKey')
    .addToUi();
}

function checkedSetKey() {
  var ui = SpreadsheetApp.getUi();
  var lang = checkedLang_();
  var t = function (nl, fr, en) { return lang === 'fr' ? fr : lang === 'en' ? en : nl; };
  var answer = ui.prompt('Checked',
    t('Plak uw API-sleutel. U maakt er een aan op ' + CHECKED_ORIGIN + '/account/api-keys.',
      'Collez votre clé API. Vous la créez sur ' + CHECKED_ORIGIN + '/fr/account/api-keys.',
      'Paste your API key. You create one at ' + CHECKED_ORIGIN + '/en/account/api-keys.'),
    ui.ButtonSet.OK_CANCEL);
  if (answer.getSelectedButton() !== ui.Button.OK) return;
  var key = answer.getResponseText().trim();
  if (!key) return;
  var status = checkedPing_(key);
  if (status !== 200) {
    ui.alert('Checked', CheckedCore.message(CheckedCore.errorOf(status) || 'unavailable', lang), ui.ButtonSet.OK);
    return;
  }
  PropertiesService.getScriptProperties().setProperty(CHECKED_KEY_PROPERTY, key);
  checkedRefresh();
  ui.alert('Checked', t('De sleutel werkt en is bewaard. De functies zijn klaar voor gebruik.',
    'La clé fonctionne et est enregistrée. Les fonctions sont prêtes.',
    'The key works and is saved. The functions are ready.'), ui.ButtonSet.OK);
}

function checkedTest() {
  var ui = SpreadsheetApp.getUi();
  var lang = checkedLang_();
  var key = PropertiesService.getScriptProperties().getProperty(CHECKED_KEY_PROPERTY);
  if (!key) { ui.alert('Checked', CheckedCore.message('no_key', lang), ui.ButtonSet.OK); return; }
  var status = checkedPing_(key);
  ui.alert('Checked', status === 200
    ? (lang === 'fr' ? 'La connexion fonctionne.' : lang === 'en' ? 'The connection works.' : 'De verbinding werkt.')
    : CheckedCore.message(CheckedCore.errorOf(status) || 'unavailable', lang), ui.ButtonSet.OK);
}

/** New cache keys, then every Checked formula calculates again. */
function checkedRefresh() {
  PropertiesService.getScriptProperties().setProperty(CHECKED_GEN_PROPERTY, String(new Date().getTime()));
  checkedRecalc_();
}

function checkedRemoveKey() {
  PropertiesService.getScriptProperties().deleteProperty(CHECKED_KEY_PROPERTY);
  PropertiesService.getScriptProperties().setProperty(CHECKED_GEN_PROPERTY, String(new Date().getTime()));
}

/**
 * Sheets keeps a custom function's answer as long as its arguments do not
 * change, so a refresh takes every Checked formula out and puts it back.
 */
function checkedRecalc_() {
  SpreadsheetApp.getActiveSpreadsheet().getSheets().forEach(function (sheet) {
    var range = sheet.getDataRange();
    var formulas = range.getFormulas();
    var hits = [];
    formulas.forEach(function (row, r) {
      row.forEach(function (f, c) { if (/\bCHECKED(_[A-Z]+)?\s*\(/i.test(f)) hits.push([r + 1, c + 1, f]); });
    });
    if (hits.length === 0) return;
    hits.forEach(function (h) { range.getCell(h[0], h[1]).setValue(''); });
    SpreadsheetApp.flush();
    hits.forEach(function (h) { range.getCell(h[0], h[1]).setFormula(h[2]); });
  });
}

function checkedLangNl() { checkedSetLang_('nl'); }
function checkedLangFr() { checkedSetLang_('fr'); }
function checkedLangEn() { checkedSetLang_('en'); }

function checkedSetLang_(lang) {
  PropertiesService.getScriptProperties().setProperty(CHECKED_LANG_PROPERTY, lang);
  onOpen();
  checkedRecalc_();
}

/** The usage call costs nothing and says whether the key is accepted. */
function checkedPing_(key) {
  var res = UrlFetchApp.fetch(CHECKED_ORIGIN + '/api/v1/usage', {
    method: 'get', headers: { 'X-Api-Key': key }, muteHttpExceptions: true
  });
  return res.getResponseCode();
}

// ---------------------------------------------------------------- the engine

function checkedLang_() {
  var set = PropertiesService.getScriptProperties().getProperty(CHECKED_LANG_PROPERTY);
  if (set) return set;
  try { return CheckedCore.langOf(SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetLocale()); }
  catch (e) { return 'nl'; }
}

/** A value as a cell: nothing is an empty cell, a date field a date. */
function checkedCell_(v, key) {
  if (v === null || v === undefined) return '';
  if (CheckedCore.DATE_FIELDS[key] && typeof v === 'string') {
    var p = v.split('-');
    if (p.length === 3) return new Date(Number(p[0]), Number(p[1]) - 1, Number(p[2]));
  }
  return v;
}

/**
 * Runs pick for one number or for every cell of a range, with every number of
 * the range fetched together. A bad cell in a range reads its message; a bad
 * single number throws, which the sheet shows as the cell's error.
 */
function checkedEach_(input, pick) {
  var lang = checkedLang_();
  var started = new Date().getTime();
  if (Array.isArray(input)) {
    var flat = [];
    input.forEach(function (row) { row.forEach(function (cell) { flat.push(cell); }); });
    var records = checkedFetch_(CheckedCore.distinctNumbers(flat), lang, started);
    return input.map(function (row) {
      return row.map(function (cell) {
        if (CheckedCore.isBlank(cell)) return '';
        var k = CheckedCore.normalizeKbo(cell);
        if (!k) return CheckedCore.message('bad_kbo', lang);
        var rec = records[k];
        return rec ? pick(rec, lang) : CheckedCore.message('unknown_kbo', lang);
      });
    });
  }
  if (CheckedCore.isBlank(input)) return '';
  var kbo = CheckedCore.normalizeKbo(input);
  if (!kbo) throw new Error(CheckedCore.message('bad_kbo', lang));
  var rec = checkedFetch_([kbo], lang, started)[kbo];
  if (!rec) throw new Error(CheckedCore.message('unknown_kbo', lang));
  return pick(rec, lang);
}

function checkedCacheKey_(gen, lang, kbo) { return 'ck1:' + gen + ':' + lang + ':' + kbo; }

/**
 * The companies behind numbers: from the cache, else from the API. Cells that
 * calculate at the same moment share their calls: a cell puts its numbers in a
 * queue, waits a moment, and whichever cell then holds the lock fetches every
 * queued number at once, a hundred per call and at most ten calls a minute.
 */
function checkedFetch_(numbers, lang, started) {
  var props = PropertiesService.getScriptProperties();
  var key = props.getProperty(CHECKED_KEY_PROPERTY);
  if (!key) throw new Error(CheckedCore.message('no_key', lang));
  var gen = props.getProperty(CHECKED_GEN_PROPERTY) || '0';
  var cache = CacheService.getScriptCache();

  var result = {};
  var missing = checkedFromCache_(cache, gen, lang, numbers, result);
  if (missing.length === 0) return result;

  var lock = LockService.getScriptLock();
  var queueKey = 'ckq:' + gen + ':' + lang;
  if (missing.length < CheckedCore.BATCH_MAX && lock.tryLock(2000)) {
    try {
      var queued = JSON.parse(cache.get(queueKey) || '[]');
      cache.put(queueKey, JSON.stringify(CheckedCore.distinctNumbers(queued.concat(missing)).slice(0, 1000)), 120);
    } finally { lock.releaseLock(); }
    Utilities.sleep(CHECKED_JOIN_MS);
    missing = checkedFromCache_(cache, gen, lang, missing, result);
    if (missing.length === 0) return result;
  }

  if (!lock.tryLock(Math.max(1000, CHECKED_BUDGET_MS - (new Date().getTime() - started)))) {
    throw new Error(CheckedCore.message('busy', lang));
  }
  try {
    // Another cell may have fetched ours while we waited for the lock.
    missing = checkedFromCache_(cache, gen, lang, missing, result);
    if (missing.length === 0) return result;
    var queue = JSON.parse(cache.get(queueKey) || '[]');
    cache.remove(queueKey);
    var todo = CheckedCore.distinctNumbers(missing.concat(queue));
    var batches = CheckedCore.chunk(todo, CheckedCore.BATCH_MAX);
    for (var i = 0; i < batches.length; i++) {
      var mine = batches[i].some(function (k) { return missing.indexOf(k) >= 0; });
      if (!mine && i > 0) continue;
      checkedThrottle_(cache, lang, started);
      var res = UrlFetchApp.fetch(CHECKED_ORIGIN + '/api/v1/companies/batch', {
        method: 'post',
        contentType: 'application/json',
        headers: { 'X-Api-Key': key },
        payload: JSON.stringify(CheckedCore.requestBody(batches[i], lang)),
        muteHttpExceptions: true
      });
      var err = CheckedCore.errorOf(res.getResponseCode());
      if (err) throw new Error(CheckedCore.message(err, lang));
      var got = CheckedCore.ingest(JSON.parse(res.getContentText()));
      var found = {}, notFound = {};
      Object.keys(got).forEach(function (k) {
        if (got[k]) found[checkedCacheKey_(gen, lang, k)] = JSON.stringify(got[k]);
        else notFound[checkedCacheKey_(gen, lang, k)] = '0';
        if (missing.indexOf(k) >= 0) result[k] = got[k];
      });
      if (Object.keys(found).length) cache.putAll(found, CHECKED_FOUND_TTL);
      if (Object.keys(notFound).length) cache.putAll(notFound, CHECKED_MISSING_TTL);
    }
    return result;
  } finally { lock.releaseLock(); }
}

/** Fills result from the cache and answers the numbers it did not hold. */
function checkedFromCache_(cache, gen, lang, numbers, result) {
  var keys = numbers.map(function (k) { return checkedCacheKey_(gen, lang, k); });
  var hit = keys.length ? cache.getAll(keys) : {};
  var missing = [];
  numbers.forEach(function (k, i) {
    var v = hit[keys[i]];
    if (v === undefined || v === null) missing.push(k);
    else result[k] = v === '0' ? null : JSON.parse(v);
  });
  return missing;
}

/** Waits for a free slot of the key's ten batch calls a minute, or gives up in time. */
function checkedThrottle_(cache, lang, started) {
  var times = JSON.parse(cache.get('ckt') || '[]');
  var now = new Date().getTime();
  var wait = CheckedCore.waitMs(times, now);
  if (wait > 0) {
    if (now + wait - started > CHECKED_BUDGET_MS) throw new Error(CheckedCore.message('rate_limited', lang));
    Utilities.sleep(wait);
    now = new Date().getTime();
  }
  times = times.filter(function (t) { return now - t < CheckedCore.WINDOW_MS; });
  times.push(now);
  cache.put('ckt', JSON.stringify(times), 120);
}