Parent Teacher Conference Scheduler

schedule.gs

function myFunction() {
  var ss = SpreadsheetApp.openById('1igifvI24bzlPIUxlpEMllbXsA9Wuk_lRGmAj21Tgy9o');
  var sheetsArray = ss.getSheets(); 
  
  for (var k = 5; k < sheetsArray.length; k++){
    var maxRow = 2;
    var sheet = sheetsArray[k];
    var row = 1;
  for (var i = 8; i < 16; i++){
    var hour = i;
    if (hour > 12){
      hour = hour-12;
    }
    for(var j = 0; j < 6; j = j +1){
      var minute = ':'+j;
      var minute = minute+'0';
      row++;
      sheet.getRange(row, 1).setValue("'"+hour+minute);
      maxRow++
    }
    
    }
    sheet.getRange(maxRow, 1).setValue("'4:00");
    sheet.getRange(maxRow+1, 1).setValue("'4:10");
    sheet.getRange(maxRow+2, 1).setValue("'4:20");
  }
}

function createSheet(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  for (var i = 0; i<data.length; i++){
    var sheetName = 'template'+((i%3)+1);
    var templateSheet = ss.getSheetByName(sheetName);
    ss.insertSheet(data[i][1], i+4, {template: templateSheet});
  }
}

function createForm(){
  var ss = SpreadsheetApp.openById('1igifvI24bzlPIUxlpEMllbXsA9Wuk_lRGmAj21Tgy9o');
  var form = FormApp.openById('1kSPrB47VtREpSq_sspmMOT9znNgmecSx_ceIyRsJ4PU');
  var sheetsArray = ss.getSheets();
  for (var i = 5; i < sheetsArray.length; i++){
    var sheet = sheetsArray[i];
    var name = sheet.getName();
    var data = sheet.getDataRange().getValues();
    var times = [];
    for (var j = 0; j < data.length; j++){
      if (data[j][1].length < 1){
        times.push(data[j][0]);
      }
        
  }
    Logger.log(name);
    Logger.log(times);
  var item = form.addListItem();
    item.setTitle(name);
    item.setChoiceValues(times);
     
  }
  
}

function testSubmit(){
  var object = {values: ['3/7/2017 10:54:29', , 'john.hinkle@aisabuja.com', 'John Hinkle', 'test', , , , , , , , , , , , , , , , , '8:40', , , , , , , , , , , , , , , , , , , , , , ]};
  onSubmittedForm(object);
}

function onSubmittedForm(e){
  //Get Responses from Sheet
  var data = e.values;
  Logger.log(data);
  
  //Get Questions from Sheet
  var ss = SpreadsheetApp.openById('1igifvI24bzlPIUxlpEMllbXsA9Wuk_lRGmAj21Tgy9o');
  var sheet = ss.getSheetByName('Form Responses 1');
  var names = sheet.getRange(1, 1, 1, data.length).getValues()[0];
  
  //Get the respondant answers
  var scheduledTimes = [];
  for (var i = 5; i < names.length; i++){
    var parentEmail = data[2];
    var parentName = data[3];
    var teacher = names[i];
    var time = data[i];
    var studentName = data[4];
    
    //If the response isn't empty
    if (time && time.length > 1){
      Logger.log(time);
      var teacherSheet = ss.getSheetByName(teacher);
      var teacherData = teacherSheet.getDataRange();
      
      //See if the scheduled time is empty and push the schedule time or a notifcation to the array
      scheduledTimes.push(findTime(teacher, time, parentEmail, parentName, studentName));
    }
  }
  
  //sort the array into chronilogical order
  var scheduledTimes = sortArray(scheduledTimes);
  
  
  //create new document for the parent (user)
  var user = data[2];
  var file = DriveApp.getFileById('15hKLqD8zTrq_e94JuLVanjH63CETczuDtLZYe0ZHJ_U');
  var newFile = file.makeCopy('Parent Schedule for '+parentName);
  newFile.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
  var id = newFile.getId();
  var doc = DocumentApp.openById(id);
  var body = doc.getBody();
  
  //merge the data in
  body.replaceText('{name}', parentName);
  var message = '';
  for (var i = 0; i < scheduledTimes.length; i++){
    message = message + scheduledTimes[i][1]+' - '+scheduledTimes[i][0]+'\n\n';
  }
  body.replaceText('{Please wait while schedule is created}', message);
  var footer = doc.getFooter();
  var today = new Date();
  var formattedDate = Utilities.formatDate(new Date(), "UTC+01:00", "yyyy-MM-dd");
   Logger.log(formattedDate);
  footer.replaceText('{date}', formattedDate);
  
  //and send the data to the user
  doc.addViewer(user);
  var url = doc.getUrl();
  var contactMessage = 'If there is an error or you need to reschedule a time, please contact chinwe.obinwanne@aisabuja.com'
  var shareUrl = 'You can view printable copy of your schedule here:\n'+url+'\n\n';
  MailApp.sendEmail(user,
                    'Schedule for Partnership Conference',
                    'Thank you for signing up for the March 30 partnership conference. Your schedule is as follows:\n\n'+message+shareUrl+contactMessage);
  
  //then update the form removing the scheduled times
  updateForm()
  

  
}

  
  
