Skip to content

Instantly share code, notes, and snippets.

@kurtkaiser
Last active September 3, 2019 10:34
Show Gist options
  • Save kurtkaiser/405ab80841e93d70f1b272d4d2daa4e3 to your computer and use it in GitHub Desktop.
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.
// 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);
}
// 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