Last active
November 10, 2018 01:55
-
-
Save kurtkaiser/f016afd7918957127f0a07f99d05d83e to your computer and use it in GitHub Desktop.
Using Google Apps Script this program manages calendars for multiple doctors and rooms. It allows patients to submit requests for appointments and have them be viewed and approved by someone in the office.
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
// Doctor Appointment Booking System | |
// Kurt Kaiser, 2018 | |
// All rights reserved | |
// [email protected] | |
var ss = SpreadsheetApp.getActiveSpreadsheet(); | |
var sheet = ss.getSheets()[0]; | |
var lastRow = sheet.getLastRow(); | |
var lastColumn = sheet.getLastColumn(); | |
/* The Submission prototype is used when a new Google Form is submitted. It is | |
also used when the manager of the program makes a change, such as approving | |
an event. It creates an object of the row that needs to be checked. */ | |
function Request(row) { | |
/* For new submissions, row is always the last row. On edits, this function is | |
called using the row where the edit occured */ | |
this.row = row; | |
this.timestamp = sheet.getRange(row, 1).getValue(); | |
this.name = sheet.getRange(row, 2).getValue(); | |
/* Data like email, date and time are required for the program to run, | |
phone number or reason can be removed, but column numbers for other | |
values must be updated to match the correct column location */ | |
this.email = sheet.getRange(row, 3).getValue(); | |
this.reason = sheet.getRange(row, 4).getValue(); | |
this.doctor = sheet.getRange(row, 5).getValue(); // This is used for calendar names | |
this.date = new Date(sheet.getRange(row, 6).getValue()); | |
this.time = sheet.getRange(row, 7).getValue(); | |
this.phone = sheet.getRange(row, 8).getValue(); | |
// Create Strings, used in email confirmation messages | |
this.dateString = (this.date.getMonth() + 1) + '/' + this.date.getDate() + '/' + this.date.getYear(); | |
this.timeString = this.time.toLocaleTimeString(); | |
// Adjust date's time, date is set to midnight, change that to reflect the inputted time | |
this.date.setHours(this.time.getHours()); | |
this.date.setMinutes(this.time.getMinutes()); | |
this.duration = '20 minutes'; | |
// Calendar's name must match exactly what is in the spreadsheet | |
this.calDoctor = CalendarApp.getCalendarsByName(this.doctor); | |
this.calDoctor = this.calDoctor[0]; | |
// Info needed for the onEdit function | |
/* Columns for duration, room, apoproval, conflict and status can be changed, but | |
the lastColumn - # would need to be updated to reflect the correct column */ | |
if (!sheet.getRange(row, lastColumn - 2).isBlank() && | |
!sheet.getRange(row, lastColumn - 3).isBlank() && | |
!sheet.getRange(row, lastColumn - 4).isBlank()) { | |
this.duration = sheet.getRange(row, lastColumn - 4).getValue(); | |
this.room = String(sheet.getRange(row, lastColumn - 3).getValue()); | |
this.calRoom = CalendarApp.getCalendarsByName(this.room); | |
this.calRoom = this.calRoom[0]; | |
this.approve = sheet.getRange(row, lastColumn - 2).getValue(); | |
} | |
// Call function to get the end time of the event | |
this.endTime = getEndTime(this.date, this.duration); | |
this.endTimeString = this.endTime.toLocaleTimeString(); | |
} | |
/* This object constructor, Change, is used by the onEdit function, when | |
an edit is detected. It creates an object that holds all the data of the | |
last five columns, the status conflicts and approval columns are used | |
to check if rows need to be checked and updated. */ | |
function Change() { | |
this.statusArray = sheet.getRange(1, lastColumn, lastRow, 1).getValues(); | |
this.statusArray = [].concat.apply([], this.statusArray); | |
this.index = this.statusArray.indexOf(''); | |
if (this.index == -1) return; | |
this.durationArray = sheet.getRange(1, lastColumn - 4, lastRow, 1).getValues(); | |
this.durationArray = [].concat.apply([], this.durationArray); | |
this.roomArray = sheet.getRange(1, lastColumn - 3, lastRow, 1).getValues(); | |
this.roomArray = [].concat.apply([], this.roomArray); | |
this.approvalArray = sheet.getRange(1, lastColumn - 2, lastRow, 1).getValues(); | |
this.approvalArray = [].concat.apply([], this.approvalArray); | |
this.conflictsArray = sheet.getRange(1, lastColumn - 1, lastRow, 1).getValues(); | |
this.conflictsArray = [].concat.apply([], this.conflictsArray); | |
} | |
/* Creates an end time for an event using the duration, | |
if you have different options for duration, such as | |
a 45 minutes, you can add or change that below */ | |
function getEndTime(thisDate, duration) { | |
var endTime = new Date(thisDate); | |
var minutes; | |
switch (duration) { | |
case '20 minutes': | |
minutes = (thisDate.getMinutes() + 20); | |
case '30 minutes': | |
minutes = (thisDate.getMinutes() + 30); | |
case '1 hour': | |
minutes = (thisDate.getMinutes() + 60); | |
} | |
endTime.setMinutes(minutes); | |
return endTime; | |
} | |
// Check for conflicts on doctor's calendars | |
function getDoctorConflicts(request) { | |
var conflicts = request.calDoctor.getEvents(request.date, request.endTime); | |
if (conflicts.length > 0) { | |
return true; | |
} else { | |
return false; | |
} | |
} | |
// Check for room conflicts, used by onEdit | |
function getRoomConflicts(request) { | |
var conflicts = request.calRoom.getEvents(request.date, request.endTime); | |
if (conflicts.length > 0) { | |
return true; | |
} else { | |
return false; | |
} | |
} | |
// Creates the needed variables for emails, called in sendEmail function | |
function draftEmail(request) { | |
request.buttonLink = 'https://goo.gl/forms/hgIVpoGuzYdwcjWr2'; | |
request.buttonText = 'Submitted Request'; | |
switch (request.approve) { | |
case 'Submitted': | |
request.subject = 'Appointment Request for ' + request.dateString + ' Received'; | |
request.header = 'Request Received'; | |
request.message = 'Once your request has been reviewed you will receive and update.'; | |
break; | |
case 'Submitted 2': | |
request.email = '[email protected]'; // Email of person managing spreadsheet | |
request.subject = 'New Request Submitted: Appointment for ' + request.dateString; | |
request.header = 'Request Received'; | |
request.message = 'A new request needs to be reviewed.'; | |
request.buttonLink = 'https://docs.google.com/spreadsheets/d/1bdMZQRT--MsRJnEliLeF0i7v0U1fnLzs_QNIS-BjOZk/edit?usp=sharing'; | |
request.buttonText = 'View Request'; | |
break; | |
case 'Approve': | |
request.subject = 'Appointment Confirmation: ' + request.doctor + ' for ' + | |
request.dateString + ' at ' + request.timeString; | |
request.header = 'Appointment Confirmed'; | |
request.message = 'Your appointment has been scheduled.'; | |
break; | |
case 'Reject': | |
request.subject = 'Reschedule: Appointment Requested on ' + request.dateString; | |
request.header = 'Reschedule'; | |
request.message = 'The requested time does not work. Please reschedule.'; | |
request.buttonText = 'Reschedule'; | |
break; | |
} | |
} | |
// Returns HTML message body, called in sendEmail function | |
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/>Patient: " + request.name + | |
"<br />" + request.doctor + | |
"<br />Date: " + request.dateString + | |
"<br />Time: " + request.timeString.slice(0, request.timeString.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>' | |
); | |
} | |
// Create calendar events using gathered data | |
function updateCalendar(request) { | |
var event = request.calDoctor.createEvent( | |
request.name, | |
request.date, | |
request.endTime | |
) | |
var event2 = request.calRoom.createEvent( | |
request.name, | |
request.date, | |
request.endTime | |
) | |
} | |
// Ummm, this sends the email, yep | |
function sendEmail(request) { | |
draftEmail(request); | |
MailApp.sendEmail({ | |
to: request.email, | |
subject: request.subject, | |
htmlBody: makeEmail(request) | |
}) | |
} | |
// -------------------- Main -------------------- | |
function onFormSubmit() { | |
var request = new Request(lastRow); | |
request.approve = 'Submitted'; | |
sendEmail(request); | |
request.approve = 'Submitted 2'; | |
sendEmail(request); | |
} | |
// Trigged on edits, used for approval updates | |
function onEdit() { | |
var change = new Change(); | |
while (change.index != -1) { | |
change.approve = change.approvalArray[change.index]; | |
change.row = change.index + 1; | |
var request = new Request(change.row); | |
if (change.approve == 'Reject') { | |
change.statusArray[change.index] = 'Reject'; | |
request.approve = 'Reject'; | |
sendEmail(request); | |
sheet.getRange(change.row, lastColumn).setValue('Sent: Reject'); | |
} else { | |
getEndTime(request); | |
if (getDoctorConflicts(request)) { | |
change.statusArray[change.index] = 'Doctor Conflict'; | |
sheet.getRange(change.row, lastColumn - 1).setValue('Doctor Conflict'); | |
} else if (request.approve == "Approve") { | |
if (getRoomConflicts(request)) { | |
change.statusArray[change.index] = 'Room Conflict'; | |
sheet.getRange(change.row, lastColumn - 1).setValue('Room Conflict'); | |
} else { | |
change.statusArray[change.index] = 'Approve'; | |
updateCalendar(request); | |
sendEmail(request); | |
sheet.getRange(request.row, lastColumn).setValue('Sent: Approve'); | |
} | |
} else { | |
change.statusArray[change.index] = 'no issues'; | |
} | |
} | |
change.index = change.statusArray.indexOf(''); | |
} | |
} | |
/* | |
// Add a menu to allow user to call the onEdit function, if you don’t want to have an on edit trigger | |
function onOpen(e) { | |
SpreadsheetApp.getUi() | |
.createMenu('Reservations') | |
.addItem('Check Requests', 'onEdit') | |
.addToUi(); | |
} | |
*/ | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment