Google Sheets™ Add-on VIES Validator

Google Sheets™ Add-on VIES Validator

vies api google sheetsThe Google Sheets™ Add-on allows you to quickly and automatically check entities (contractors) using the Google Sheets™ spreadsheet in terms of their current status in the VIES system.

This documentation contains instructions on how to integrate the Google Sheets™ application with the VIES API system.

 

 

Installation and configuration

Installation

After entering the Google Workspace Marketplace website https://workspace.google.com/marketplace/app/vies_api_eu_vat_validation/1043052728267, click the Install button.

vies api google sheets add-on install

Selecting the Google Account for which the VIES Validator Google Sheets Add-on should be installed and available:

vies api google sheets add-on install account

On the next screen you can read the Privacy Policy and the applicable Terms of Service, as well as the scope of your data that we will process. To proceed to the next step of the installation, press the Continue button.

vies api google sheets add-on install permissions

Confirm that you have granted the necessary permissions for the add-on to function properly and press the Allow button:

vies api google sheets add-on install permissions allow

The VIES Validation add-on has been successfully installed on your account and will be available after launching Google Sheets in the Extensions tab. To continue, please click Next:

vies api google sheets add-on install extension

The final screen of the installer will prompt you to configure the Add-on. Clicking the “Complete additional setup now” link will open a new browser window with the current documentation page. Click the Done button and begin configuring the add-on as described in the Configuration section.

vies api google sheets add-on installed

Configuration

After installation, the plugin is set to the test environment by default (more details on Test API page). To be able to call out functions in the production environment, it is necessary to configure access to the viesapi.eu website once. To do this, go to any Google Sheets™ spreadsheet and perform the function:

=VIESAPI_CONNECT_PRODUCTION("identifier";"key")

where the function parameters are:

  • identifier – the parameter should contain the identifier (ID) generated on the account on the viesapi.eu website,
  • key – the parameter should contain the Key generated on the account on the viesapi.eu website.

Details on generating the identifier and key are described on the Documentation website.

In order to verify the correctness of the connection with a given API (test or production), the following function should be performed:

=VIESAPI_CONNECT_STATUS()

Which can return values:

  • SUCCESS: Production – if the add-in is correctly configured to work with the production environment,
  • SUCCESS: Test – if the add-in is correctly configured to work with the test environment,
  • ERROR: Connection requires configuration – in case of a wrong configuration.

If you prefer a sidebar configuration, click the Extensions tab, then select VIES API - EU VAT Validation and finally click Show sidebar to enable the sidebar:

vies api google sheets add-on configuration sidebar

Then go to the ID and Key tab to enter the ID and Key in the appropriate text fields, which are available after logging in to your VIES API account: https://viesapi.eu/sign-in/

vies api google sheets add-on configuration id and key

Usage

Before using the add-on, you should familiarize yourself with the specifics of its operation:

  • All function calls are executed in asynchronous mode, which does not cause the freezing (no response) effect of the Google Sheets™ application.
  • Adding (inserting) a row or column does not refresh all defined function calls.
  • Copying or moving cells that contain functions does not refresh defined function calls.
  • Deleting a row or column will refresh all defined function calls and send queries to viesApi.eu (only in the case of default Google Sheets™ settings). The description of changing the default settings is included in the Configuration chapter.
  • Attention! It is not recommended to run more than 500 parallel queries because Google Sheets™ is having trouble handling a large number of asynchronous calls.

Get company data from VIES

VIESAPI_VIES_DATA() – The function retrieves all available company data directly from the VIES system. The parameters should be:

  • European tax identification number (VAT EU),
  • (optional) FORMAT of the returned data (see: Tags that can be used as the FORMAT parameter).

=VIESAPI_VIES_DATA("PL7171642051")

If you only need company status in VIES, please use the following query:

=VIESAPI_VIES_DATA("PL7171642051";"$(Valid)")

If the returned status is Yes, it means that the company has a valid EU VAT number.

