How to turn a Google Sheet into a simple vacation requesting app
Introduction
You may have several reasons why you want to have a spreadsheet app to process vacation requests. Whether it’s because you want to prototype what features you need before buying a solution or building it from scratch, or maybe you don’t have budget to purchase a tool at this time, or maybe because your team is growing and you are presently doing all this by hand (via email or paperwork). Everyone has different needs.
One of our interns last year (Made LaPuerta), built a simple vacation app from a spreadsheet as a prototype, and so I was inspired to recreate a similar solution that helps approve time requests from a Google Sheets, sends notification emails, logs each activity in an automated manner, and creates a calendar event.
It works like this…
The sheet has 3 functions you can activate via a custom menu at the top of the sheet.
The first one (Form setup) creates an intake form that you share with users to submit time off requests, and their responses will arrive in your sheet automatically. The second function (Column setup) creates the columns needed to manage approvals in the sheet. The third (Notify employees) enables you to send email notifications once you have marked a submission as approved or not approved in the sheet.
Table of Contents
- Tools used
- Steps to try it out
- Access the script’s code
Tools used
- Google Sheets (anyone can copy the sheet to re-use it’s automation script made using Apps Script)
- Google Form (auto-generated by the script in the sheet)
- Google Calendar
Steps to try it out
- Make a copy of this sheet here.
- Wait for a few seconds for a custom menu at the top to appear called “Time off.”
- Click Time off > Form setup
4. Click “continue” and then allow when an authorization window appears.
Note: if you receive a message that says “This app isn’t verified” click “Advanced” and then “Go to <name of the sheet>.
If asked for an additional step…
5. A new sheet called “Form Responses 1” will be created with headers populated from column A to I.
Important: if this new sheet is not created, go ahead and re-click Time off > Form setup one more time from that tab and that should do the trick.
6. A new menu item at the top of that sheet called Form will appear (wait a few seconds). Click Form > Go to live form to visit the form created by the script titled “Request time off”
Note: You can share this form with your staff, and their responses will arrive in your sheet. I recommend first testing it with your own email. And if for whatever reason you wish to delete that sheet and start a new one, you need to first unlink the form by choosing Form > Unlink form first and then click the Time off > Form setup menu option again.
7. If you wish to receive email notifications every time someone submits a time-off request, ensure to enable notifications in edit mode of the Form.
Note: if another person other than you will be managing this process and wants to receive notifications. They need to have edit rights to the Form and enable the notification alert as well.
Optionally if requestors wish to receive a confirmation of their submission via email, instruct them to toggle the option before clicking Submit:
8. Email alerts will look like this:
Tip: you can create an email filter to label emails like these and check that label once a day or set it up as a section if you have multiple inboxes setup in your Gmail.
9. Return to your spreadsheet, and click the “Form Responses 1” tab, then select the Time Off > Column setup custom menu at the top. This will create two new columns called Approval and Notification Status that contain drop-down values to choose from.
Caution: make sure you click the Time Off menu from the correct tab called “Form Responses 1", otherwise the new columns will be created in another tab.
10. Next, test out an entry by filling out the form with your email address and optionally add an email address in the “Additional email” field that you would like to also be notified once your request is approved. This is ideal for CCing a manager, a peer, or Google Group when your request is approved.
Note: if you use your Workspace email address (formerly known as a G Suite email address), the “Additional email” address must be a user within your domain and you must be signed into your Workspace account.
11. Responses will arrive in your sheet like this.
12. To approve or reject an incoming time-off request, choose an option in column H of each row of requests.
13. To continue to test out your first entry, choose Approve in column H, then click Time off > Notify employees.
14. After a few seconds, column I will be populated with the word “Notified”
and an “all day calendar invite” will be created on the requestor’s calendar. The calendar invite will contain a message in the description that they request was approved.
They will also receive an email notification like this:
Optionally if an “Additional email” was populated, that email address will be CCed in the email and also be included in the time off calendar invite as a guest.
15. If a request is marked as not approved in column H of the sheet, and you click Time Off > Notify employees, then an email notification is sent to the person who submitted the time off request that they were not approved. The email will look like this:
Tip: if you wish to retest a different approval path (approve or not approve) for the same entry you entered in the sheet, simply change the “Notified” column (column I) and then click Time off > Notify employees once more.
Visiting the code
In your sheet, click Tools > Script editor and you will find the script with comments.
