A google app script that makes a google form and google spreadsheet function as an out of office request and approval system

Create a google form with questions you would like to know when someone submits a leave request. The following questions used to work with this script are as followed and in this order:

When asking for their leave dates, make sure to use the date question feature in forms. If not, you have to convert it to a date time format in the script itself.

You can use the automatic generated one with the forms or link a google spreadsheet you personally created. After linking the spreadsheet sheet to the form, create a new tab/sheet on the bottom called "Archive" where you will store processed requests. Optional: Rename the linked tab with the purple icon as "Pending". Add a column after your question columns called "Approved" where the approver will process requests

a) Click the app script editor and a new script file should be automatically generated for you

b) Rename the file to "out-of-office-script" and delete the generated contents. Paste in all the contents from out-of-office-script.gs

c) Adjust the customizable information to your liking (email bodies, variable and data changes, calendar IDs, etc.) - some areas are labeled for you and are required for you to change them in order for it to work.

d) Save!

Install a form trigger to run the notify function when someone submits a request. Click on the triggers category on the left and then click add trigger on the bottom right. Create a trigger to run the notify function on a new form submit.

After submitting a leave request, both the manager (approver) and submitter will be notified of the submission. The request will populate the linked sheet and the approver can open the sheet at anytime to manage requests.

The approved column handles "yes" and "no". Blank cells or other text will be ignored. After approving and denying requests, the manager can press the schedule events tab and press the set time off button. Approved and denied rows will be processed and moved to the Archive sheet, all other rows will be ignored to be processed another time.

If approved, a new event will be created and the submitter will receive an approval email and calendar invite to their out of office. If denied, the submitter will receive a denied email. All emails will be sent from the user who ran the set time off button.