Tags available in the VIESAPI_VIES_DATA function that can be used as the FORMAT parameter:

  • $(UID) – unique identifier generated by the viesApi.eu service
  • $(CountryCode) – country code
  • $(VATNumber) – european VAT number
  • $(Valid) – response from the VIES service, informing about the current EU VAT status of the checked company (Yes/No)
  • $(TraderName) – company registration name
  • $(TraderCompanyType) – business type
  • $(TraderAddress) – trader address where the company is registered
  • $(ID) – unique identifier generated by the VIES system (consultation number)
  • $(Date) – the date the response was generated
  • $(Source) – the source of the data

Default format: "$(TraderName), $(TraderAddress)"

If the string "JSON" is given as the FORMAT parameter, the function will return the complete result in the form of a JSON string, which can be freely parsed and processed further:

=VIESAPI_VIES_DATA("PL7171642051";"JSON")

Get parsed company data from VIES

VIESAPI_VIES_DATA_PARSED() – The function retrieves all available company data directly from the VIES system. In addition, company’s name and address are returned as parsed data splitted into separate attributes. The parameters should be:

  • European tax identification number (VAT EU),
  • (optional) FORMAT of the returned data (see: Tags that can be used as the FORMAT parameter).

=VIESAPI_VIES_DATA_PARSED("PL7171642051")

If you only need company status in VIES, please use the following query:

=VIESAPI_VIES_DATA_PARSED("PL7171642051";"$(Valid)")

If the returned status is Yes, it means that the company has a valid EU VAT number.

Tags available in the VIESAPI_VIES_DATA_PARSED function that can be used as the FORMAT parameter:

  • $(UID) – unique identifier generated by the viesApi.eu service
  • $(CountryCode) – country code
  • $(VATNumber) – european VAT number
  • $(Valid) – response from the VIES service, informing about the current EU VAT status of the checked company (Yes/No)
  • $(TraderName) – company registration name
  • $(TraderNameComponents.Name) – company name from traderName (without legal form)
  • $(TraderNameComponents.LegalForm) – name of legal form extracted from traderName (without company name)
  • $(TraderNameComponents.LegalFormCanonicalId) – dictionary identifier of legal form
  • $(TraderNameComponents.LegalFormCanonicalName) – dictionary name of legal form
  • $(TraderCompanyType) – business type
  • $(TraderAddress) – trader address where the company is registered
  • $(TraderAddressComponents.Country) – name of the trader country in its national language
  • $(TraderAddressComponents.PostalCode) – postal code of the trader’s address
  • $(TraderAddressComponents.City) – city of the trader’s address
  • $(TraderAddressComponents.Street) – street of the trader’s address
  • $(TraderAddressComponents.StreetNumber) – street building number of the trader’s address
  • $(TraderAddressComponents.HouseNumber) – apartment/office number of the trader’s address
  • $(TraderAddressComponents.Other) – other address details (area name, building name, floor number, etc.)
  • $(ID) – unique identifier generated by the VIES system (consultation number)
  • $(Date) – the date the response was generated
  • $(Source) – the source of the data

Default format: "$(TraderName), $(TraderAddress)"

If the string "JSON" is given as the FORMAT parameter, the function will return the complete result in the form of a JSON string, which can be freely parsed and processed further:

=VIESAPI_VIES_DATA_PARSED("PL7171642051";"JSON")

User account status check (free of charge)

VIESAPI_ACCOUNT_STATUS() – The function returns basic information about the plan currently used by the user. The parameters should be:

  • (optional) FORMAT of the returned data (see: Tags that can be used as the FORMAT parameter)

=VIESAPI_ACCOUNT_STATUS()

Attention! Calling the function does not increase the number of queries made.

