Home > Software engineering >  VAT Checker Apps Script
VAT Checker Apps Script

Time:09-30

I'm trying to make VAT checker in Google Apps script, to use in a Google Sheet. My code looks like this:

function VATChecker(input1, input2) {
  
var country = input1
var VATid = input2


// Builds the VIES URL
const VATUrl = `https://ec.europa.eu/taxation_customs/vies/rest-api/ms/${country}/vat/${VATid}`;
const encodedVATUrl = encodeURI(VATUrl);

// Fetches & parses the URL
const fetchRequest = UrlFetchApp.fetch(encodedVATUrl);
const results = JSON.parse(fetchRequest.getContentText());


// Returns the Data
var data = [];
  
results.data.forEach(function(pieceOfData){
  data.push(String(pieceOfData.isValid));
});

if (data = true)
  return "VALID";

else 
  return "NOT VALID";


For now it's giving me the following error in the cell with the function:

TypeError: Cannot read property 'forEach' of undefined (line 20).

When manually creating the URL, this is the data that is returned

{
  "isValid" : true,
  "requestDate" : "2022-09-29T08:42:50.353Z",
  "userError" : "VALID",
  "name" : "MVI E-COM B.V.",
  "address" : "\nTRASMOLENLAAN 00012\n3447GZ WOERDEN\n",
  "requestIdentifier" : "",
  "vatNumber" : "861876295B01",
  "viesApproximate" : {
    "name" : "---",
    "street" : "---",
    "postalCode" : "---",
    "city" : "---",
    "companyType" : "---",
    "matchName" : 3,
    "matchStreet" : 3,
    "matchPostalCode" : 3,
    "matchCity" : 3,
    "matchCompanyType" : 3
  }
}

Any suggestions?

CodePudding user response:

There is no array returned and also object property data. So you can get the isValid directly from results:

/**
* Returns valid or invalid check on VAT number
*
* @param {string} vat The VAT id.
* @param {string} country the ISO2 country id. Default is NL
* @return {string} valid / not valid
* @customfunction
*/
function VATChecker(vat, country = "NL") {
  // Builds the VIES URL
  const url = `https://ec.europa.eu/taxation_customs/vies/rest-api/ms/${country}/vat/${vat}`;

  // Fetches & parses the URL
  const fetchRequest = UrlFetchApp.fetch(url);
  const result = JSON.parse(fetchRequest.getContentText());

  return (result.isValid) ? "VALID" : "NOT VALID"
}

function testVatInCodeEditor(){
  console.log(VATChecker("003177397B01"))
}

Succes met het implementeren!

  • Related