Job Postings & Applications Using AirTable

job postings and applications using airtable

One extremely common use case where SheetDream shines is in designing a system to not only display open job postings, as well as accept applications on the website from potential job seekers.

This is a problem that nearly every company faces, and no doubt about it there is plenty of software out there that can take care of the problem for you (at virtually every price point you can think possible!). TalentLyft is an excellent candidate relationship management tool, for instance, which offers a wealth of different features. It does come at a somewhat hefty price point of $149.

TalentLyft is great and offers so much, but perhaps you don’t need all the bells and whistles. Perhaps you need only a handful of things from a candidate relationship system and the extras just aren’t worth it to you.

In this article, we’re going to take a look at how you can build out a talent management system using SheetDream, Airtable, and SendGrid for sending emails to applicants.

There’s basically 3 different things that we want to accomplish:

  • Be able to display our open job positions live on the website
  • Be able to accept applications from job seekers on the website
  • Be able to send out different emails to applicants depending on conditions within Airtable (e.g. candidate approved for a phone interview, candidate rejected, candidate approved for an in-person interview, etc.)

Where Do We Start?

You might be slightly overwhelmed by just these 3 bullet points. Where do you even start? How do I jump right into this?

I’ll tell you the easiest way to start: Airtable. The data. Honestly, for most any project or app you work on starting with the data is not a bad strategy at all. Put on your creative hat and just start tinkering around in Airtable.

So to start with, we define all of the entities that exist in your dataset:

  • Jobs
  • Applications

We’ll begin by defining our Jobs table. Here’s a screenshot from Airtable showing what we have:

jobs in airtable

Here are the specific fields:

  • Role Name (job title)
  • Department
  • Summary: a rich text summary of the open position
  • Goals and Responsibilities: a rich text summary of exact responsibilities the individual will have
  • Reports To: who the candidate reports to
  • Location: where the office is located
  • Salary: the salary range for this particular position
  • Benefits
  • Start Date
  • Contract: whether or not it’s a part-time, full-time, or contract position
  • Deadline For Applications
  • Education
  • Experience

All of these fields are either of type Single Line Of Text, or Long Text, with the exception of the Department field which we have set to a Single Select.

Next, we add our AirTable to SheetDream and let it build our API.

Adding It To SheetDream

We head into SheetDream, and under the Data Sources tab we select Airtable and input our Airtable information. We grabbed our base name from Airtable’s API documentation (if you click Help, there’s a link called API documentation which leads to documentation for that particular base).

adding an airtable to sheetdream

After saving the changes and waiting a few minutes, we refreshed the screen to see that our table was successfully synced and our API was setup and ready to go:

viewing table & activity in sheetdream

api tab in sheetdream

OK! We’re off to a good start. We’ve setup a Jobs table in Airtable, and gotten it synced to SheetDream which is exposing the data in our very own API. Cool stuff. Now, we need to put these jobs on display live on the website. For that, we head over to the Embedding & Code tab in SheetDream to generate some HTML/Bootstrap/Handlebars.js code to dynamically display this stuff on our site.

embedding and code tab in sheetdream

On this tab, we select the accordion layout and set the Group By Key value to “Role Name”. What this means is that SheetDream will group / index our results by this particular key when delivering the data to the Handlebars.js template. In this particular scenario it’s not super important, but grouping is a pretty powerful concept which can let you do some cool things when displaying your data in a web browser. But anyway, more on that later. We push on, and after that we have some basic HTML we can work with. We copy that into JSFiddle, a great app for very quickly getting some HTML/CSS/JavaScript up and running quickly. We tinker a bit with the HTML/CSS until we have things looking good:

jsfiddle showing job board using handlebars.js

Not half bad so far!

Granted, not everyone is an HTML/CSS wiz (hint: we offer assistance with this under our Ultimate plan!).

Anyway, back on topic. We’ve got something looking good and we’re displaying our open jobs on our website now. Now, we need to enable candidates to easily submit their applications to us from the site. Let’s take a look at how we would do something like this.

Again, we start with the data on the database. Here’s what our Applications table looks like:

applications table in airtable

We have the following fields:

  • Role Name: the name of the role from the Jobs table
  • Status: a single select field which determines our workflow routing for SendGrid emails
  • CV: the candidate’s resume in text format
  • Email Address: the candidate’s email
  • Full Name: candidate’s full name
  • Cover Letter: the candidate’s cover letter in text format
  • Welcome Email Sent: single select field of “Yes” or “No”
  • Phone Screen Email Sent: single select field of “Yes” or “No”
  • Interview Toolkit Email Sent: single select field of “Yes” or “No”
  • Panel: the interview panel that will be interviewing the candidate (note: Single Line Of Text)