The tags available in the VIESAPI_ACCOUNT_STATUS function can be used as the FORMAT parameter:

  • $(UID) – unique identifier generated by the viesApi.eu service
  • $(BillingPlanName) – name of the current plan
  • $(SubscriptionPrice) – monthly subscription fee
  • $(ItemPrice) – cost of a single query outside the plan
  • $(ItemPriceStatus) – cost per query – VAT/VIES/Company activity status
  • $(ItemPriceParsed) – cost per query – VAT/VIES/Company activity status with parsed data
  • $(Limit) – maximum number of queries in the plan
  • $(RequestDelay) – minimum time interval between queries
  • $(DomainLimit) – maximum number of domains (API keys)
  • $(OverPlanAllowed) – ability to exceed the maximum number of queries in the plan
  • $(ExcelAddIn) – access to the Google Sheets™ and Microsoft Excel™ add-in
  • $(Stats) – access to query statistics
  • $(NIPMonitor) – access to monitoring of companies’ status: VAT/VIES/Activity status
  • $(FuncGetVIESData) – access to functions that verify company status in the VIES system
  • $(FuncGetVIESDataParsed) – access to functions that verify company status in the VIES system and parse the trader’s name and address
  • $(VIESDataCount) – number of VIES queries performed this month
  • $(VIESDataParsedCount) – number of VIES queries with parsed data performed this month
  • $(TotalCount) – total number of queries performed this month

Default FORMAT: "Name of the current tariff plan: $(BillingPlanName), Monthly subscription net price: $(SubscriptionPrice), Maximum number of inquiries in the plan: $(Limit), Total number of queries made in the current month: $(TotalCount)"

If the string "JSON" is given as the FORMAT parameter, the function will return the complete result in the form of a JSON string, which can be freely parsed and processed further.

VIES system status check (free of charge)

VIESAPI_VIES_STATUS() – This function allows you to verify the current status of the VIES system. It returns an overall status indicating availability, as well as a list of individual member countries together with the current availability status of their national systems. The parameters should be:

  • (optional) FORMAT of the returned data (see: Tags that can be used as the FORMAT parameter)

=VIESAPI_VIES_STATUS()

Attention! Calling the function does not increase the number of queries made.

The tags available in the VIESAPI_VIES_STATUS function can be used as the FORMAT parameter:

  • $(UID) – unique identifier generated by the viesApi.eu service
  • $(Available) – current availability of VIES system (Available, Partially available, Unavailable)
  • $(Countries) – list of individual member countries’ states (Available, Unavailable)
  • $(AT), $(FR), $(PL), etc. – availability status of a specific member country (use 2-letter upper case country code as tag value)

Default FORMAT: "$(Available)"

If the string "JSON" is given as the FORMAT parameter, the function will return the complete result in the form of a JSON string, which can be freely parsed and processed further.

Other useful functions (free of charge)

VIESAPI_ACCOUNT_STATUS_TOTAL() – The function returns information about the total number of inquiries made in the current billing month for a given account (all API keys). Returns exactly the same number as the $(TotalCount) tag in the VIESAPI_STATUS_ACCOUNT() function.

VIESAPI_ACCOUNT_STATUS_LIMIT() – The function returns information about the maximum number of queries available in the plan selected by the user. Returns exactly the same number as the $(Limit) tag in VIES.STATUS.ACCOUNT() function

VIESAPI_EUVAT_NORM() – This feature corrects and formats EU VAT number (removes spaces and hyphens etc.) offline and does not retrieve any data from VIES:

=VIESAPI_EUVAT_NORM("PL 717-164-20-51")

VIESAPI_EUVAT_VALID() – The function only checks the correctness (syntax, checksum) of the EU VAT number offline and does not retrieve any data from the VIES system. Returns TRUE if the number is valid or FALSE if it is not valid:

=VIESAPI_EUVAT_VALID("PL7171642051")

VIESAPI_CONNECTION_STATE() – The function returns current status of add-in configuration.

If the function returns the following value, it means, among others that the ID and API key were not saved:

ERROR: Connection requires configuration

After saving the API ID and key, the function should return the following message:

SUCCESS: Production

Google Sheets™ is a trademark of Google LLC.