Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Quick and dirty google script to call VIES VAT ID check service from google sheets.
/**
* A special function that runs when the spreadsheet is open, used to add a
* custom menu to the spreadsheet.
*/
function onOpen() {
var spreadsheet = SpreadsheetApp.getActive();
var menuItems = [
{name: 'VAT ID check', functionName: 'idCheck'}
];
spreadsheet.addMenu('VIES', menuItems);
}
function idCheck() {
var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getDataRange().getValues();
// Logger.log(data);
// Logger.log(sheet.getLastRow())
var attribs = data[0];
for(var i = 1; i < sheet.getLastRow(); i++){
var record = data[i]
// Logger.log(record);
try {
var countryCode = record[0].slice(0, 2);
var vatNumber = record[0].slice(2);
var payload = requestXml(countryCode, vatNumber)
var result = callApi(payload)
// Logger.log(result);
sheet.getRange(i + 1, 2).setValue(result["valid"]);
sheet.getRange(i + 1, 3).setValue(result["name"]);
sheet.getRange(i + 1, 4).setValue(result["duration"]);
} catch(error) {
sheet.getRange(i + 1, 2).setValue("ERROR");
Logger.log('ERROR: ' + error);
}
}
}
function requestXml(countryCode, vatNumber) {
// XmlService cannot handle multiple namespaces for a single element at the moment.
var document = XmlService.parse("<soapenv:Envelope xmlns:soapenv=\"http://schemas.xmlsoap.org/soap/envelope/\" xmlns:urn=\"urn:ec.europa.eu:taxud:vies:services:checkVat:types\"></soapenv:Envelope>");
var root = document.getRootElement();
var ns = XmlService.getNamespace("soapenv", "http://schemas.xmlsoap.org/soap/envelope/");
var nsVat = XmlService.getNamespace("urn", "urn:ec.europa.eu:taxud:vies:services:checkVat:types");
var header = XmlService.createElement("Header", ns);
var body = XmlService.createElement("Body", ns);
var vat = XmlService.createElement("checkVat", nsVat);
var countryElement = XmlService.createElement("countryCode", nsVat).setText(countryCode);
var vatElement = XmlService.createElement("vatNumber", nsVat).setText(vatNumber);
vat.addContent(countryElement);
vat.addContent(vatElement);
body.addContent(vat);
root.addContent(header);
root.addContent(body);
return XmlService.getPrettyFormat().format(document);
}
function callApi(payload) {
var options =
{
"method": "POST",
"contentType": "text/xml",
"payload": payload,
"muteHttpExceptions": true,
};
var sendDate = (new Date()).getTime();
var xml = UrlFetchApp.fetch("http://ec.europa.eu/taxation_customs/vies/services/checkVatService", options).getContentText();
var durationMs = (new Date()).getTime() - sendDate;
var root = XmlService.parse(xml).getRootElement();
var vat = root.getChildren()[0].getChildren()[0];
return {
"valid": value(vat, "valid"),
"name": value(vat, "name"),
"address": value(vat, "address"),
"duration": durationMs
}
}
function value(entity, name) {
var ns = XmlService.getNamespace("urn:ec.europa.eu:taxud:vies:services:checkVat:types")
return entity.getChild(name, ns).getValue()
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.