title: Calendar Booking Using Google Forms
body: <p>I recently came across a video from Kurt Kaiser where he goes through the process of creating an Appointment Booking System.</p>
  <p>I'm new to coding and I followed his steps along with having the code that he has shared and I cannot seem to get this to work. For some reason after I submit the form and approve the request it never puts the event in my calendar.</p>
  <p>I'm calling the calendar in the script I made by the ID. Is there something else I need to do after I create the calendar in order for this to work?</p>
  <p>Do I need to make my calendar public in google?</p>
  <p>Again I followed his instruction video to the T and still don't get the events in the calendar.</p>
  <p>I also get an error for name under the :// Creates a calendar event using the submitted data in the code below, I believe it said it wasn't defined. But maybe that's because I was just running the script?</p>
  <p>I have tried to reach out to the author of the code but he has not responded.</p>
  <p>Again he has freely shared this code from his website.
  Any help would greatly be appreciated as I have been pulling my hair out on this project and trying to come up with a booking system that uses google forms for my team.</p>
  <pre><code> `// Calendar Reservation Approval System
      // Kurt Kaiser, 2018
      // All Rights Reserved
  
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getActiveSheet();
      var lastRow = sheet.getLastRow();
      var lastColumn = sheet.getLastColumn();
  
      **// Calendar to output requests
      var calendar =  CalendarApp.getCalendarById('bfpkrilvj3avj0srbelt3n89rc@group.calendar.google.com');**
  
      // Creates an object from the last form submission
      function Submission(row){
      this.timestamp = sheet.getRange(row, 1).getValue();
      this.name = sheet.getRange(row, 2).getValue();
      this.reason = sheet.getRange(row, 3).getValue();
      this.date = new Date(sheet.getRange(row, 4).getValue());
      this.dateString = (this.date.getMonth() + 1) + '/' + 
      this.date.getDate() + '/' + this.date.getYear();
      this.time = sheet.getRange(row,5).getValue();
      this.timeString = this.time.toLocaleTimeString();
      this.email = sheet.getRange(row, 6).getValue();
      // Adjust time and make end time
      this.date.setHours(this.time.getHours());
      this.date.setMinutes(this.time.getMinutes());
      this.endTime = new Date(this.date);
      this.endTime.setHours(this.time.getHours() + 2);
      }
  
      // Check for conflicting events
      function getConflicts(request) {
      var conflicts = calendar.getEvents(request.date, request.endTime);
      if (conflicts.length &lt; 1) {
      request.status = &quot;New&quot;;
      } else {
      request.status = &quot;Conflict&quot;;
      sheet.getRange(lastRow, lastColumn - 1).setValue(&quot;Reject&quot;);
      sheet.getRange(lastRow, lastColumn).setValue(&quot;Sent: Conflict&quot;);
       }
      }
  
      // Convert data of status and notified columns into array
      function StatusObject(){
      this.statusArray = sheet.getRange(1, lastColumn -1, lastRow, 1).getValues();
      this.notifiedArray = sheet.getRange(1, lastColumn, lastRow, 1).getValues();
      this.statusArray = [].concat.apply([], this.statusArray);
    this.notifiedArray = [].concat.apply([], this.notifiedArray);
  }
  
  // Get the index of the row that has had a status change
  function getChangeIndex(statusChange){
    statusChange.index = statusChange.notifiedArray.indexOf(&quot;&quot;);
    statusChange.row = statusChange.index + 1;
    if (statusChange.index == -1){
      return;
    } else if (statusChange.statusArray[statusChange.index] != &quot;&quot;) {
      statusChange.status = statusChange.statusArray[statusChange.index];
      sheet.getRange(statusChange.row, lastColumn).setValue(&quot;Sent: &quot; + statusChange.status);
      statusChange.notifiedArray[statusChange.index] = &quot;update&quot;;
    } else {
      statusChange.status = statusChange.statusArray[statusChange.index];
      statusChange.notifiedArray[statusChange.index] = &quot;no update&quot;;
    }
  }
  
  // Draft contents for emails depending on needed message
  function draftEmail(request){
    request.buttonLink = &quot;button URL&quot; // I would have the URL in place here for the button
    request.buttonText = &quot;New Request&quot;;
    switch (request.status) {
      case &quot;New&quot;:
        request.subject = &quot;Request for &quot; + request.dateString + &quot; Appointment Received&quot;;
        request.header = &quot;Request Received&quot;;
        request.message = &quot;Once the request has been reviewed you will receive an email updating you on it.&quot;;
        break;
      case &quot;New2&quot;:
        request.email = &quot;myemail@address.com&quot;;
        request.subject = &quot;New Request for &quot; + request.dateString;
        request.header = &quot;Request Received&quot;;
        request.message = &quot;A new request needs to be reviewed.&quot;;
        request.buttonLink = &quot;https://link to the spreadsheet&quot;;// I would have the URL to my spreadsheet here
        request.buttonText = &quot;View Request&quot;;
        break;
      case &quot;Approve&quot;:
        request.subject = &quot;Confirmation: Appointment for &quot; + request.dateString + &quot; has been scheduled&quot;;
        request.header = &quot;Confirmation&quot;;
        request.message = &quot;Your appointment has been scheduled.&quot;;
        break;
      case &quot;Conflict&quot;:
        request.subject = &quot;Conflict with &quot; + request.dateString + &quot; Appointment Request&quot;;
        request.header = &quot;Conflict&quot;;
        request.message = &quot;There was a scheduling conflict. Please reschedule.&quot;;
        request.buttonText = &quot;Reschedule&quot;;
        break;
      case &quot;Reject&quot;:
        request.subject = &quot;Update on Appointment Requested for &quot; + request.dateString;
        request.header = &quot;Reschedule&quot;;
        request.message = &quot;Unfortunately the request times does not work. Could &quot;+
          &quot;we reschedule?&quot;;
        request.buttonText = &quot;Reschedule&quot;;
        break;
    }
  }
  
  // Creates a calendar event using the submitted data
  function updateCalendar(request){
    var event = calendar.createEvent(
      request.name,
      request.date,
      request.endTime
      )
  }
  
  // Send Email
  function sendEmail(request){
    MailApp.sendEmail({
      to: request.email,
      subject: request.subject,
      htmlBody: makeEmail(request)
    })
  }
  
  // --------------------  Main Functions ---------------------
  function onFormSubmission(){
    var request = new Submission(lastRow);
    getConflicts(request);
    draftEmail(request);
    sendEmail(request);
    if (request.status == &quot;New&quot;){
      request.status = &quot;New2&quot;;
      draftEmail(request);
      sendEmail(request);
    }
  }
  
  // Triggered function to check if any status has changed
  function onEdit(){
    var statusChange = new StatusObject();
    while (true){
      getChangeIndex(statusChange);
      if (statusChange.index == -1){
        return;
      } else {
        var request = new Submission(statusChange.row);
        if (statusChange.status){
          request.status = statusChange.status;
          if (statusChange.status == &quot;Approve&quot;){
            updateCalendar(request);
          }
          draftEmail(request);
          sendEmail(request);
        }
      }
    }
  }`
  </code></pre>