// Factline for Google Sheets. Paste in Extensions > Apps Script, save, then run setFactlineKey once. // Results are cached for 6 hours, so re-opening the sheet does not use credits again. const FACTLINE = "https://factline.dev"; function setFactlineKey() { const key = Browser.inputBox("Your Factline API key (fl_live_...)"); if (key && key !== "cancel") PropertiesService.getUserProperties().setProperty("FACTLINE_KEY", key.trim()); } function factline_(path, params) { const q = Object.keys(params).filter(function (k) { return params[k] !== "" && params[k] != null; }) .map(function (k) { return k + "=" + encodeURIComponent(String(params[k]).trim()); }).join("&"); const url = FACTLINE + path + "?" + q; const cache = CacheService.getUserCache(), hit = cache.get(url); if (hit) return JSON.parse(hit); const key = PropertiesService.getUserProperties().getProperty("FACTLINE_KEY"); if (!key) throw new Error("Run setFactlineKey first (Extensions > Apps Script > setFactlineKey > Run)."); const r = UrlFetchApp.fetch(url, { headers: { "x-api-key": key }, muteHttpExceptions: true }); const j = JSON.parse(r.getContentText()); if (r.getResponseCode() >= 400) return { error: j.message || ("HTTP " + r.getResponseCode()) }; cache.put(url, JSON.stringify(j), 21600); return j; } /** Is this IBAN valid? =FACTLINE_IBAN(A2) gives VALID or INVALID with the bank. @customfunction */ function FACTLINE_IBAN(iban) { const j = factline_("/v1/iban/check", { iban: iban }); return j.error ? "ERROR: " + j.error : (j.valid ? "VALID" : "INVALID") + (j.bank ? " (" + j.bank + ")" : ""); } /** Does this EU VAT number exist, and for which company? =FACTLINE_VAT(B2) @customfunction */ function FACTLINE_VAT(vat) { const j = factline_("/v1/eu/vat", { vat: vat }); return j.error ? "ERROR: " + j.error : (j.valid ? "VALID: " + (j.name || "") : "NOT VALID") + (j.stale ? " (cached, source down)" : ""); } /** Possible sanctions match (EU, UN, UK, US OFAC)? =FACTLINE_SANCTIONS(C2) gives CLEAR or REVIEW. @customfunction */ function FACTLINE_SANCTIONS(name) { const j = factline_("/v1/sanctions/screen", { name: name }); if (j.error) return "ERROR: " + j.error; const m = (j.matches || [])[0]; return j.possible_match ? "REVIEW: " + (m ? m.matched_name + " (" + m.score + ")" : "possible match") : "CLEAR"; } /** Company check in one call: register, status, VAT, sanctions. =FACTLINE_COMPANY(D2, "NL") gives PASS, REVIEW or BLOCK_REVIEW. @customfunction */ function FACTLINE_COMPANY(name, country, vat) { const j = factline_("/v1/kyb/check", { name: name, country: country, vat: vat }); if (j.error) return "ERROR: " + j.error; return String(j.verdict || "").toUpperCase() + (j.company && j.company.status ? " (" + j.company.status + ")" : "") + ((j.flags || []).length ? ": " + j.flags.join(", ") : ""); }