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.

doctorOfficeReservation.js

// 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();
}
*/