IBAN Checker for Google Sheets
Check a column of IBANs without leaving the spreadsheet: whether each one is valid, why it failed, and the bank and BIC behind it. The add-on sends each IBAN to the same validation engine as this site, national check digits included, so a sheet and the IBAN checker never disagree about an IBAN.
In Google's review since : Google first verifies the add-on's access to your Google account, then reviews its Google Workspace Marketplace listing. Once both are approved, it installs from the Marketplace in one click and this page links to the listing.
How do I set it up?
- Install the add-on from the Google Workspace Marketplace.
- Get an API key on the API page. The free key includes 100 checks a month.
- In any spreadsheet open Extensions > IBAN Checker by ibanchecker.cash > Settings and API key, paste the key and save it. It is kept in your Google account, not in the spreadsheet.
- In every other spreadsheet, open Extensions > IBAN Checker by ibanchecker.cash once before typing a formula. Google Sheets loads an add-on's formulas in a spreadsheet only after the add-on has been used there.
Which formulas does it add?
Each formula takes a single cell or a range and returns a result of the same shape.
| Formula | Returns | Example |
|---|---|---|
=IBAN_VALID(A2:A100) | TRUE or FALSE | TRUE |
=IBAN_ERROR(A2:A100) | Why the IBAN failed; empty when it is valid | Check digit verification failed. The IBAN contains a transcription error. |
=IBAN_BANK(A2:A100) | The bank's name | Commerzbank |
=IBAN_BIC(A2:A100) | The bank's BIC | COBADEFFXXX |
=IBAN_CHECK(A2:A100) | Five columns: valid, error, country, bank, BIC | TRUE, empty, Germany, Commerzbank, COBADEFFXXX |
Spaces and lower case are accepted. A cell without any digit, such as a header that reads IBAN, is skipped and not counted. One formula checks at most 300 IBANs that were not checked in the last six hours; for a longer list use the menu command below.
How do I check a long list at once?
Select the IBANs and run Extensions > IBAN Checker by ibanchecker.cash > Check selected IBANs. The results are written as plain values in the five columns to the right of the selection, with a header row when the selection sits under one, and they are not recalculated when the sheet opens again. Nothing is overwritten: if those columns already hold data, the add-on asks first and inserts five new columns instead. One run takes up to 5,000 rows.
How many requests does it use?
Each IBAN checked counts as one request on your API key, the same as a call to POST /api/v1/validate. An IBAN that appears several times in one formula or run is sent once. Results are kept for six hours in your own Google account, so the same IBAN checked again within six hours, by another formula or a recalculation, is not counted. The free key covers 100 checks a month and the Basic plan 10,000; the other plans are on the pricing page.
Whose key does a shared spreadsheet use?
Google runs spreadsheet formulas with the settings of the spreadsheet's owner, so in a shared sheet the formulas count against the owner's key. The menu command always uses the key of the person who runs it.
What does the add-on have access to?
It asks Google for three permissions and nothing more:
- to read and write the spreadsheet it is used in, and no other file (
spreadsheets.currentonly); - to send the IBANs you check to
https://ibanchecker.cash/api/v1/validate(script.external_request); - to show its settings sidebar (
script.container.ui).
The API validates each IBAN in memory and stores none of them. The cache in your Google account is keyed by a SHA-256 hash of the IBAN and holds only the result. We receive nothing else from your Google account: not your name, your email address, other cells or other files. The details are in the privacy policy.
What do the messages in a cell mean?
| Message | What it means |
|---|---|
| Invalid IBAN | The IBAN failed validation, so it has no bank. IBAN_ERROR says why. |
| Not in our bank directory | The IBAN is valid, but the bank its code points to is not in our directory yet. The gap is ours, not an error in your data. |
| Monthly limit of your API key reached | Your key has used this month's requests. IBANs answered before the limit keep their results. |
| ibanchecker.cash could not be reached | The request did not get an answer from the API. Results are not cached until it does; recalculate later. |
| Not checked: Google's time limit was reached | Google stops a menu run after six minutes. Select the rows that are marked and run the check again. |
| #ERROR! Add your ibanchecker.cash API key | No key is saved yet. Open Extensions > IBAN Checker by ibanchecker.cash > Settings and API key. |
| #NAME? Unknown function | The add-on has not been used in this spreadsheet yet. Open Extensions > IBAN Checker by ibanchecker.cash once, then reload the spreadsheet. |
| Authorization is required to perform that action | Shown in the sidebar when the browser is signed in to several Google accounts and the spreadsheet is open in one that is not the first. Google Apps Script sends the sidebar's request as the first account. Open the spreadsheet in a browser window signed in only to the account that installed the add-on. |
Where is the source code, and who do I ask for help?
The add-on is open source under the MIT license: github.com/koraykoylu/ibanchecker-sheets. Questions and problems go to [email protected] or through the contact page. Using it is covered by our terms of service.
Version 0.1.0.