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.