The idea behind this table is that we’re going to have an email automation process that will get kicked off by SheetDream when the Status field changes value. We use the “Email Sent” fields in the table to track when we send emails out to the candidates as they go through the hiring process. This prevents us from accidentally emailing a candidate more than once. We’ll see exactly how that works a little later in SheetDream.

So with that, we’re ready to start submitting applications. We head to SheetDream’s Embedding & Code tab again, this time we set the action to “Create New Record”. We choose No Layout because we’ve already got our accordion layout, and we just want SheetDream to generate the Handlebars.js we’ll need to inject the fields into the HTML. This creates a form we can use to submit and do a POST back to the server and submit some data to be inserted into a spreadsheet or Airtable.

embedding and code tab in airtable for applications table

We pasted that into our JSFiddle, and again after a little more HTML/CSS tweaking we get something like this:

application submission with sheetdream

Clicking the “Submit Application” button makes a POST call to our API, posting the data and consequently creating a new record within Airtable.

One very important thing to note here is that we’re missing some sort of protection against spam and abuse. Before releasing this to the public, we would want to make sure we add in a CAPTCHA system (such as Google’s reCAPTCHA). After generating your site key you would want to add some special JavaScript and HTML from here to your page to ensure the user is real.

The Workflow Automation Part

Being able to accept applications is great — but we also want to be able to keep our candidates informed during our hiring process. We want to send them an email where they can track the status of their application, and we want to send them emails for when we’re moving forward with a phone screen, or when we’re moving forward with an in-person interview. The idea is:

  1. Applicant submits application via website
  2. A hiring manager at the company reviews the submitted application, and decides whether or not the application is rejected (changing the Status to Rejected in Airtable) or approved for a phone screen (changing the Status to Approved For Call).
  3. Upon changing that status, a workflow automation will run sending the candidate an email requesting more information from them for scheduling the phone screen.
  4. After a candidate has completed a phone screen, the screener either rejects the candidate or changes the Status to Approved For Interview. This means the candidate is invited to come in for an in-person interview. They also assign the interview Panel, a comma separated list of managers that the interviewee will be spending time with. Changing these two fields kicks off another workflow automation that will send another email to the candidate.

So how do we achieve this? By leveraging SheetDream’s External API data source. Just like you can configure Google Sheets or Airtable as data sources, you can also configure an external API as well. Though in our case, we’re actually pushing data out of SheetDream and into SendGrid.

The External API data source is a screen that lets you configure API calls to be made directly from SheetDream, and lets you pass any data stored within SheetDream to these APIs. It can also be used to grab data from APIs and insert it directly into your Airtable or spreadsheet.

data source screen in sheetdream

The JSON-like query language you see above is called JSONata, which allows you to dynamically transform one JSON structure to another.

Those who have used Integromat and more advanced Zapier probably had their ears perk up on that line. One of the biggest things these services have to solve when calling APIs is being able to transform and reformat data in ways that different services can understand each other. JSONata achieves this by exposing a very simple little mapping language which lets you pick and choose the fields you want in your final JSON data structure.

