DIY – Resource Guru Outlook Calendar Integration with Zapier and AWS

The company I work for uses Resource Guru as a central resource scheduling tool for our engineers. Project Managers love it because they have a consolidated view of all of the engineers’ schedules, but for the engineers, it’s ANOTHER tool they have to log into. The main complaint I hear is: “Why should I have to manage another schedule, when I already have an Outlook calendar.” I agree.

Resource Guru doesn’t currently have an integration to add schedule additions/updates/deletes to Outlook. There are a few options that get us pretty close, but were lacking in some way.

Zap with Zapier!

Zapier is a really cool task service that allows you create ‘Zaps’ to connect various online apps and services. There are hundreds of things you can do like, create a Google calendar entry from an Evernote task list, or share new tweets from Twitter into Slack. The list goes on, but one of the coolest features is the ability to create an action that executes an AWS Lambda function.

Webhooks?

Resource Guru allows you to create Webhooks. These Webhooks can POST the data generated from Resource Guru to an external website. Zapier let’s you create a ‘receiving URL’ that accepts the JSON data being sent from the webhook. Once we get that data into Zapier, we can take action on it.

Why not use the existing Calendar Zap actions?

Zapier can already create calendar events though, can’t it? Believe me, I really wanted to use the built in Gmail and Zapier SMTP functions. Not only could I have kept everything within Zapier, but also because using Lambda means custom code. It’s not terrible, but supportability becomes a problem.

I ran into two main issues trying to get this to work natively. The problem isn’t getting the data into Zapier, it was getting it into the calendar invite. There are (3) integrations I tried.

  1. Gmail Calendar: Even the detailed event wouldn’t allow me to use values passed from the Resource Guru Webhook. I wanted to customize the notification and details the engineer would see in both the email and invite.
  2. Gmail & Zapier SMTP: These looked promising, but you aren’t able to create the RAW email necessary to embed the attachment. Also, I wasn’t able to create a file object on the fly within the <code> action that I could pass in as the attachment. This is important because we want to control the Calendar UID. This lets us automatically update calendar invites when an update happens in Resource Guru.

Getting it done with AWS Lambda

There are a few reasons I wanted more control over the actual invite. First, it meant I could add any of the information sent from Resource Guru to the email/calendar invite. Engineers could see the details of customer, project, notes, etc., at a glance. Second, it meant I could control the calendar UID. By using the value of the schedule entry ID (payload_Id) I could make all updates/deletes to the existing calendar entry use the same ID. The result is, as project managers update schedules, changes are automatically reflected on the engineers schedules.

The basic workflow looks like this:
  1. Project Manager makes/updates schedule entry in Resource Guru
  2. Resource Guru sends update to Zapier via Webhook
  3. Zapier sends the update to AWS Lambda
  4. Lambda generates the custom message and calendar invite
  5. AWS Simple Email Service send the invite to the engineer.
So what did it take to get this to work?
  1. Create a Zapier Webhook Trigger and set it up to ‘Catch a hook’. Then copy down the URL on the ‘View Webhook’ dialog.

  2. Setup the Webhook in Resource Guru. If you haven’t done this, check out the following: Resource Guru Webhooks. Here is an example of my Webhook. We are sending all ‘Booking’ changes to the ‘Receiving URL’. 
  3. Setup an AWS Lambda and Simple Email service (SES) Account in the AWS Identity and Access Management Console. Key thing here is you will need to create an account that has access to AWS Lambda and SES. I have given my account more rights than it probably needs.
  4. Create a Lambda function called ‘resourceGuruCalendarUpdate’. The code can be found on Github: resourceGuruCalendarUpdate_clean.js Note, you will have to update a few of the values (email addresses, and access ID) to get it to work in your environment.
  5. Create Lambda action in Zapier. During the setup we configure Zapier to use the AWS account we create previously.
  6. Now we need to tell the Lambda Action what values we want to pass to AWS. Setup the ‘Edit Template’ option in the Lambda Action by adding the following values:

Setting up email with AWS Simple Email Service

The final step is to enable SES to send email messages to the engineers. The Lambda function imports the SES methods from the aws-sdk and should take care of all the heavy lifting. We do however, have to ‘authorize’ the email address OR the domain in SES.

NOTE: If you own the domain and can edit the DNS entries, you should be able to do this for the single @yourdomain.com you are sending to. In my case, I don’t so I have to have each individual approve receiving the email.

  1. Navigate to the AWS SES Admin console and select ‘Email Addresses’, then click ‘Verify a New Email Address’
  2. Each user will have to click the AWS approval link that gets sent to their email.

Common Issue:

The most common issue I have found is users have not clicked the verified/approval message that gets sent to them. An easy place to look is the CloudWatch logs for the Lambda function within AWS. You will see a very clear message indicating that that SES is not authorized to send to the recipient.

Test It!

Now you should be able to test. You can simply create a Resource Guru schedule entry that should activate the process.

Simplify Testing in your own Resource Guru Environment

I didn’t want to mess with my organization’s Resource Guru account, so I just created a temporary account. You can use the same receiving Webhook URL for your Zapier Zap. Now you can make all the changes you need without effecting the production calendar.

Future thoughts:

  1. Duration Logic: Resource Guru provides a child element in the JSON response called Payload.Durations. This is an array of hash entries. Here is a sample:

    What I haven’t been able to figure out, is how to get the child items (date, duration, start_time, etc., into Lambda. When Zapier passes them, they come in as some funky csv type value. Anyway, if you can get that value in, you can update the logic so it will actually do partial day calendar invites.

  2. Crude Calendar Creation: I mention it in the script. I am building the calendar attachment in the script, but it isn’t very clean. There is a ics.js module you may be able to use to make it better.

Let me know what you think in the comments below. Thanks for reading!

-Martin @ubergiek

 

Leave a Reply

Your email address will not be published. Required fields are marked *

*