Email employee day off request to managers
I'm a big fan of Google's suite of products. Not only are they free and easy to use, but they can be integrated with each other to automate many processes. For example, you can use Google Forms to enter data Google Sheets that can be summarized in a report that is emailed to your team every week.
In this guide, I'll show you how to build an application that integrates Google Forms, Gmail, and Google Sheets. The application will allow an employee to submit a request for days off, automatically send the request via email to their manager, and allow their manager to approve/deny the request via a link in the email. To do this, we'll also need to use the powerful Google Apps Script.
Let's start by creating a simple form to accept Name, Date, Type of Day Off, and the Manager's Email in Google Forms.
We'll store the responses in a new Google Sheet called Day Off Request (Responses).
In the Google Sheet, we need to have the column headers for each of the form fields. In addition, want to add three new columns to store the requestor's email address, a unique ID for each request, and the status of the request. Here is what the sheet should look like.
Once this is setup, we can go back to the Form and then to the Script Editor where we want to write a function that does a few things.
1) Identify the requestor's email address and save it to the sheet.
2) Get the response ID and save it to the sheet.
3) Set the status of the request to 'Pending'.
4) Insert approve and deny links within the body of the email
5) Email the manager the request information
Here's the full code.
We want to run this function every time the form is submitted, so we need to create a trigger.
We've created the Form, saved the data to Sheets, and sent an email with the request to the manager. The email will look something like this:
Re: A time-off request has been submitted
Manager's Email: email@example.com
Click below link to approve:
Click below link to deny:
Now, you might be wondering about the approve/deny links and how they work.
The links make it easy for the manager to approve/deny the request by passing certain information within the URL. In this case, we need to pass the ID of the response and the status, either 'Approved' or 'Denied', as parameters in the URL. To read and process these parameters, we need to use Google Apps Script.
Essentially we want to update the status of a particular response in Google Sheets based on which link the manager clicked.
To do this, we'll use the doGet(e) function.
The function retrieves the response ID and status from the parameters in the URL, finds the row that matches the response ID in the sheet where we saved the responses, and updates the status value.
It would be great if we could also notify the requestor that their request has either been approved or denied.
So let's send an email to the requestor after the status has been updated. We'll update a few lines from the function above.
Here's what the email will look like.
Re: Your request for time-off has been approved
Your request for time-off (Vacation) on Thu, Aug 9, 2018 has been approved.
And there you have it. We've created an application that allows an employee to submit a time-off request using Google Forms, save the data in Google Sheets, send an email to get a manager's approval/denial, process the approval/denial, and notify the employee of the decision via email.
We could even display the day's off in a shared Google Calendar with the current status of the request, but that's a post for another time.