//Searches the spreadsheet for the teacher to double check the time is available returns an array
function findTime(teacher, time, parentEmail, parentName, student){
  var ss = SpreadsheetApp.openById('1igifvI24bzlPIUxlpEMllbXsA9Wuk_lRGmAj21Tgy9o');
  var sheet = ss.getSheetByName(teacher);
  var data = sheet.getDataRange().getValues();
  for (var i = 0; i<data.length; i++){
    if (data[i][0] == time){
      
      //If the time slot is open sends a back the the parent name, teacher email, and the index for sorting
      if (data[i][1].length < 1){
      sheet.getRange(i+1, 2).setValue(parentName);
      sheet.getRange(i+1, 3).setValue(parentEmail);
      sheet.getRange(i+1, 4).setValue(student);
        Logger.log(['findTime',teacher,time])
        var email = findTeacherEmail(teacher);
        addToCalendar(parentName, email, i);
      return [teacher, time, i];
      }
      
      //If the time slot is booked, emails a error message to the teacher and replaces the time with an error message with directions
      else{
        var email = findTeacherEmail(teacher);
        MailApp.sendEmail(email,'Double Booked', 'The '+time+' timeslot '+parentEmail+' requested for '+teacher+' is already booked. Please schedule another time that works.');
        return [teacher, 'Your requested timeslot has been is not available, please email '+email+' for another available slot.',i];
      }
    }
  }
  
  //If teacher is not found, returns an error message
  return [teacher, 'Unknown Teacher',99]
}

//Returns the teachers email or mine if it cannot find one
function findTeacherEmail(teacher){
  var ss = SpreadsheetApp.openById('1igifvI24bzlPIUxlpEMllbXsA9Wuk_lRGmAj21Tgy9o');
  var sheet = ss.getSheetByName('email');
  var data = sheet.getDataRange().getValues();
  for (var i = 0; i<data.length; i++){
    if (data[i][0] == teacher){
      return data[i][1];
      }
  }
  return 'john.hinkle@aisabuja.com';
}
  
//updates the form to remove booked times
  function updateForm(){
    var ss = SpreadsheetApp.openById('1igifvI24bzlPIUxlpEMllbXsA9Wuk_lRGmAj21Tgy9o');
    var form = FormApp.openById('1kSPrB47VtREpSq_sspmMOT9znNgmecSx_ceIyRsJ4PU');
    var items = form.getItems();
    for (var i = 2; i < items.length; i++){

      var choices = []
      var item = items[i];
      Logger.log(item.getTitle());
      var sheet = ss.getSheetByName(item.getTitle());
      if(!sheet){
        Logger.log(item.getTitle());
        continue;
      }
      var data = sheet.getDataRange().getValues();
      for (var j = 0; j<data.length; j++){
        if (data[j][1].length<1){
          choices.push(data[j][0]);
        }
      }
    item.asListItem().setChoiceValues(choices);  
  }
}

//Returns array sorted by chronilogical order
function sortArray(myArray){
  var newArray = [];
  while (myArray.length > 0){
    var lowest = 100;
    var lowestArray = [];
    var index = 0
    for (var i = 0; i < myArray.length; i++){
      if (myArray[i][2] < lowest){
        lowest = myArray[i][2];
        lowestArray = myArray[i];
        var index = i;
      }
    }
    newArray.push(lowestArray);
    myArray.splice(index, 1);
  }
  Logger.log(newArray)
  return newArray;
}

//Adds the 10 minute conference to the teacher's schedule
function addToCalendar(parentName, teacherEmail, timeInt){
  var march30 = 1490857200000;
  var timeToAdd = 600000 * (timeInt-1);
  var startTime = march30+timeToAdd
  try {
    var calendar = CalendarApp.getCalendarsByName(teacherEmail)[0];
    calendar.createEvent(parentName, new Date(startTime), new Date(startTime+600000))
  }
  catch(e){
    Logger.log(e)
  }
}

function changeTime(){
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getRange(2, 3, 28, 1).getValues();
  Logger.log(data);
  for (var i = 0; i < data.length; i++){
    var text = 'March 22 ' + data[i][0]
    sheet.getRange(i+2, 3).setValue(text);
  }
  
}