An Auto Scheduler for Google Docs

clue scheduler.js

//matches the response to a time in the provided spreadsheet then updates the sheet
//if update is successful runs the set choices function
// Returns an empty array if process was successful answer or was 'Do not wish to meet'
// if there was alredy someone signed up for the time, returns the current teacher and time
function updateSheet(ssId, numRows, id, responseId)
{
  var form = FormApp.getActiveForm();
  var items = form.getItems();

  var formResponses = form.getResponses();
  var recentResponses = formResponses[formResponses.length - 1];
  var responses = recentResponses.getItemResponses();
  var ssheet = SpreadsheetApp.openById(ssId);
  var sheets = ssheet.getSheets();
  var sheetName = sheets[id-1].getName();
  var sheet = ssheet.getSheetByName(sheetName);

  // var responses = form.getResponse(responseId);
  Logger.log(responses);
  var question = items[id];
  Logger.log(question.getTitle());
  try
  {
    var response = recentResponses.getResponseForItem(question);
    Logger.log(response);
  }
  catch(e)
  {
    Logger.log('error')
    return ['your chosen time','your chosen time', sheetName];
  }
  if (!response){return ['your chosen time','your chosen time', sheetName];}
    
  
  if (response.getResponse() == 'Do Not Wish to Meet')
    {return [];}
  
  //updates the spreadsheet and regets the values from spreadsheet to check if someone else had been scheduled.
  SpreadsheetApp.flush();
  var times = sheet.getRange(1, 1, numRows, 2).getValues();
  var name = responses[0].getResponse();
  for (var i = 0; i < times.length; i++)
  {
      if (times[i][0] === response.getResponse())
        {
          if (times[i][1] == 'null')
          {
            var cell = sheet.getRange(i+1, 2);
            cell.setValue(name);
            SpreadsheetApp.flush();
            times = sheet.getRange(1, 1, numRows, 2).getValues();
            setChoices(sheet, times, items, id);
            return [];
          }
          else
          {return [times[i][0], times[i][1], sheetName];}
      }
  }
  return [];
}

//finds the next teacher according to the list of times in spreadsheet sheet 1.
//returns [teacher, time] or ['null', 'null'] if no teacher was found for the time
function findNext(time, teacherTimeArray)
{
  for (var i = 0; i < teacherTimeArray.length; i++)
  {
    if (time == teacherTimeArray[i][1])
    {return [teacherTimeArray[i][0],teacherTimeArray[i][1]];}
  }
  return ['null', 'null'];
}

//sets the choices for the form
function setChoices(sheet, times, formItems, id){
  Logger.log('set choices')
  var question = formItems[id].asMultipleChoiceItem();
  question.setRequired(true);
  var choices = [];
  Logger.log(times);
  for (var i = 0; i < times.length; i++)
  {
    Logger.log(times[i][1])
    if (times[i][1] == 'null')
      {choices.push(times[i][0]);}
    Logger.log('Choices is: '+choices);
  }
  if (choices.length)
    {question.setChoiceValues(choices);
    Logger.log('choices set');
    }
  
}

function createDoc(userName, masterTimes, teacherTimeArray, doubleBookErrors, ssheet, numRows, myName, myEmail)
{
//creates a new document based on the user's name and set the permissions  
var doc = DocumentApp.create('Schedule for '+userName);
  var file = DriveApp.getFileById(doc.getId());
  file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
  var url = doc.getUrl();
  
//writes to the document
  var body = doc.getBody();
  body.appendParagraph('Dear '+userName+',');
  body.appendParagraph('');
  body.appendParagraph('Thank you for taking the time to schedule with us. Your schedule is printed as follows: ');
  body.appendParagraph('');
  
//creates a table with the schedule in the document
  var schedule = body.appendTable();
  for (var i = 0; i < masterTimes.length; i++)
  {
    var next = findNext(masterTimes[i], teacherTimeArray);
    if (next[0] !== 'null'){
      var tr = schedule.appendTableRow();
      tr.appendTableCell(next[0]);
      tr.appendTableCell(next[1]);
    } 
}
//writes a custom message if a time was double booked giving a list of other availiable times for the teacher    
  for (var i = 0; i < doubleBookErrors.length; i++)
  {
    body.appendParagraph(' ');
    body.appendParagraph('There was an error with your submission. Someone has already booked '
                         +doubleBookErrors[i][0]+' with '+doubleBookErrors[i][1]+
                         '. Please contact '+myEmail+' for another time. Other available times are:');
    body.appendParagraph(' ');
    Logger.log('dbe is :'+ doubleBookErrors[i][1])
    var currentValues = ssheet.getSheetByName(doubleBookErrors[i][1]).getRange(1, 1, numRows, 2).getValues();
    Logger.log(currentValues);
    for (var j = 0; j < currentValues.length; j++)
    {
      if (currentValues[j][1] === 'null' && currentValues[j][0] !== 'Do Not Wish to Meet')
        {body.appendParagraph(currentValues[j][0]);}
    }
  }
  
//writes to the document
  body.appendParagraph(' ');
  body.appendParagraph('Sincerely,');
  body.appendParagraph(myName);
  return url;
  }

