Annotate HR Recruitement Data Using Google Apps Script and ML

Sep 19, 2022

In this article, we will combine the power of Google Apps Script and Machine Learning APIs to create a new function that annotates text from your sheet.

Table of Contents
            
A Practical Use Case: HR Recruiting Process
How To Do This?
Let’s Do It!
Final Code, Results and Conclusion

Note: In this article, we used a model already built and deployed.
Consider reading this article to know more about building a model, and this one about tools and best practices for deploying an NLP model as a prediction microservice with a REST API.

A Practical Use Case: HR Recruiting Process

Have you ever used a spreadsheet program such as Google Sheets?

Probably you have used it to perform some calculations and manipulations on your data arranged in cells (row ∩ column). For that, we have a bunch of formulas that we can use (e.g., IF(), SUM(), TEXT(), SEARCH(), etc.).

Although spreadsheets offer us a wide range of useful operations, sometimes we need to perform a specific task that only an external Machine Learning model can handle. Here’s an example of a business use case:

If you are an IT recruiter for a startup, and you received 500 applications, which you automatically collected in a sheet, you will have to go through each application and read it to decide if the candidate should be shortlisted for a test/interview or rejected. But having 500 texts of more than a thousand words to classify would be a tedious and inefficient process.
To help yourself, you have decided to automatically reject any application that does not meet the minimum years of experience, the proper degree, and does not have at least 80% of the required skills.
That would make things so much easier, right? But how to extract automatically from each job application the skills, the diploma, and the experience?

The short answer is to use a machine learning model as a service that takes the text (job application) as input and returns the searched entities (skills, degree, etc.) as output.

This is just one business use case out of thousands of personal and professional use cases where Google Apps Script and ML APIs will help you get better results with fewer resources.

In this article, we will approach the HR process from the candidate’s side. Rather than detecting the match between the application and the job offer for the recruiter, we will help the candidate extract the skills required in the job offer, the required years of experience, and the diploma major so that they can have a stronger application.

Here is a demo of the result we expect to get:

Demo: The user enters a job description in a google sheet, then gets the skills, experience, and diploma from it using functions we will create (By the author.

How To Do This?

To do the automatic entity extraction we mentioned above, we must first have a deployed ML model API that takes text as input and returns its entities. Once we have that, we’ll use Google Apps Script to take the input from the user’s sheet, call the API using that data, and display the result in that sheet.

UBIAI Auto-Labeling API: Provides the ability to train NLP models for auto-labeling and inference. The auto-labeling tool reduces the time and money it takes to label data and allows an automatic annotation of entities, such as time, place, date, product, person, etc., after downloading text from native files.

For this article, we will use a model trained by ubiai.tools to extract entities such as Skills from job descriptions.

Auto labeling tool to extract Skills, Diplomas, Experience, etc. (Source: UBIAI)

Google Apps Script

Developing an application can be a complicated task. However, nowadays, we have more and more tools to help with this task, so it is possible to perform it more easily, as the example in this article. Google Apps Script is an example of such software. It allows us to script inside some of Google’s services, so it is possible to modify the behavior of the Google Sheets software.

On a wider scale, it is very effective for creating creative applications that can be stored and shared with many people via Google Drive or a publication in the Chrome Web Store. Google Script is the programming language used for this purpose. It is very similar to JavaScript, although the latter is generally used to manipulate web pages, Google Script is more suitable for accessing data and modifying the behavior of Google applications.

Note: There are two types of Apps Scripts: the standalone scripts, which are not linked to a specific Google document, and the container-bound scripts, which are linked to a Google document and created from that doc. In this article, it is evident that we will be using a container-bound script from a Google Sheets spreadsheet.

Let’s Do It!

So, first of all, we need to create a Google sheet, in which we will put our data to proceed:

Google Sheets (By author)

Then we need to access Apps Script via Extensions > Apps Script:

Google Apps Script created from a Google sheet (By author)

We got a file already created named Code.gs containing a default function named myFunction, so I will change the name of the function to getUbiaiAnnotationResponse, and now we can start scripting. Yes, it’s as simple as that .

– The first thing to do in the script is to take the data from the user’s sheet. The user will call a function, passing the cell corresponding to the job description to it. So let’s add a parameter to our function, which corresponds to the user’s job description input, and then assign it to a variable that will be included in the body of the API call. Here’s the code:

Get the data (By author)

Next, let’s set the appropriate ubiai.tools API options:

				
					const apiRoot = 'https://api.ubiai.tools:8443/api_v1/annotate/';const apiToken = '*** Secret String :) ***';const options = {
   'method': 'post',
   'contentType': 'application/x-www-form-urlencoded',
   'payload': data
};
So we will send the data — in a single block in the HTTP message body — using a POST request.

Let’s now call the API using our token and options, then parse the response and retrieve the returned entities. Here’s what it looks like:
const response = UrlFetchApp.fetch(apiRoot + apiToken, options);const entities = JSON.parse(response)[0].entities;

Let’s run the code on a data sample and see the result:

Note: you will be asked for permissions the first time you run the script.

API response (By author)

So, as shown in the screenshot above, running our script on the data “five years experience, a degree in computer science, Python expert” returned a list of entities containing that the entity “5 years” is an Experience, “computer science” is a Diploma_Major, and “python” is a Skill.

Since we are interested in displaying the Skills, Diploma_Major, and Experience each in a custom column, let’s go ahead and define three functions to send the requested label back to the user’s sheet:

The getSkills function takes the user’s data and then calls the getUbiaiAnnotationResponse function we defined to call the NLP API and return the desired skill entities to the user (Same for getExperience and getDiploma).

Note: Since we may be calling getSkills, getExperience, and getDiploma with the same data, it would be a good practice to add a caching mechanism in the code to avoid unnecessary redundant API calls.

Final Code, Results, and Conclusion!

The getSkills final code:

The getExperience final code:

The getDiploma final code:

The getUbiaiAnnotationResponse final code:

Final code: API call (By author)

Google Sheets Results

Results on google sheet (By author)

While this use case is simple (extracting skills, experience, and diploma major from a job description), it is designed to demonstrate how to use an external API in Google Sheets to create a new function that processes text.

Using the pre-trained NLP model automates the work and makes it more efficient. This technique can be used in any other use case when you can leverage both Google Sheets and Machine Learning models.

Thanks for reading! Stay tuned for more.

What are you waiting for?

Automate your process!

The Services provided are really great, we received a genuine advice and at very reasonable cost. all the work went hassle-free and no complication.