Network Programming in .NET

Google sheets is an amazing product, but let’s imagine, you’d like to expand upon Google Sheets to fill in the details of a cell by calling an API.

In this example, we’re using an API that determines the make and model of a car from it’s license plate, If you’d like to follow along, then you can create your own free account at https://www.regcheck.org.uk  – The username and password for this particular API have been removed from the demo code, you’ll need to use your own.

So, Open Google Sheets, and Press Tools -> Script Editor, then enter the following script;

function RegCheck(RegistrationNumber) {
var encode = Utilities.base64Encode(‘***USERNAME***:***PASSWORD***’, Utilities.Charset.UTF_8);
var option = {
headers : {
Authorization: “Basic “+ encode
}
}

var url = “https://www.regcheck.org.uk/api/json.aspx/Check/” + RegistrationNumber;
var response = UrlFetchApp.fetch(url, option).getContentText()
response = JSON.parse(response);

return response.Description;
}

What this does, is that it first creates the basic authenticatio header from your username and password, which you used to register with the API. And then passes the registration Number into the URL.

Once the URL returns, we extract the Description from the JSON returned, and return it back to Google Sheets.

Now, In google Sheets, all you need to do is enter =RegCheck(“xxxxx”) in a cell in order to call the API.  Where “RegCheck” is the function name

Of course, you can use the same approach to call any API you wanted.

If you ever need to generate a Uint8Array to represent a 1×1 pixel transparent PNG in Node, here’s the script

var base64 = “iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAQAAAC1HAwCAAAAC0lEQVR42mNkYAAAAAYAAjCB0C8AAAAASUVORK5CYII=”;
var atob = require(‘atob’);
var raw = atob(base64);
var rawLength = raw.length;
u8 = new Uint8Array(new ArrayBuffer(rawLength));
for(i = 0; i < rawLength; i++) {
u8[i] = raw.charCodeAt(i);
}

Two factor authentication is a way to level up your security, beyond username and password. Using Google Authenticator is also a great way to do this for free, since it doesn’t incur costs such as doing 2FA via SMS.

It does require a basic tech awareness, so if your typical user is elderly, then this is not the way to go.

You can of course use Google Authenticator without using an API, you can implement the crypto code yourself, but using this API at AuthenticatorAPI.com does save you alot of development time, since it’s just two API calls.

So, how does it work?, well first you have to generate a random code. This could be just any random code that comes to your head, or perhaps better, to generate a random code per user, and store this.

You now need to show a QR code to a user, which they scan into the Authenticator App. The QR code is generated using the API, and is just a block of HTML you display on your page. It’s 300×300 pixels in size.

To do this, you call;

https://www.authenticatorApi.com/pair.aspx?AppName=MyApp&AppInfo=John&SecretCode=12345678BXYT

Once the user has paired, and they go to log in, you prompt them for their pin. You then have to send the PIN and the SecretCode from earlier to our API, and it will return either True or False.

By Calling;

https://www.authenticatorApi.com/Validate.aspx?Pin=123456&SecretCode=12345678BXYT

The pin is time dependent, so the same PIN won’t work the following day. This defeats key loggers and replay attacks.

As an update for 2020, this code is going to get buried under ice in Svalbard, under Github’s archive program!

Latvia is a small baltic country, with a population of 1.92 million, and a car ownership rate of 342 per 1,000 – so an estimated car count of 650,000 vehicles. Today, we have expanded our API coverage to include Latvia, you can read more about this at our Latvian website http://www.autoapi.lv 

Car registration plates in Latvia use the /CheckLatvia  endpoint and return the following information:

• Make / Model
• Age
• VIN number
• Fuel
• Engine size
• Representative image

Sample Registration Number: 

GZ3425

Sample Json:

{

  “Description”: “VW BORA”,

  “Variant”: “BORA (1J2) (98-13)”,

  “RegistrationYear”: 1998,

  “CarMake”: {

    “CurrentTextValue”: “VW”

  },

  “CarModel”: {

    “CurrentTextValue”: “BORA”

  },

  “MakeDescription”: {

    “CurrentTextValue”: “VW”

  },

  “ModelDescription”: {

    “CurrentTextValue”: “BORA”

  },

  “VehicleIdentificationNumber”: “WVWZZZ1JZXW273563”,

  “EngineSize”: 2324,

  “Power”: 110,

  “FuelType”: “Benzīns”,

  “ImageUrl”: “http://autoapi.lv/image.aspx/@VlcgQk9SQQ==&#8221;

}