Home > Uncategorized > Using #AppsScript to call an #API from Google Sheets

Using #AppsScript to call an #API from Google Sheets

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.

Categories: Uncategorized
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: