Creating a Birthday List in Google Sheets

We are really excited to announce the release of the HR Partner API (Application Programming Interface) this month.  What does this mean?  Well, in short, it means that you can extract your HR Partner data in real time, and use that data in all manner of third party applications to really streamline your HR Partner processes.

Let's look at one example here.  A lot of our customers have asked if it is possible to get a 'birthday report' out of HR Partner - basically a list of everyone who has a birthday in the current month.  We already provide a birthday widget on our main dashboard that does this, but customers have asked for a printable report, or a list that they can do something with (i.e. send a birthday email to employees etc.).

Well, in this post, we will show you how to extract a list of employees from HR Partner into a Google Sheet, then from that, generate a list of employees who have a birthday in the current month.

Activate the API in your HR Partner company

Before we do anything else, we will need to ensure that your HR Partner company is accessible via our API.  By default, this is turned OFF for security reasons, but if you need to access your data from third party applications, then you will need to turn access ON.

Go to Setup -> Configure -> Integrations on the left hand menu.

You will need to tick the box that says 'Enable API Access for external applications'.  Also, while you are in here - take note (or copy and paste) the API Token just above the text box.  You will need this token in order for external applications to connect to your HR Partner company.

Note: Please keep this token safe and secure.  It is basically the 'password' to your company, and if any other party has access to this, then they will be able to access your HR data! (This is why we have blurred our own sample token above).  If you suspect that someone else may have accessed your token, you can click the yellow 'Reset API Token' button to generate a new one.

Google Sheet Script

Ok, next step is to set up a connection between Google Sheets and HR Partner.  In order to make the process of doing an API query easy for you, our programmers have created a special Google App Script which will do 90% of the hard work in the background (with thanks to Brad Jasper and Trevor Lohrbeer for major portions of the code).

To get this script, please visit our GitHub page at https://github.com/HR-Partner/HRPartner2GoogleSheets

You will need to click the file pointed out above, the one that says HRPartner2GoogleSheets.gs

What you will need to do is to click and drag the entire contents of the file (yes, ALL 550+ lines) and then press [Ctrl-C] to Copy the file contents into your clipboard.

Note: You can download the file and then copy it using a text editor - just use whichever method is easier for you.

Installing the Script

Now you will need to create a brand new Google Sheet in your browser.

From the top level menu, click on Tools, then Script Editor.

This will actually open up a new browser tab with the Google Script Editor.

There is a default placeholder script already in the window, but just delete this and paste the contents of the HRPartner2GoogleSheets.gs file that you copied in an earlier step.

Before we do anything else, we must make a slight change to this script.  On line 36, there is a variable declared which should hold your HR Partner API Token that we mentioned earlier.

Replace the text '<insert your HR Partner API key here>' with your API Token that you copied earlier.

Be sure that your API Token is enclosed in single quote marks.

Ok, that is all the changes we need to make here.  Now just click the 'Save' icon on the toolbar and  you should be all set.

You will be asked for a project name to save this script as.  Type in HRPartner2GoogleSheets, or actually, you can name this project anything you like!

You can now go back to the browser tab with your empty Google Sheet to continue.

This script has installed 2 brand new custom functions in your Google Sheets, called ImportHRPartner() and DateHRPartner().  We will explain more about these below.

Grabbing HR Partner Data

Right, we are now ready to pull some information from HR Partner into a Google Sheet.  What we will do first, is to grab a list of employees, including their Code, Full Name, Birth Date and the Department they work in.

First, lets rename this tab to 'Employees' so we know what sort of information we will have in here.

Click on the little green down arrow next to the 'Sheet1' default tab name and choose 'Rename'.  Then type in 'Employees' and press [Enter].

Let's start with a really basic query.  We shall import ALL employees and ALL fields from the employee file here.

Go to cell A1, and type in:

=ImportHRPartner("employees")

After a few seconds (in which the cell A1 says 'Loading...'), you should see a result like this.

This is very cool, but it has pulled in ALL employees and ALL fields, which can be a little cluttered.  Let's start by filtering down to ONLY employees who are active in HR Partner.

Click back on cell A1 (Magically, your formula is still there!), and change it to:

=ImportHRPartner("employees?is_active=true")

Now, you should only get a list of active (non terminated) employees.

Note: You could have used the following formula as well, which would have given you the same thing:

ImportHRPartner("employees?is_terminated=false")

Ok, we are getting there.  But there are still too many columns.  We really only wanted the employee code, full name, department and birth date, so what we will do is to add another parameter to the formula:

=ImportHRPartner("employees?is_active=true", "/code,/full_name,/date_of_birth,/department")

Which should give you the following.

When we specify individual fields, we should precede them with a forward slash '/' character, and separate the field names with a comma ','.

How do you know what the field names should be? Well, head on over to our API Documentation site, and look at the JSON data structure that is returned from the "employees" API call.

All you need to do is to specify any of these JSON field names using a leading slash, e.g. /last_name or /location or /started_at etc.

Date Fields

There is one more thing that we need to be aware of, and it is a limitation of the integration between HR Partner and Google Sheets.  It is the way date fields are treated.

When dates are brought across from HR Partner, they are treated as plain text fields, not actual date fields, even though they may look like normal date fields in yyyy-mm-dd format.

This means that you cannot do any date maths on them (i.e. adding days to the original date, or finding out the month number of the date etc.) until they are converted into a proper date object in Google Sheets.

Fortunately, our integration script also contains a function to convert these text dates into proper date objects that Google Sheets recognizes.

Lets create a new column to the right of the imported ones, that is a date object representation of the birth date.

Go to cell E1 and give it a heading of 'DOB'.  Then go to cell E2 and enter in the following formula:

=DateHRPartner(C2)

What this is saying, is to use the custom formula called DateHRPartner() to convert the text string in cell C2 into a proper date object and put it into cell E2.

You will see that E1 will then contain a slightly differently formatted date field (formatted to your specific country and locale settings).  You can then copy and paste this formula into all the other rows in column E.

Birthday List

Ok, lets move on with the next phase of this lesson, and that is to generate a birthday list of everyone who has a birthday in the current month.

We will put this information in a whole new tab to reduce clutter.  So let's create a new tab by clicking the '+' button to the left of the existing tabs.  You can give this new tab a meaningful name as well.

Now, in the new tab, go to cell A1.  We are going to use Google Sheet's own QUERY() function to pull a subset of data form the main employee list on the first tab.  Enter in the following formula:

=QUERY(Employees!A1:E26, "SELECT B,E WHERE MONTH(E)=MONTH(NOW())")

What this formula is saying is essentially: "Look at the current data in cells A1 to E26 on the Employees sheet, and select column B (Name) and column E (DOB) from that sheet where the MONTH in column E (DOB) is equivalent to the MONTH in the current date".

This should show you the following.

And there you have it - 3 employees who have birthdays this month (I ran this query in May 2020).

Conclusion

We hope that we have shown you how easy it is to create and run queries on your HR Partner data using our powerful API.  If you have any further questions, please do not hesitate to contact us on support@hrpartner.io - however, please be aware that API queries are usually of a more technical nature so they have to be escalated to our development team, meaning a longer response time for an answer.

We are also unable to provide direct support for a lot of third party tools that we are not familiar with, however our team is available for contract work to create integrations with services that you may use.  Please contact sales@hrpartner.io to discuss your needs and obtain a quote from us.

(Header Photo by Émile Perron on Unsplash)