Conference Scheduler

conferenceScheduler.gs

function onSubmittedForm(e) {
  var data = e.values;  //gets the information in an arary, the last item is blank
  Logger.log(data);
  
  //open spreadsheet and get finance sheet
  var thisSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var financeSheet = thisSpreadsheet.getSheetByName('Finances');
  
  //put all the info into one array
  var data2 = [];
  for (var i = 0; i < data.length; i++){
    data2.push(data[i]);
  }
  
  //and added new answers
  for (var i = 0; i < 3; i++){
  data2.push('No');
  }
  
  //added the information to the finance sheet
  financeSheet.appendRow(data2);
  
  teacherList(data);
}


//Takes a class name and returns an array of class information
//[0] Class Name, [1] Teacher, [2] Teacher Email, [3] Location
//var class = getClassInfo('className');
//var teacher = class[1];

function getClassInfo(className){
  var ss = SpreadsheetApp.openById('1cRSmUZIZ92_h9OZqg6SvanT8rZLqA_18oh536pDqI6M');
  var sheet = ss.getSheetByName('SessionInformation');
  var data = sheet.getDataRange().getValues();
  for (var i = 0; i<data.length; i++){
    if (data[i][0] == className){
      return data[i];
    }
  }
  return [className, 'unknown class', 'unknown class', 'unknown class']
}

//Takes a class name and returns an object with class information
//Contains following properties: class.name, class.teacher, class.email, class.location
//var class = createClassObject('className');
//var teacher = class.teacher;

function createClassObject(className){
  var ss = SpreadsheetApp.openById('1sZcS2efv6gYT61Ycp7SiHqnm2Gq6RKyEc2QVKEVIZ9U');
  var sheet = ss.getSheetByName('SessionInformation');
  var data = sheet.getDataRange().getValues();
  for (var i = 0; i<data.length; i++){
    if (data[i][0] == className){
      var class = {name: data[i][0], teacher: data[i][1], email: data[i][2], location: data[i][3]}
      return class;
    }
  }
  var class = {name: className, teacher: 'N/A', email: 'N/A', location: 'N/A'}
  return class;
}

//this example shows you how to call getClassInfo
  function testClass(){
    Logger.log(getClassInfo('cat'));
  }

function test(){
  var myArray = {values:  ['3/10/2017 11:39:45', 'hinkle.macau@gmail.com', 'Hinkle Test', 'Aisabuja', 'An overview of Google Docs', 'Technology on a budget - Low cost options to teach technology','']}
  var data = myArray.values;
  onSubmittedForm(myArray);
  
}

function sendEmailUpdates(){
  var sheet = SpreadsheetApp.getActive().getSheetByName('3Finances');
  var table = sheet.getDataRange().getValues();
  for (var i = 1; i < table.length; i++){
    teacherList(table[i]);
    
  }
  
}

function teacherList(data) {
  
  var time = data[0];
  var emailAddress = data[1];
  var studentName = data[2];
  var organization = data[3];
  var className1 = data[4]
  var className2 = data[5];
  var paid = data[7];
  
  if (paid !== 'Yes'){
      Logger.log('Not Paid');
      return;
      
  } else {
    Logger.log([emailAddress,studentName,organization,className1,className2]);
  }

  
  var class1 = createClassObject(className1);
  var class2 = createClassObject(className2);
  
  //var teacherSS = SpreadsheetApp.openById('1OzdbIv_K4_Kkk30HVJlK-kSAq8YwTzwh4PtOQix1vxs');
  
  //var sheetData = [studentName,organization,emailAddress];
  /*
  try{
  var sheet = teacherSS.getSheetByName(class1.name);
    sheet.appendRow(sheetData);
  }
  catch(e){
    var sheet = teacherSS.insertSheet(class1.name);
    sheet.appendRow(sheetData);
  }
  
  try{
  var sheet2 = teacherSS.getSheetByName(class2.name);
    sheet2.appendRow(sheetData);
  }
  catch(e){
    var sheet2 = teacherSS.insertSheet(class2.name);
    sheet2.appendRow(sheetData);
  }
  */

  
//Make schedule
  
  var file = DriveApp.getFileById('1OKlKjKqC4MVK-kj2i0oy2A8X9uk5E35rktXspw5Aofc');
  var folder = DriveApp.getFolderById('0B3QaE26f2r25S0R4X1JOS2hUTUU');
  var newFile = file.makeCopy(studentName+' Updated FCT Conference Schedule', folder);
  newFile.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
  var id = newFile.getId();
  var doc = DocumentApp.openById(id);
  var url = doc.getUrl();
  
  var body = doc.getBody();
  body.replaceText('{name}', studentName);
  body.replaceText('session1.name', class1.name);
  body.replaceText('session2.name', class2.name);
  body.replaceText('session1.teacher', class1.teacher);
  body.replaceText('session2.teacher', class2.teacher);
  body.replaceText('session1.location', class1.location);
  body.replaceText('session2.location', class2.location);
  var footer = doc.getFooter();
  
  var date = new Date();
  var today = Utilities.formatDate(date, 'UTC+1:00', "MM-dd-yyyy");
  footer.replaceText('{date}', today);
  var body = "We have confirmed your registration for the FCT conference tomorrow.\nThere have been some changes to the room assignments. Please view your updated schedule.\n\nWe look forward to having you!\n\n"+url;
  MailApp.sendEmail(emailAddress, 'Updated Schedule', body);
  
}

//append to classes
function createClassList(){
var sheet = SpreadsheetApp.getActive().getSheetByName('Finances');
var data = sheet.getDataRange().getValues();
Logger.log(data);
var newSpreadsheet = SpreadsheetApp.openById('1OzdbIv_K4_Kkk30HVJlK-kSAq8YwTzwh4PtOQix1vxs');


  for (var i = 0; i < data.length; i++){
    var name = data[i][2];
    var email = data[i][1];
    var org = data[i][3];
    var paid = data[i][7];
    var session1 = data[i][4];
    var session2 = data[i][5];
    
    
    if (paid == 'Yes'){
      Logger.log(name);
      try{
        var sheet = newSpreadsheet.getSheetByName(session1);
        sheet.appendRow([name, email, org]);
      }
      catch(e){
        var sheet = newSpreadsheet.insertSheet(session1);
       sheet.appendRow([name, email, org]);
        
        
      }
      try{
        var sheet = newSpreadsheet.getSheetByName(session2);
        sheet.appendRow([name, email, org]);
      }
      catch(e){
        var sheet = newSpreadsheet.insertSheet(session2);
       sheet.appendRow([name, email, org]);
        
        
      }
    }
    
  }
}
  

function sendEmail(){
  var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet6')
  var url = 'https://drive.google.com/file/d/0B3QaE26f2r25ZjFsR1hrTmhBTUNlSU1kdnhDalpVbnhEcVhZ/view?usp=sharing';
  var message = 'We have prepared this pamphlet for you with more information about our program tomorrow.\n\
We are looking forward to having you!\n\n'+url;
  
  var data = sheet.getDataRange().getValues();
  MailApp.sendEmail('john.hinkle@aisabuja.com', 'Pamphlet', message)
  for (var i = 0; i< data.length; i++){
    var email = data[i][0];
    
    MailApp.sendEmail(email, 'Pamphlet', message);
  }
  
  }