Event Scheduler

event.gs

function myFunction() {
  var ss = SpreadsheetApp.openById('1S-idIC3gabylSZhFZTbOKkGT5VBB8l0VAoKyt2ZIVaE');
  var form = FormApp.openById('1DsVEYADcbprx44MGfaaUrqIBFvx40rS-tQitPOM9jZY');
  
  //define variables
  var formResponses = form.getResponses();
  var lastest = formResponses[formResponses.length-1];
  var responses = lastest.getItemResponses();
  var user = lastest.getRespondentEmail();
  var timestamp = lastest.getTimestamp();
   var eventName = responses[0].getResponse()
  var eventDate = responses[1].getResponse()
  var startTime = responses[2].getResponse()
  var endTime = responses[3].getResponse()
  var department = responses[4].getResponse()
  var people = responses[5].getResponse()
  var location = responses[6].getResponse()
  var itemsNeeded = responses[7].getResponse()
  
  //check if there is food
  var food = 'none';
  try{
    var food = responses[8].getResponse();
    }
  catch(err){
    Logger.log(err);
  }
  
  Logger.log([eventName, eventDate, startTime, endTime, department, people, location, itemsNeeded, food]);
  
  //put items needed in a string for emailing
  var itemsList = '';
  for (var i = 0; i < itemsNeeded.length; i++){
    itemsList = itemsList+'\n'+itemsNeeded[i]
  }
  
  //open ss and get information
  var ss = SpreadsheetApp.openById('1S-idIC3gabylSZhFZTbOKkGT5VBB8l0VAoKyt2ZIVaE');
  var form = FormApp.openById('1DsVEYADcbprx44MGfaaUrqIBFvx40rS-tQitPOM9jZY');
  var emailArray = [];
  var items = ss.getSheetByName('Items').getDataRange().getValues();
  var emails = ss.getSheetByName('emails').getDataRange().getValues();
  var toEmail = user;
  Logger.log(items);
  Logger.log(emails);
  
  //for each respnose in items needed, see if it on the spreadsheet
  for (var i = 0; i < items.length; i++){
    
    //if it is, get info who is responsible for it
    if (itemsNeeded.indexOf(items[i][0])>-1){
      var role = items[i][1];
      var email = getEmail(role, emails);
      
      //If the email has not been added to the mailing list, add the email to the mailing list
      if (toEmail.indexOf(email)==-1){
        Logger.log('Email added to list');
        toEmail = toEmail+','+email
        emailArray.push(email);
      }
    }
  }
  //notify admin about thier department
  for (var i = 0; i < emails.length; i++){
    if (emails[i][0] == department){
      var email = emails[i][1];
      toEmail = toEmail+','+email;
      emailArray.push(email);
      
      //add secretary if there is food
      if (food != 'none'){
        var email = emails[i][2];
        toEmail = toEmail+','+email;
        emailArray.push(email);
        itemsList = itemsList+'\n\nFood:\n'+food;
        
      }
    }
    
  }
  
  //set up email body and message
  Logger.log(toEmail);
  var subject = 'New event on '+eventDate;
  var body = eventName+' is scheduled for '+eventDate+' from '+startTime+' to '+endTime+' in the '+location+' by '+user+'.\nThe following items will be needed for '+people+' people:\n'+itemsList+'\n\nThanks,\nJohn Hinkle';
  Logger.log(toEmail);
  Logger.log(subject);
  Logger.log(body);
  
  //set up calendar event for the event
  var year = eventDate.slice(0,4);
  var month = eventDate.slice(5,7);
  var day = eventDate.slice(8,10);
  Logger.log('Year: '+year);
  Logger.log('Month: '+month);
  Logger.log('Day: '+day);
  var newDay = month+'/'+day+'/'+year
  var startString = newDay+' '+startTime;
  Logger.log(startString);
  var start = new Date(startString);
  Logger.log(start);
  var endString = newDay+' '+endTime;
  var end = new Date(endString);
  Logger.log(end);
  var calendar = CalendarApp.getCalendarById('aisabuja.com_hnpac9jokq2u7gekv8di9fu04c@group.calendar.google.com');
  var event = calendar.createEvent(eventName, start, end);
  event.setLocation(location);
  event.setDescription('Set by: '+user+'\n\nItems Needed:\n'+itemsList);
  
  //send emails about event and send and invitation to the event
  for (var i = 0 ; i < emailArray.length; i++){
    event.addGuest(emailArray[i]);
    }
   MailApp.sendEmail(toEmail, subject, body);
  
  
}

function testSubmit(){
  var e = {values: ['3/16/2017 17:25:46', 'Test',' 3/18/2017', '9:00:00 AM', '10:00:00 AM', 1, 'MPH', 'Projector', 'Sound', 'Chairs', ,'john.hinkle@aisabuja.com']}
  onSubmittedForm(e);
  
}

function getEmail(role, emails){
  Logger.log('Getting email for '+role);
  for (var i = 0; emails.length; i++){
    if (role == emails[i][0]){
      Logger.log('Email Found: '+emails[i][1]);
      return emails[i][1];
    }
  }
  Logger.log('Email not Found');
  return 'john.hinkle@aisabuja.com';
}