Check an IBAN list in Google Sheets: bank, BIC and results to review

A supplier file contains IBANs whose format and bank references you want to check. The IBANforge script for Google Sheets fills five columns alongside the original data. It helps prepare a review of bank details; it verifies neither the account holder nor a payment.

Start in a test spreadsheet you own, using the examples below. Manual installation uses Apps Script: you do not need to develop code to use the formulas, but the script remains a copy to maintain. It is not published on the Google Workspace Marketplace.

Install and try a small list

1. Download the three Apps Script files. Open Extensions → Apps Script in your test spreadsheet.
2. Replace Code.gs, add an HTML file called Sidebar and paste in Sidebar.html. In project settings, show appsscript.json and replace its contents with the supplied manifest. Save.
3. Run ibfShowSidebar from the editor. Review the requested permissions, then authorize the script if they suit your use. In the spreadsheet sidebar, enter your IBANforge key and choose Test and save. Do not put the key in a cell. The API key guide explains how to obtain one and check its effective limit.
4. Copy the following six lines into A2:A7, keeping the empty line. These are documentation examples, never payment instructions.

DE65100700000123456789
DE65 1007 0000 0123 4567 89
 
DE65100700000123456798
CH3130000000000000001
TEST

5. Leave B2:F7 empty and enter =IBAN_CHECK(A2:A7) in B2. The formula fills five columns, B to F, and preserves the six rows, including the empty row. The first two entries are the same string after spaces are removed.

If pasting skips the empty line, restore it before testing. Keep the original column and put your own review notes in G or beyond. A formula over a range avoids making a separate Apps Script call for every row.

Read the five columns

verified means a reference was found. verified, retired also signals a code marked retired: review the details and source without substituting a bank code yourself. not_in_register (not_allocated) reports absence from the exhaustive register used, as of its date; it does not establish fraud. An unknown result remains unknown. checksum_failed reports a failed check, not its cause.

The Sheets summary omits source dates, authoritative, register details and any successor code. A bank name therefore does not always mean “confirmed by the national register”. To document a review decision, retain the complete response using the validation guide, or use the supplier-file workflow. Register values can change; this trial does not freeze a bank result or source date.

For one result column, use =IBAN_VALID(A2:A7), =IBAN_BANK(A2:A7) or =IBAN_BIC(A2:A7). The aliases IBAN_VALIDE, IBAN_BANQUE, IBAN_CONTROLE, IBAN_GUELTIG, IBAN_BANKNAME and IBAN_PRUEFUNG call the same functions. IBAN_CHECK expects a single input column.

Calls, caching and usage

The batch endpoint requires a key. The script removes spaces, uppercases strings, skips empty cells and deduplicates values within one execution. It sends values missing from the cache in batches of at most 100. Each transmitted IBAN consumes one unit, even if invalid; it is not one unit per HTTP request. Pricing and the key's usage report describe the applicable offer and balance.

The script requests a six-hour cache. Google can evict it sooner, so recalculation can consume more units. Concurrent formulas do not guarantee a single call. If a later batch fails, earlier batches may already have consumed units; this execution writes its cache only at the end. There is no automatic purchase or retry.

Data sent and shared spreadsheets

The functions send their input strings, the key in the authentication header and source=sheets to api.ibanforge.com. The sidebar also requests the key's usage. Pass only the IBAN column, not names or comments. Apps Script makes requests through Google's infrastructure, not just your browser.

The key is stored in the Apps Script project's user properties. For custom functions, Google documents access to the spreadsheet owner's properties; editors can also change the bound script. This mechanism does not guarantee a separate key or quota for each collaborator. Run the pilot in your own copy and check access before sharing an active spreadsheet. See Google's rules for custom functions and sharing.

If the sheet shows an error

A failed request is not an invalid IBAN. Account for calls already made before trying again. For larger recurring jobs, the integration recipes help choose a workflow that retains complete responses. The script source is available to inspect.