Last active
September 3, 2019 10:34
-
-
Save kurtkaiser/405ab80841e93d70f1b272d4d2daa4e3 to your computer and use it in GitHub Desktop.
Using Google Apps Script, this script is added to a sheet file that a Google Form feeds into. Using the submitted requests, the program manages booking calendars and alerts users of the status of their reservations.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// Room Reservation System Video | |
// Kurt Kaiser, 2018 | |
// All rights reserved | |
var ss = SpreadsheetApp.getActiveSpreadsheet(); | |
var sheet = ss.getSheets()[0]; | |
var lastRow = sheet.getLastRow(); | |
var lastColumn = sheet.getLastColumn(); | |
// Calendars to output appointments to | |
var cal101 = CalendarApp.getCalendarById('[email protected]'); | |
var cal102 = CalendarApp.getCalendarById('[email protected]'); | |
var cal103 = CalendarApp.getCalendarById('[email protected]'); | |
var cal202 = CalendarApp.getCalendarById('[email protected]'); | |
var cal103 = CalendarApp.getCalendarById('[email protected]'); | |
// Create an object from user submission | |
function Submission(){ | |
var row = lastRow; | |
this.timestamp = sheet.getRange(row, 1).getValue(); | |
this.name = sheet.getRange(row, 2).getValue(); | |
this.email = sheet.getRange(row, 3).getValue(); | |
this.reason = sheet.getRange(row, 4).getValue(); | |
this.date = sheet.getRange(row, 5).getValue(); | |
this.time = sheet.getRange(row, 6).getValue(); | |
this.duration = sheet.getRange(row, 7).getValue(); | |
this.room = sheet.getRange(row, 8).getValue(); | |
// Info not from spreadsheet | |
this.roomInt = this.room.replace(/\D+/g, ''); | |
this.status; | |
this.dateString = (this.date.getMonth() + 1) + '/' + this.date.getDate() + '/' + this.date.getYear(); | |
this.timeString = this.time.toLocaleTimeString(); | |
this.date.setHours(this.time.getHours()); | |
this.date.setMinutes(this.time.getMinutes()); | |
this.calendar = eval('cal' + String(this.roomInt)); | |
return this; | |
} | |
// Use duration to create endTime variable | |
function getEndTime(request){ | |
request.endTime = new Date(request.date); | |
switch (request.duration){ | |
case "30 minutes": | |
request.endTime.setMinutes(request.date.getMinutes() + 30); | |
request.endTimeString = request.endTime.toLocaleTimeString(); | |
break; | |
case "45 minutes": | |
request.endTime.setMinutes(request.date.getMinutes() + 45); | |
request.endTimeString = request.endTime.toLocaleTimeString(); | |
break; | |
case "1 hour": | |
request.endTime.setMinutes(request.date.getMinutes() + 60); | |
request.endTimeString = request.endTime.toLocaleTimeString(); | |
break; | |
case "2 hours": | |
request.endTime.setMinutes(request.date.getMinutes() + 120); | |
request.endTimeString = request.endTime.toLocaleTimeString(); | |
break; | |
} | |
} | |
// Check for appointment conflicts | |
function getConflicts(request){ | |
var conflicts = request.calendar.getEvents(request.date, request.endTime); | |
if (conflicts.length < 1) { | |
request.status = "Approve"; | |
} else { | |
request.status = "Conflict"; | |
} | |
} | |
function draftEmail(request){ | |
request.buttonLink = "https://goo.gl/forms/JX43ZkcyzVKHen4I3"; | |
request.buttonText = "New Request"; | |
switch (request.status) { | |
case "Approve": | |
request.subject = "Confirmation: " + request.room + " Reservation for " + request.dateString; | |
request.header = "Confirmation"; | |
request.message = "Your room reservation has been scheduled."; | |
break; | |
case "Conflict": | |
request.subject = "Conflict with " + request.room + "Reservation for " + request.dateString; | |
request.header = "Conflict"; | |
request.message = "There is a scheduling conflict. Please pick another room or time." | |
request.buttonText = "Reschedule"; | |
break; | |
} | |
} | |
function updateCalendar(request){ | |
var event = request.calendar.createEvent( | |
request.name, | |
request.date, | |
request.endTime | |
) | |
} | |
function sendEmail(request){ | |
MailApp.sendEmail({ | |
to: request.email, | |
subject: request.header, | |
htmlBody: makeEmail(request) | |
}) | |
sheet.getRange(lastRow, lastColumn).setValue("Sent: " + request.status); | |
} | |
// --------------- main -------------------- | |
function main(){ | |
var request = new Submission(); | |
getEndTime(request); | |
getConflicts(request); | |
draftEmail(request); | |
if (request.status == "Approve") updateCalendar(request); | |
sendEmail(request); | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// Room Reservation System | |
// Kurt Kaiser, 2018 | |
// All rights reserved | |
function makeEmail(request) { | |
return ( | |
'<!DOCTYPE html><html><head><base target="_top"></head><body><div style="text-align: center;' + | |
'font-family: Arial;"><div id="center" style="width:300px;border: 2px dotted grey;background:' + | |
'#ececec; margin:25px;margin-left:auto; margin-right:auto;padding:15px;"><img src="https://upload.' + | |
"wikimedia.org/wikipedia/commons/thumb/6/69/Calendar_font_awesome.svg/512px-Calendar_font_awesome" + | |
'.svg.png"width="180" style="margin:10px 0px"><br /><div style=" border: 2px dotted grey;' + | |
'background:white;margin-right:auto; margin-left:auto; padding:10px;"><h2>' + | |
request.header + | |
"</h2><h3>" + | |
request.message + | |
"<br /><br/>" + | |
request.name + | |
"<br />" + | |
request.room + | |
"<br />" + | |
request.reason + | |
"<br />" + | |
request.dateString + | |
"<br />" + | |
request.timeString.slice(0, request.timeString.length - 4) + | |
"<br />" + | |
request.endTimeString.slice(0, request.endTimeString.length - 4) + | |
"<br /></h3><br />" + | |
'<a href="' + | |
request.buttonLink + | |
'" class="btn" style="-webkit-border-radius: 28;' + | |
"-moz-border-radius: 5;border-radius: 5px;font-family: Arial; color: #ffffff;font-size: 15px;" + | |
'background: #ff7878;padding:8px 20px 8px 20px;text-decoration: none;">' + | |
request.buttonText + | |
'</a><br /><br /></div></div><div><p style="font-size:12px">' + | |
'Coded by<a href="https://www.linkedin.com/in/kurtkaiser/">' + | |
'Kurt Kaiser</a><br /></p></div></body></html>' | |
); | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment