How BulkVAT Works

A transparent overview of BulkVAT's processing pipeline, mathematical checksum engine, official tax authority integrations, and execution budget management.

1. Data Cleaning & Normalization

Raw spreadsheets frequently contain formatting noise that breaks strict tax databases: embedded spaces, dashes, dots, lowercase characters, full-width Unicode numerals, or missing country prefixes.

BulkVAT applies deterministic normalization before any validation attempt:

• Whitespace & Delimiter Stripping: Removes spaces, tabs, line breaks, hyphens, and punctuation while preserving valid alphanumeric sequences.
• Unicode Normalization: Converts full-width digits and non-standard characters to standard ASCII via NFKC normalization.
• Prefix Standardisation: Detects ISO country codes, handles Greek prefix aliases (normalizing GR to the official VIES prefix EL), and supports Northern Ireland XI goods prefixes.
• Country Hinting: If numbers lack a 2-character country prefix, the user can supply a default country hint in the sidebar to test ambiguous domestic numbers.

2. Offline Algorithmic Checksum Validation

Before making any network request, BulkVAT runs the cleaned string through national mathematical checksum algorithms. If a number fails its mathematical check, it is immediately flagged as invalid without querying external APIs, saving quota and network bandwidth.

3. Live European Commission VIES Lookups

For European Union member states, BulkVAT connects directly to the European Commission's official VIES REST API (https://ec.europa.eu/taxation_customs/vies/rest-api).

The live service validates whether the tax ID is currently active and registered for intra-Community trade, returning:

• Validity State: Active or Invalid in the national registry.
• Company Name & Address: Official registered legal name and registered address (where disclosed by national law).
• Official Consultation Number: When requester VAT credentials are configured in settings, VIES generates a cryptographic audit confirmation string (requestIdentifier) proving the check occurred on that exact date.

4. Resilient Error Handling & Outage Detection

National tax registries (such as the Italian Agenzia delle Entrate or Spanish AEAT) undergo regular weekend maintenance. Standard validators often report these downtime periods as "Invalid VAT Number," causing teams to reject legitimate invoices.

BulkVAT accurately classifies upstream downtime:

• Member State Outages: Reported as MS UNAVAILABLE so your spreadsheet accurately reflects technical downtime rather than false invalidity.
• Rate Limiting & Server Congestion: Handled with exponential backoff and randomized jitter (HTTP 429, 500, 502, 503, 504) to avoid dropping batches.

5. 4.5-Minute Budget Guard & Large Spreadsheet Resumption

Google Apps Script terminates any execution exceeding 6 minutes (360 seconds). A spreadsheet containing 5,000 rows cannot complete in a single execution window.

BulkVAT solves this with an automated state machine:

• At 4.5 minutes (270,000 ms), the batch halts gracefully.
• Current row index and accumulated statistics are checkpointed into PropertiesService.getUserProperties().
• A Google Apps Script time-driven continuation trigger is automatically registered.
• The script resumes seamlessly in a fresh 6-minute container, with zero duplicate rows and zero dropped rows.
• Triggers are automatically deleted when the run finishes or is cancelled.

6. Audit Trail & CSV Export

Tax authorities require contemporaneous evidence to justify zero-rated intra-community supplies under Article 138 of the EU VAT Directive. BulkVAT writes all consultation numbers, timestamps, and address data directly to your sheet, and provides an instant RFC 4180 compliant CSV export for your permanent compliance archive.