So, what we’re going to do in External API is use JSONata to select only certain records that satisfy a particular condition (such as Status = “Approved For Call” and “Phone Screen Email” being “No”). This gives us a list of the applicants who need to be scheduled for a phone screen. From there we can build the URL we want to send a request to (in our case the https://api.sendgrid.com/v3/mail/send endpoint) and the data we want to pass along to it. We select the data from our database using the aforementioned JSONata.

$.Applications[`Welcome Email Sent`="No"].{ 
    "__url": "https://api.sendgrid.com/v3/mail/send", 
    "__id": _id 
}

This basically says: for each of the Application rows with Welcome Email Sent = “No”, select two fields: a URL, and record ID.

So in this particular case, we’re setting URL to https://api.sendgrid.com/v3/mail/send and setting a variable called “__id” with the row’s record ID. Later on in the API call we query that row by this ID (using JSONata once again) to construct a request to https://api.sendgrid.com/v3/mail/send. Notice the usage of our __id variable in this one:

$.Applications[_id=%.__id].{  
    "template_id": "d-f0d522b2fba54661ab3fcaa2f53020b7",
    "personalizations": [{
        "to": [{
            "email": $.`Email Address`,
            "name": $.`Full Name`
        }],
        "dynamic_template_data": {
            "name": $.`Full Name`,
            "link": "https://api.plutodream.com/sheetdream/jobboard/status.htm?id=" & $._id
        }
    }],
    "from": {
        "email": "support@plutodream.com",
        "name": "SheetDream Support"
    },
    "reply_to": {
        "email": "support@plutodream.com",
        "name": "SheetDream Support"
    },
    "subject": "Thank You For Applying"
}

So we find the particular Application we’re operating on by our __id field we selected, and then we dynamically insert values in for email, name, and link from that particular Application. “name” and “link” are merge values for our SendGrid template, letting us personalize the email message a little bit more.

Here’s what some sample generated JSON would look like from the above JSONata query if Jeff Bezos were the applicant:

{  
    "template_id": "d-f0d522b2fba54661ab3fcaa2f53020b7",
    "personalizations": [{
        "to": [{
            "email": "jeff.bezos@imaginarycompany.com",
            "name": "Jeff Bezos"
        }],
        "dynamic_template_data": {
            "name": "Jeff Bezos",
            "link": "https://api.plutodream.com/sheetdream/jobboard/status.htm?id=53161dba-98a8-42ea-9bf5-e798f31c331b"
        }
    }],
    "from": {
        "email": "support@plutodream.com",
        "name": "SheetDream Support"
    },
    "reply_to": {
        "email": "support@plutodream.com",
        "name": "SheetDream Support"
    },
    "subject": "Thank You For Applying"
}

[Confused by the JSONata? Don’t worry. SheetDream’s editor will guide you along as you write it. We also provide several builtin templates you can use to build your API request off of. JSONata itself is actually not that difficult to get the hang of, even if you’re not a programmer. Check out this video to get a crash course in 5 minutes. We’ve also got very active customer service at support@plutodream.com, where we can help get you setup in short order.]

At this point, we’ve done a few things:

  • We’ve got our basic API call configured
  • We’ve setup various different email templates within SendGrid for different scenarios we want to send an email for.
  • We have our API call setup to only trigger when the row satisfies a specific condition.

Next, we test our first API call. Just like in Integromat and Zapier, testing is an important part of the building process. We click Test & Load Data to call SendGrid (triggering an email send), and load any data that gets returned.

test and load data in sheetdream

In SendGrid’s case, it doesn’t really return anything back, not even JSON. Which is why we’ve checked “Treat returned data as raw data (not JSON)”, preventing it from running any JSONata on the request.

After that, we can tell SheetDream what kind of changes we want to make to our Airtable data after this API call. Normally for APIs you’ll be retrieving data from them and transforming that data using JSONata to insert into your Airtable or Google Sheet. But in this scenario, all we really want to do is update our “Phone Screen Email Sent” column to “Yes”. Changing it to “Yes” will now prevent SheetDream from processing the same record twice — so we don’t send a duplicate email. We also set all of the other “Email Sent” fields to “No”, so that they’re all setup for the next API connections we setup and configure.

table configuration on external api screen in sheetdream

This can be read as: store these fields into the Applications table, where _id (SheetDream’s internal record ID) equals our __id variable that we queried earlier.

This basically sets things up so that when a record is in a specific state in Airtable, an action takes place that sends an email. Cool stuff.

We repeat this same process for our other emails: for those approved for a phone screen, and for those who passed the phone screen and we’re inviting in for an in-person interview.

Here’s the email a candidate receives when they submit their application:

application received

You’ll notice a link to a status page that we haven’t talked about in this use case: that was left out for brevity. But it’s a simple page that displays the Status field from Airtable and displays a friendly (um, somewhat) message telling the candidate about what’s going on with their application. We try to let them down softly when we can.

Here’s the email the “phone screen approved” folks will receive:

phone screen email

And the email that the in-person interview approved receive:

in-person interview request

So here’s what we accomplished in fairly short order:

  • Displaying job openings from Airtable on our website
  • Inserting new applications submitted by job seekers visiting the website
  • A series of workflow automations triggering off record statuses, making API calls to SendGrid to send out emails to the applicants as statuses change

We’ve more or less built out a complete barebones end-to-end candidate relationship management system. And all it took was a little HTML/CSS to get things plugged in, as well as a handful of JSONata queries. Talk about low-code!

For this sort of application, if you had developed it from the ground up the “old school” way, it would probably take several days to cobble together. You have to setup a database, a web server, decide on a tech stack and get all of the scaffolding in place. Then you have to design the API, expose it (safely!) and stand it up on the cloud somewhere. All in all, you’re looking at paying a developer for several days worth of work to get something working, and with SheetDream we got all of this stood up in under 1 day! Here’s the final product for our fictitious company:

Intrigued by SheetDream’s capabilities in regards to this particular use case? Have any questions? Get in touch with us! We’d love to hear about your particular use case and see if we can do something to help.

 

 

More Use Cases