Check your supplier list right in the spreadsheet
Four formulas for a supplier sheet: =FACTLINE_IBAN(A2), =FACTLINE_VAT(B2), =FACTLINE_SANCTIONS(C2) and =FACTLINE_COMPANY(D2; "NL"). Each cell shows a plain answer (VALID, CLEAR, PASS, REVIEW) from official records. Answers are cached for 6 hours, so re-opening the sheet costs nothing.
Set it up in two minutes
- Get a free API key (2,000 credits a month) on /business.
- In your sheet: Extensions > Apps Script. Delete what is there, paste the script below, click save.
- Choose
setFactlineKeyin the function list, click Run, allow access, paste your key. - Back in the sheet, type
=FACTLINE_IBAN(A2)and drag it down.
Credits per cell: IBAN 1, VAT 2, sanctions 3, company check 25 (1 credit = USD 0.002). The script talks only to https://factline.dev with your key; it stores the key in your own Google account (user properties).
The script
// 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(", ") : "");
}
Prefer no code at all? Paste a list on /monitor (screen now, watch by email), or forward an invoice to check@factline.dev.