function reset(formItems, ssId, firstSheetNum, lastSheetNum, numRows, form)
{
  var ssheet = SpreadsheetApp.openById(ssId);
  var sheets = ssheet.getSheets();
  for (var i = firstSheetNum; i < lastSheetNum+1; i++)
  { 
    Logger.log(sheets[i-1].getName());
    var sheetName = sheets[i-1].getName();
    var sheet = ssheet.getSheetByName(sheetName);
    for (var j = 1; j < numRows + 1; j++)
    {
      var cell = sheet.getRange(j, 2);
      cell.setValue('null');
    }
    var times = sheet.getRange(1, 1, numRows, 2).getValues();
    setChoices(sheet, times, formItems, i);
  }
  form.deleteAllResponses();
}

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//                                             The Main Function Begins. Set custom variables here.                                           //
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

//the main function. Set the trigger to on form submit.
function onFormSubmit(e){ 
  var numRows = 13;                                             //rows in the spreadsheet
  var ssId = ??;    //spreadsheet id
  var firstSheetNum = 3;                                        //make sure the sheet number matches the question number.
  var lastSheetNum = 8;                                         //ditto
  var myName = 'Mr. Body';
  var myEmail = ???;
  //var notScripting = true
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
    
//sets my variables    
  var form = FormApp.getActiveForm();
  var items = form.getItems(); 
  var ssheet = SpreadsheetApp.openById(ssId);
  var sheets = ssheet.getSheets();
  var masterSheet = ssheet.getSheetByName(sheets[1].getName())
  var masterTimes = masterSheet.getRange(1, 1, numRows, 1).getValues();
    
//gets most recent form response
  var formResponses = form.getResponses();
  var recentResponses = formResponses[formResponses.length - 1];
  var responseId = recentResponses.getId();
  var responses = recentResponses.getItemResponses();
  var userName = responses[0].getResponse();
  var email = responses[1].getResponse();
  var forLogger = responses;
  for (var i = 0; i < forLogger.length; i++){Logger.log(forLogger[i].getResponse());}
  
//resets the spreadsheet app if name is Mr. Resetti and email is reset.
  if (userName === 'Mr. Resetti' && email === 'reset')
    {
      reset(items, ssId, firstSheetNum, lastSheetNum, numRows, form);
      MailApp.sendEmail(myEmail, 'Form Reset', 'Your form as been reset');
      return;
    }

//double checks that this the most current response has not been acted upon
  //doesn't work yet :(
/*  
  var responsesCollected = masterSheet.getRange(1, 2, formResponses.length, 1);
  var j = 1
  while(notScripting)
  {
    var isCurrentResponce = true
    for (var i = 0; i < masterSheet.length; i++)
    {
      if (responseId == responsesCollected[i])
      {
        j++
        responseId = formResponses[formResponses.length - j];
        isCurrentResponce = false;
        var responses = form.getResponse(responseId);
        var userName = responses[0].getResponse();
        var email = responses[1].getResponse();
        break;
      }
    }
    if (isCurrentResponce)
    {
      var cell = masterSheet.getRange(formResponses.length, 2);
      cell.setValue(responseId);
      break
    }
  }
    
  */    
    
      
      
//check for duplicate responses. Emails the sender if duplicate responses are found and doesn not schedule them.   
  for (var i = 0; i < responses.length; i++)
  {
    var r1 = responses[i].getResponse();
    if (r1 != 'Do Not Wish to Meet')
    {
      for (var j = i+1; j < responses.length; j++)
      {
        if (r1 === responses[j].getResponse())
        {
          var formUrl = form.getPublishedUrl();
          var message = 'Dear '+userName+',\n\nThanks for scheduling with us; however, there has been an error with your schedule. '+
                        'You had requested to meet with two or more teachers at the same time. As a result your form needs to be resubmitted. '+
                        'No times have been reserved for you. Please visit the following link to reschedule:\n\n'+formUrl+
                        '\n\nWe are sorry for the inconvenience.\n\n'+
                        'Sincerely,\n'+myName;
          MailApp.sendEmail(email,
                            'Schedule Error',
                            message,
                            {name: myName, bbc: myEmail});
          return;
        }
      }
    }
  }
    
    
//updates the sheets and looks for double booking errors then saves them in doubleBookErrors
  var doubleBookErrors = [];
  for (var i = firstSheetNum; i < lastSheetNum+1; i++)
  { 
    var ec = updateSheet(ssId, numRows, i, responseId);
    if(ec.length)
      {doubleBookErrors.push([ec[0],ec[2]]);}
  }
    
//creates a double arrary with the teacher name and the selected time ommitting 'Do Not Wish to Meet'  
  var teacherTimeArray = [];
  for (var i = firstSheetNum; i < lastSheetNum + 1; i++)
  {
    //sorts out missing answers
    var question = items[i];
    try
      {var currentResponse = recentResponses.getResponseForItem(question).getResponse();}
    catch(e){Logger.log(e);
             continue;}
    
    if (currentResponse && currentResponse !== 'Do Not Wish to Meet')
      {
         var noDoubleBooks = true
         for (var j = 0; j < doubleBookErrors.length; j++)
         {
             if (doubleBookErrors[j][0] == currentResponse)
             {
               noDoubleBooks = false;
               break;
             }
         }
         if (noDoubleBooks)
           {teacherTimeArray.push([items[i].getTitle(), currentResponse]);}
      }
    }
    
//creates a document and returns the url
 var url = createDoc(userName, masterTimes, teacherTimeArray, doubleBookErrors, ssheet, numRows, myName, myEmail);
    
//sends an email to the form filler
   
  MailApp.sendEmail(email,
                    'Schedule',
                    'Dear '+userName+',\n\nThanks for scheduling with us. Your schedule can be found here:\n' +url+'\n\nSincerely,\n'+myName,
                    {name: myName, bbc: myEmail});
                    
                    
  Logger.log('Success!')
  }