// Radar for Google Sheets - https://radarbg.com/no-code // Setup (2 minutes): Extensions > Apps Script > paste this whole file > put your key below > Save (disk icon). // Then in any cell: =RADAR("iban-validator", A2) one answer (default field) // =RADAR("iban-validator", A2, "bankName") one chosen field // =RADAR_ROW("iban-validator", A2) all answer columns in one row // =RADAR_HEADERS("iban-validator") the column names for RADAR_ROW // A range works too: =RADAR("eu-vat-number-validation", A2:A40) (up to about 50 rows per formula; // bigger lists: upload the file at https://radarbg.com/no-code). 1 answered value = 1 call; the same value again within 24 h is free. const RADAR_KEY = "PASTE_YOUR_KEY_HERE"; // free key: https://radarbg.com/no-code#key (keep it private) const RADAR_URL = "https://radarbg.com/v1/cell/"; /** * Looks up a value (or a range of values) with a Radar API. * @param {string} api API name, for example "iban-validator" or "eu-vat-number-validation". * @param {string} value A cell or a one-column range. * @param {string} field Optional answer field, for example "bankName". Empty = the main answer. * @return The answer. * @customfunction */ function RADAR(api, value, field) { return radar_(api, value, field || "", false); } /** * All answer columns for a value (or a range) in one row each. * @param {string} api API name, for example "iban-validator". * @param {string} value A cell or a one-column range. * @return One row of answers per value. * @customfunction */ function RADAR_ROW(api, value) { return radar_(api, value, "", true); } /** * Column names that RADAR_ROW returns. * @param {string} api API name. * @return One row of column names. * @customfunction */ function RADAR_HEADERS(api) { const r = UrlFetchApp.fetch(RADAR_URL + encodeURIComponent(api) + "?format=headers", {muteHttpExceptions: true}); const j = JSON.parse(r.getContentText()); return j.ok ? [j.cols] : "ERROR: " + j.error; } function radar_(api, value, field, row) { if (!RADAR_KEY || RADAR_KEY.indexOf("rk_") !== 0) return "ERROR: put your key in RADAR_KEY (Extensions > Apps Script)"; const many = Array.isArray(value); const vals = many ? value.map(function (r) { return Array.isArray(r) ? r[0] : r; }) : [value]; const cache = CacheService.getScriptCache(); const out = [], reqs = [], where = [], ckeys = []; vals.forEach(function (v, i) { v = String(v === null || v === undefined ? "" : v).trim(); if (!v) { out[i] = row ? [""] : ""; return; } const url = RADAR_URL + encodeURIComponent(api) + "?format=json&field=" + encodeURIComponent(field) + "&value=" + encodeURIComponent(v); const ck = Utilities.base64Encode(Utilities.computeDigest(Utilities.DigestAlgorithm.MD5, url + (row ? "#row" : ""))); const hit = cache.get(ck); if (hit !== null) { out[i] = JSON.parse(hit); return; } reqs.push({url: url, headers: {"X-API-Key": RADAR_KEY}, muteHttpExceptions: true}); where.push(i); ckeys.push(ck); }); if (reqs.length) { const res = UrlFetchApp.fetchAll(reqs); res.forEach(function (r, n) { let ans; try { const j = JSON.parse(r.getContentText()); ans = j.ok ? (row ? j.row : j.value) : (row ? ["ERROR: " + j.error] : "ERROR: " + j.error); if (j.ok) cache.put(ckeys[n], JSON.stringify(ans), 21600); } catch (e) { ans = row ? ["ERROR: no answer"] : "ERROR: no answer"; } out[where[n]] = ans; }); } if (!many) return row ? [out[0]] : out[0]; return out.map(function (a) { return row ? a : [a]; }); }