Google Sheets guide

Validate IBANs in Google Sheets with Apps Script

Check a selected IBAN column from a spreadsheet menu and get a separate, timestamped report with validation status, bank name and BIC. Download the starter, add your own API token and run checks when you need them.

Download the Google Sheets starter ZIP

The ZIP includes Code.gs, an Apps Script manifest, setup instructions and local fixture tests. No libraries, deployment or spreadsheet add-on are required. You need a Google account with spreadsheet editing access and an IBAN-Test API token.

What this spreadsheet checks

The API checks IBAN format and checksum, with available bank directory information. A valid result does not establish account ownership, existence, payment eligibility or available funds. See the IBAN-Test API documentation for the current validation scope.

This starter uses an explicit IBAN-Test menu command. Editing a cell does not trigger a paid request. Each run accepts one contiguous column selection of up to 500 rows, containing no more than 10 distinct non-blank IBANs. These are starter limits, not your account allowance.

Spaces are removed and letters converted to uppercase for requests and duplicate detection. Original cells remain untouched. Repeated IBANs share one request within a run, but each source row appears in the report. Empty cells are skipped; headings and formulas must be excluded.

1. Install the starter in a spreadsheet

1. Create a private Google spreadsheet for your first test. Download and unpack the ZIP.
2. In Sheets, open Extensions → Apps Script. Replace the default contents of Code.gs with the downloaded file. If the project already contains useful scripts, use a fresh spreadsheet for this setup.
3. Open Project Settings in Apps Script and enable Show “appsscript.json” manifest file in editor. Return to the editor and replace that file with the supplied manifest.
4. Save the project, then reload the spreadsheet. The IBAN-Test menu appears through the script’s onOpen function. Google explains this mechanism in its custom menu guide.
5. Choose IBAN-Test → Save my API token. Complete Google’s authorization flow for the current spreadsheet and external requests, then run the command again if needed. Review the script and requested permissions before allowing access. Workspace policies may require administrator assistance; see Google’s authorization documentation.
6. Paste your API token from the IBAN-Test account dashboard into the prompt. Enter only the token, without Bearer. The prompt is not masked: avoid screen sharing while entering it.

2. Select and validate your IBANs

Put a heading such as IBAN in A1. Format your input cells as plain text and enter IBAN values below it. For an initial demonstration, use the documented example DE89370400440532013000, the spaced version DE89 3704 0044 0532 0130 00, and a deliberately changed checksum such as DE00370400440532013000.

Select only the populated input range, for example A2:A4, and choose IBAN-Test → Validate selected IBANs. This selection contains two unique normalized values, so the starter attempts two requests. Do not select the entire column, a heading or multiple separated ranges. Avoid editing the selected cells during a run.

After all requests return recognized results, the script creates a new tab beginning IBAN check, followed by its UTC timestamp and a short identifier. Open that tab and verify the COMPLETE marker in A1. Old reports remain historical snapshots and never update automatically.

3. Read the report

The report contains nine columns: source sheet, source row, original IBAN, normalized IBAN, status, API code, bank name, BIC and check time in UTC. Blank bank fields mean that no value was supplied; they do not turn a valid IBAN into an invalid one.

This abbreviated example shows the expected status mapping when the API returns the stated codes. It is illustrative, not a live authenticated test:

The starter accepts VALID only with HTTP 200, code 2100 and error: false. It accepts INVALID only for codes 3100, 3101 or 3102 with error: true. Authentication, quota, network and unexpected-response failures stop the run. See the documented result codes.

Keep tokens and spreadsheet data private

Your token is saved in the current user’s script properties, separate from cells and source code. Each collaborator must save their own token. Google documents this scope in PropertiesService.

User properties are not protection against malicious script changes. Spreadsheet editors can also edit its bound script; changed code could access your token when you next run it. Use this starter only with trusted editors, or keep a separate private workbook. Google describes these access relationships in its bound script guide.

The script sends normalized IBANs to IBAN-Test using HTTPS. Reports retain original IBANs and share the workbook’s access permissions. Limit sharing and retain reports only as long as needed. Remove my API token clears your local stored token; revoke a compromised token through your IBAN-Test account as well.

Quotas, failures and trying again

There are no automatic retries or cross-run caches. A new run rechecks every distinct value, and requests completed before a failure may already consume quota. The script uses Google URL Fetch; Apps Script quotas and execution limits also apply.

• Token or access error: verify the token and account status before rerunning.
• Quota or rate limit: wait until requests are permitted again. Respect any service-provided retry interval; do not repeatedly click the menu.
• Network, server or unreadable-response error: wait and retry the small selection manually. Persistent failures need investigation.
• No menu or permission denied: save the script, reload Sheets and check your editing access and Workspace restrictions.

A stopped validation produces no new completed report. A report-writing interruption may leave an incomplete tab: discard it, and never substitute an older report for the failed run. The script guards against overwriting input, concurrent script runs and formula injection in output text.

What has been tested

The download passes 46 local Node.js fixture tests with mocked Apps Script services, covering deduplication, limits, response handling, token storage, interrupted writes and formula escaping. It has not been tested inside a real Google Sheets account or against the authenticated API. Start with a small test workbook before using your operational data.