AppsScript is a Macro programming language that you can use within Google Sheets to automate data entry. In this case, we want to take one cell, which contains a vehicle registration number, and run it through an API, in order to extract details, like the make / model / VIN of that car. The API providing this is available here; https://www.carregistrationapi.in (India) – Many other countries are available, like the US, UK, Australia, South Africa, etc., but this particular example is for India.
To Access the App Script “IDE” press Extensions > App-Script on your google sheet. Here I am using the code as follows:
var USERNAME = “***USERNAME GOES HERE ****”;
var PASSWORD = “*** PASSWORD GOES HERE ****”;
function CheckIndia(plate,property,subproperty)
{
var userProperties = PropertiesService.getUserProperties();
var cacheHit = userProperties.getProperty(plate);
if (cacheHit != null)
{
var cacheData = JSON.parse(cacheHit);
if(subproperty == null)
{
return cacheData[property];
}
else
{
return cacheData[property][subproperty];
}
}
// Sample UP14CT0093
var url = ‘https://www.regcheck.org.uk/api/json.aspx/CheckIndia/’ + plate;
var headers = {
“Authorization” : “Basic ” + Utilities.base64Encode(USERNAME + ‘:’ + PASSWORD)
};
var params = {
“method”:”GET”,
“headers”:headers
};
var response = UrlFetchApp.fetch(url,params);
var json = response.getContentText();
userProperties.setProperty(plate,json);
var data = JSON.parse(json);
if(subproperty == null)
{
return data[property];
}
else
{
return data[property][subproperty];
}
}
The USERNAME / PASSWORD variables need to be taken from your registration on https://www.carregistrationapi.in .
The Function “CheckIndia” is what you call from within the Google Sheet Cell, such as:
=CheckIndia(A2,”Description”)
Here, in the example above, A2 is the cell that contains the license plate, and “Description” is the make + model of the vehicle.
There is a level of internal caching built-in so that the API is not called every single time a call is made, if the call was made elsewhere on the sheet.
