Quick and dirty google script to call VIES VAT ID check service from google sheets.

google_sheets_vies_vat_id_check.js

/**
 * 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()
}