Creating a contract is a menial task. It’s essentially data entry. So, last year when we introduced more automation and tracking on our people ops team, we tackled contract creation. Even though it’s a task that can be done relatively fast, it meant a lot of back and forth, extra notifications and communication — “hey can I get that…” and “hey you put the last name/start date/ in wrong.” These are mistakes that happen when there is double manual entry — managers give the information for someone else to then input and edit a template contract, only for it to be sent back to them for reviewing, to then send it back to be signed off.

There are tools that are set up to do this for you, like Juro and other HRIS software like Bob. But sometimes the tool can actually make the process more painful and cumbersome as well as more expensive. Therefore… Apps Scripts and Zapier to the rescue!

Below I’ll explain what Zapier is and what Apps Scripts do, the steps I took to automate contracts, how we do it at Applied (including the script you can add to your google sheet), and why we haven’t automated the whole process (yet).

What is Zapier?

Zapier is a product that allows you to integrate applications and automate workflows. Meaning, you can connect applications that don’t have direct integrations. For example, sending a message to your slack every time someone fills in a google form or typeform.

What is an Apps Script?

Apps Scripts are a way for you to write a piece of code and interact nicely with a Google application like Gmail or Google Sheets so that you can customise your documents, processes or chat functions.

You can use Apps Scripts for many things (like creating a report on how many emails you send and receive, notify you if your website is down, or automatically update a date column when updating your risk register) and this isn’t the only way to automate your process. If you wanted to, you could use Zapier to patch the whole thing together. But that’s not as fun as learning a new thing now, is it?

User Flow

It might be helpful to write a user story or a diagram of the process you are trying to automate.

For my user story I wrote:

“I am an admin that needs to populate contracts with candidate information without a lot of mistakes or back and forth with the hiring manager”

And the diagram of what I wanted looked like this:

In addition to writing out your user story and what the process is/should be, ask yourself these questions:

1. how many people will use this?
2. how much time will be saved by automating this?
3. how much anxiety will be reduced by automating this? (alternatively, could more anxiety be introduced by automating this process/document?)
4. who needs access to the final document?
5. are these documents private or confidential?
6. is having an audit trail of this process/request beneficial for you company’s compliance requirements?

Once you have a good grip on the questions above, and it sounds like automating is the right thing to do, it’s time to get writing!

How to automate contract creation in 6 steps

Step 0

Once you’ve done the above (user story and answering questions) speak with your team and hiring managers about the updated process. They are the end users of the product you are building and they need to not just understand it but want to follow it and believe that it’s going to save them time and energy.

Step 1

Figure out what document you want to create and how many ‘tags’ or variables you’ll be replacing in your document.

Create a template with variables. For us, this was our default contract for full-time UK employees. From that template, figure out what will be your variables — these could be:

• names
• start date
• salaries
• manager
• signatories
• addresses

Step 2

Create a form and link it to google sheet.

Whenever a hiring decision happens at Applied, the hiring manager makes a phone call telling the candidate that they’ve been hired 🎉 (yay). After this they fill out a form with the relevant information for that candidate.

When the form is submitted it fills the row in google sheets which triggers the Apps Script to run the code which creates the draft contract with the right information.

Step 3

Google how to create a document from a googlesheet using Apps Script

That’s right, I googled how to do this and then I did it. Well almost…

Step 4

Find an Apps Script on github or a blog (like this one!) and copy it. As I did this a year ago, some of the blogs I read are now out of date or don’t work as Google updated some bits of how these things are written.

Google how to edit the Apps Script so that it works for you and your google workspace ie. if you’re adding more ‘IF’ statements (complexity), it may require a bit more work. ie. if you want to then send an email with the link to the pdf or google doc you’ve just created, you’ll need a bit more time and testing to make sure it works. Don’t be afraid to ask your engineering team for help either!

The piece of code we wrote creates a new google doc and pdf and puts them in the right folders. Here is the code we used and that you can copy:

