Parents Evening Appointment System

Parents Evening Appointment System

//PART 2
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Admin')
    .addItem('Set up forms & sheets', 'setUpForms')
    .addToUi();
}

function setUpForms() {
  //Folder to store forms in - Add your own folder ID here
  const formFolder = DriveApp.getFolderById('1iYRITqL3JhKtIzXNlVVzFx1WAgbWW38T');

  //Get master form IDs (Add your own IDs here)
  const formMaster1615 = DriveApp.getFileById('1CA6lrD5ABPNW1WChaeuyfmPfh0Wlo5lhdNRCLamE3Cs');
  const formMaster1745 = DriveApp.getFileById('1Gqf_rcq4NtA4GvUXkPfPYOSbnwvLn3EzQbZECe_WxeE');

  //Get formLinks sheet and data
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ssId = ss.getId();
  const shFormLinks = ss.getSheetByName("formLinks");
  const [flHeader, ...flData] = shFormLinks.getDataRange().getValues();

  //Get the data on the students sheet
  const shStudents = ss.getSheetByName("students");
  const [studentsHeader, ...studentData] = shStudents.getDataRange().getValues();

  //Loop down groups and make copy of & rename either 1615 or 1745 form
  for (rw in flData) {
    //Get group info from formLinks sheet and into variables
    let [groupNo, group, time] = flData[rw];

    time = time.toString();
    let newForm;
    if (time === "1615") {
      newForm = formMaster1615.makeCopy(groupNo + "-" + group, formFolder);
    }
    else if (time === "1745") {
      newForm = formMaster1745.makeCopy(groupNo + "-" + group, formFolder);
    }

    //Add form URL to formLinks sheet & form to spreadsheet
    let newFormId = newForm.getId();
    let openNewForm = FormApp.openById(newFormId);
    let newFormUrl = openNewForm.getPublishedUrl();

    shFormLinks.getRange(Number(rw) + 2, 4).setValue(newFormUrl);
    openNewForm.setDestination(FormApp.DestinationType.SPREADSHEET, ssId);
    SpreadsheetApp.flush();

    //Rename form response sheet 
    let sheets = ss.getSheets();
    let newFRSheet = sheets[0];
    newFRSheet.setName("F" + groupNo);

    //Move sheet to the right
    ss.setActiveSheet(newFRSheet);
    ss.moveActiveSheet(sheets.length);

    //Set up APPOINTMENT SHEETS     
    //Loop down student list looking for group no   
    let date, teacher, room;
    for (s in studentData) {
      let sd = studentData[s];
      if (sd[0] === groupNo) {
        date = sd[7];
        teacher = sd[4];
        room = sd[6];
        break;
      }
    }

    //Create group appointment sheet, rename & add group details    
    const shMaster = ss.getSheetByName("master");
    shMaster.showSheet();
    let newAppSheet = shMaster.copyTo(ss);
    newAppSheet.setName(groupNo);
    let details = [[date], [group], [teacher], [room]];
    newAppSheet.getRange("B3:B6").setValues(details);
  };
  shMaster.hideSheet();

  // Create new trigger
  ScriptApp.newTrigger("onFormSubmit")
    .forSpreadsheet(ss).onFormSubmit().create();

  ss.toast("Forms and sheets set up.");
}

//PART 3
function onFormSubmit(e) {
  //Get appointment & corresponding group from latest form submission
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheetName = e.range.getSheet().getName();
  const shResponses = ss.getSheetByName(sheetName);
  const lastRow = shResponses.getLastRow();

  //Get latest form submission and assign values to variables 
  const submission = shResponses.getRange(lastRow, 1, 1, 9).getValues()[0];

  //Get current appointments array from Form
  const formUrl = shResponses.getFormUrl();
  const form = FormApp.openByUrl(formUrl);
  const items = form.getItems();
  const formClassList = items[6].asMultipleChoiceItem();
  const choices = formClassList.getChoices();
  let timesArray = [];
  for (ch in choices) {
    timesArray.push(choices[ch].getValue());
  }

  //Remove latest submitted time from array
  for (cc in timesArray) {
    let time = submission[7];
    if (timesArray[cc] === time) {
      let indexApp = timesArray.indexOf(time);
      timesArray.splice(indexApp, 1);
    }
  }

  //Update Form with times left     
  formClassList.setChoiceValues(timesArray);

  //Set QUERY formula if it's the first submission
  if (lastRow === 2) {
    ss.getSheetByName(groupNo).getRange("A9")
      .setFormula("=IFERROR(QUERY('" + sheetName +
        "'!A2:I60;\"select H, B where G='\"&$B3&\"' order by H asc\");\"Error\")");
  }

  //Send confirmation email
  sendEmail(submission);
}

function sendEmail(submission) {
  //Get individual data from submission - ignore first column
  const [, student, group, teacher, building, classroom, date, time, email] = submission;

  //Set up email
  let emailSubject = "APPOINTMENT CONFIRMATION - " + student;

  let emailBody = HtmlService.createTemplateFromFile('email')
    .evaluate().getContent();
  emailBody = emailBody.replace('#STUDENT#', student);
  emailBody = emailBody.replace('#GROUP#', group);
  emailBody = emailBody.replace('#DATE#', date);
  emailBody = emailBody.replace('#TIME#', time);
  emailBody = emailBody.replace('#BUILDING#', building);
  emailBody = emailBody.replace('#CLASSROOM#', classroom);
  emailBody = emailBody.replace('#TEACHER#', teacher);

  //Send email or if you have received daily limit, register it on the toEmail sheet
  if (MailApp.getRemainingDailyQuota() > 0) {
    MailApp.sendEmail(email, emailSubject, emailBody, {
      htmlBody: emailBody, replyTo: 'bazrobertsbooks@gmail.com'
    });
  }

  else {
    const toEmailSheet = ss.getSheetByName("toEmail");
    const appDetails = [student, group, teacher, building, classroom, date, time, email];
    toEmailSheet.appendRow(appDetails);
  }
}

HTML - email.html
<!DOCTYPE html>
<html>

<head>
  <base target="_top">
    <style>
    p, h3 {
      font-family: verdana, Helvetica, sans-serif;
    }
  </style>
</head>

<body>
  <img src="https://docs.google.com/uc?id=14lNpZR6SS6ZOHQlzq_E2HbdsbhP0jNVb" height='70' width='260'>
  <h3>PARENTS EVENING APPOINTMENT CONFIRMATION</h3>
  <hr>
  <p>Below are the details of your appointment:</p>
  <p><strong>STUDENT: </strong>#STUDENT#</p>
  <p><strong>GROUP: </strong>#GROUP#</p>
  <p><strong>DATE: </strong>#DATE#</p>
  <p><strong>TIME: </strong>#TIME#</p>
  <p><strong>BUILDING: </strong>#BUILDING#</p>
  <p><strong>CLASSROOM: </strong>#CLASSROOM#</p>
  <p><strong>TEACHER: </strong>#TEACHER#</p>
  <p>Regards,</p>
  <p>Foreign Language Department</p>

</body>

</html>