function contractautomation(e) {
  var timestamp = e.values[0]
  var firstname = e.values[1]
  var lastname = e.values[2]
  var salary = e.values[3]
  var title = e.values[4]
  var startdate = e.values[5]
  var hiringmanager = e.values[6]
  // the above are variables that you've asked for in your form. In the google sheet, they are the headings/columns.The numbered values are the numbers in sheets. ie the first column is 0.

  var templateFile = DriveApp.getFileById(
    'file id found in URL after document/d/'
  )
  //the above line finds the template you created that you want filled in. You need to put the file id in the singular quotes above.

  var templateResponseFolder = DriveApp.getFolderById(
    'file id found in URL after drive/folders/'
  )
  //the above line finds the folder that you created where you want the new drafed contract to be saved. You need to put the file id in the singular quotes above.

  var copy = templateFile.makeCopy(
    'Draft Employment Contract, ' + firstname + ' ' + lastname,
    templateResponseFolder
  )
  //the above line makes a copy of the template you made, and then titles it with what is in the brackets above, and then saves it to the folder above)
  var doc = DocumentApp.openById(copy.getId())

  var body = doc.getBody()
  //the above lines find the body of the document so that it can find and replace the variables with their correct values ie. replace firstname with the person's actual first name

  body.replaceText('{{firstname}}', firstname)
  body.replaceText('{{lastname}}', lastname)
  body.replaceText('{{startdate}}', startdate)
  body.replaceText('{{title}}', title)
  body.replaceText('{{salary}}', salary)
  body.replaceText('{{hiringmanager}}', hiringmanager)

  doc.saveAndClose()
  //this line saves and closes the document
  //optional steps below to save as a pdf in separate folder
  var pdffolder = DriveApp.getFolderById('folder id')
  //the above line finds the folder that you want to save the pdf to
  var blob = DriveApp.getFileById(copy.getId()).getAs('application/pdf')
  pdffolder.createFile(blob)
  //the above line creates the pdf and saves it to the folder where you want it saved to
}

Step 5

Test it. Test it again. Test it some more.

If you are adding more complexity, you’re going to need to test it in different ways.

It took me awhile to realise that if you don’t change your google sheets to UK standard (file > settlings > locale), it will always put your dates in the American standard — this is also the case with Zapier and calendar zaps as well so you may need to add an extra filter in Zapier so that the date formatting is correct.

Step 6

Test it again. Ask someone on your team to fill out the form so you can test it a final time.

If you want to test it without having to fill out the form replace the variables as:

var timestamp = e?.values[0] || '2017-08-22 15:29:14.830 +0100'
var firstname = e?.values[1] || 'pedro'
var lastname = e?.values[2] || 'pascal'
var salary = e?.values[3] || '300000'
var title = e?.values[4] || 'best friend'
var startdate = e?.values[5] || '2018-08-22'
var hiringmanager = e?.values[6] || 'Oscar Isaac'

Tips and Reminders

Here are a few bonus last steps for how we made this a smoother process:

Tip 1: Create a slack channel with your hiring managers for focused collaboration and transparency on hiring.

Tip 2: Create a notification on your hiring slack channel using Zapier so that people get excited about hiring!

Tip 3: Have another google doc with your additional clauses/edits for cases that don’t fit your template. For us this meant contracts where employees are:

• part-time
• in senior roles (who may be required clauses related to non-compete or notice periods)
• receiving commission/bonus schemes

Tip 4: Maybe don’t automate all the things with third party tools…

Great — you’ve created a process where instead of 5 steps there are two. Maybe you’re thinking now.. why not just create a form that automatically sends the contract to the candidate? You can definitely add more complexity but some products and processes (including hiring) should have a human through the loop, meaning they should require a human to make that final decision or press that final send button.

One of the reasons some of these pre-made tools aren’t fit for purpose is that they create things automatically based on optimistic triggers. For example, your HRIS might be linked to multiple tools. If you add in a potential candidate to your HRIS so they can sign the contract but then they pull out of the process, it may trigger other things (like creating an email or a task list) that you then have to go and undo and apologise to the hiring manager for being inundated with false notifications. This adds in a layer of anxiety and frustration that could be avoided.

Tip 5: As we’ve written about above there are various ways you can automate your processes through things like Zapier, Juro, Bob, or Apps Scripts. A final note of operational caution is to make sure what access or data you may be giving away by doing so. One of the reasons we went with writing our own Apps Script instead of getting a chrome extension was so that we weren’t giving control or access to a third party we weren’t ready to trust.

Systems Thinking

Time is an incredibly valuable asset in a busy team, and reducing any friction that could lead to people having more time in their day to focus on important tasks, is definitely worth a change in your process.

These types of processes don’t take one person either! Remember to bring people along and co-create with your colleagues. If (like me) you’re the only people person or ops person at your company, partner with someone in marketing, product, and engineering to make sure these tools come to life and get used. Not only that, by bringing along experts from different teams, you’ll be able to cut down the time it takes to build these products and get people onboarded at the same time.

You do not rise to the level of your goals. You fall to the level of your systems. So, if your goal is to have a positive and formidable experience when hiring — look to your systems first.