Test.js

function myfunction(e) {
  ScriptProperties.setProperty('StaffRosterID', "0Ag1z3a5NEg8HdDl1WDlGYVBLZjRKTDA5aFlPQkM5Q3c");
  ScriptProperties.setProperty('Request Folder', "0Bw1z3a5NEg8HYm43SEhXMUZFclk");
  ScriptProperties.setProperty('Academic Year', "1415");
  ScriptProperties.setProperty('FYEAD', "kelly.beers@maine.edu");
  
  var timestamp = e.values[1];
  var name = e.values[3];
  var complex = e.values[6]; 
  var title = e.values[7];
  var scale = e.values[12];
  var LLC = e.values[13];
  var partners = e.values[5];
  var money = e.values[21];
  var date = e.values[8];
  var starttime = e.values[9];
  var endtime=e.values[10];
  var location = e.values[11];
  var NewID = getID();
  var username = e.values[2];
  var userdescription = e.values[24];
  Logger.log(username);
  var url=spreadsheetToPDF(complex,title,date,NewID,name,timestamp);
  sendemail(name,complex,title,scale,partners,money,NewID,date,url,LLC,username);
  addcalevent(name,complex,title,date,starttime,endtime,location,userdescription);
  
}

function spreadsheetToPDF(complex,title,date,NewID,name,timestamp) {
  var IDsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Enter ID");
  IDsheet.getRange(1,2).setValue(NewID);
  
  try{
    SpreadsheetApp.flush();
    var Responsespreadsheet=SpreadsheetApp.getActive();
    var dataSheet = Responsespreadsheet.getSheetByName("Print Form");
    var dataSheetIndex = dataSheet.getSheetId();
    var token = ScriptApp.getOAuthToken();
    var url = Responsespreadsheet.getUrl();
    url = url.replace(/edit$/,'');
    var url_ext = 'export?exportFormat=pdf&format=pdf' + //export as pdf
      //below parameters are optional...
      '&size=letter' + //paper size
        '&portrait=true' + //orientation, false for landscape
          '&fitw=true' + //fit to width, false for actual size
            '&sheetnames=false&printtitle=false&pagenumbers=false' + //hide optional headers and footers
              '&gridlines=false' + //hide gridlines
                '&fzr=false' + //do not repeat row headers (frozen rows) on each page
                  '&gid=' + dataSheetIndex; //the sheet's Id
      
    var response = UrlFetchApp.fetch(url + url_ext, {
                                     headers: {
                                     'Authorization': 'Bearer ' +  token
                                     }
                                     });
    
     var date = new Date(timestamp);
     var moddate = date.getFullYear()+' '+("0" + (date.getMonth() + 1)).slice(-2)+' '+("0" + date.getDate()).slice(-2);
  
     var blob = response.getBlob().setName(ScriptProperties.getProperty('Academic Year')+" Request: "+complex+" - "+title+" ID:"+NewID+" "+moddate);

  var requestfolder = DriveApp.getFolderById(ScriptProperties.getProperty('Request Folder'));
  var pdfurl = requestfolder.createFile(blob).getUrl();
  //  setDescription("Submitted by: "+name+" on "+timestamp);
  return pdfurl;
     
     
  }
 catch(e){
    senderrormessage(e);
  		logger.log(e)
  }
  
  
  
}

function addcalevent(name,complex,title,date,starttime,endtime,location1,userdescription){
 Logger.log(complex);
  switch (complex) 
  {
    case 'DTAV/Patch':
      var calid = 'maine.edu_36j1cc8uo16j61ipicrjcpe3sg@group.calendar.google.com';
      var complexsort = "DP";
      break;
    case 'YAK':
      var calid = 'maine.edu_tda091m0k0rjqvcefsludsqnto@group.calendar.google.com';
      var complexsort = "YAK";
      break;
    case 'GC':
      var calid = 'maine.edu_6e60ekjcga7jt87eprhito1bng@group.calendar.google.com';
      var complexsort = "GC";
      break;
    case 'BCP':
      var calid = 'maine.edu_pu23o8gk0dfnc20sdovs8oam2k@group.calendar.google.com';
      var complexsort = "BCP";
      break;
    case 'SOX':
      var calid = 'maine.edu_9o55v20u2v0g9jt3jgv1br1qh0@group.calendar.google.com';
      var complexsort = "SOX";
      break;
    case 'KA':
      var calid = 'maine.edu_t710b48uckk17hguil1iruo6uc@group.calendar.google.com';
      var complexsort = "KA";
      break;
    case 'H2O':
      var calid = 'maine.edu_428ltgr9ks4s5ijq39uu5idas4@group.calendar.google.com';
      var complexsort = "H20";
      break;
    case 'FYE':
      var calid = 'maine.edu_8g32l3f32a3a12psgenjr1oedo@group.calendar.google.com';
      var complexsort = "FYE";
      break;
    case 'SYE & TSP':
      var calid = 'maine.edu_la6u2jguippl4qkohbbekgasr4@group.calendar.google.com';
      var complexsort = "SYE";
      break;
    default: 
      var calid = 'um.reslife@maine.edu';
      var complexsort = "ResLife";
  }
  
  Logger.log(calid);
  var calendar = CalendarApp.getCalendarById(calid);
  Logger.log(calendar);
  var EventStartTime = new Date(date+' '+starttime);
  var EventEndTime = new Date(date+' '+endtime);
  
  var description1 = userdescription+' This is a program for the '+complex+' Complex. The program is a being planned by '+name+'. '; 
  
  calendar.createEvent(title, EventStartTime, EventEndTime, {location: location1, description: description1});
  var cal2 = CalendarApp.getCalendarById('um.reslife@maine.edu');
  cal2.createEvent(complexsort+": "+title, EventStartTime, EventEndTime, {location: location1, description: description1});
  
}

function sendemail(name,complex,title,scale,partners,money,NewID,date,url,LLC, username){
 
  var prostaffemail = prostaff(complex);
  var gradstaffemail = gradstaff(complex);
  var cc = prostaffemail+','+gradstaffemail;
  
  var body = name+', Thank you for submitting Program Development Form for '+title+'. The program ID number is: '+NewID;
  
  var bodyHTML1 = '<p>'+name+',</p>';
  bodyHTML1+='<p>Thank you for submitting a program development form for:</p>';
  bodyHTML1+='<p><b>Title: </b>'+title+'<br/>';
  bodyHTML1+='<b>Date: </b>'+date+'<br/>';
  if(partners!=""){
    bodyHTML1+='<b>Program Partners: </b>'+partners+'<br/>';
  }
  if(money!=""){
    bodyHTML1+='<b>Cost: </b>'+money+'<br/>';
  }
  else{
    bodyHTML1+='<b>Cost: </b>$0.00<br/>';
  }
  bodyHTML1+='<b>Program ID: </b>'+NewID+'<br/>';
  bodyHTML1+='<b>Scale: </b>'+scale+'<br/>';
  if(LLC!=""){
    bodyHTML1+='<b>LLC: </b>'+LLC+'</p>';
  }
  bodyHTML1+='<p><b>Please save the Program ID Number. It will be required to submit the evaluation form.</b></p>';
  
  
  bodyHTML1+='<p><i>The Program Request can be viewed <a href="'+url+'">Here</a> and approved <a href="https://docs.google.com/a/maine.edu/spreadsheet/ccc?key=0Ag1z3a5NEg8HdHJ1YVgxbWhETDRHWktkRFoxdHduYlE#gid=5">Here</a> (CC Only).';
  
  if(scale=="FYE" || scale=="SYE" || scale=="TSP" || scale=="LLC" ){
    cc=cc+","+ScriptProperties.getProperty('FYEAD');
  }
  
  var advancedArgs = {htmlBody:bodyHTML1, cc: cc};
  if(LLC!="")
  {
    MailApp.sendEmail(username, "Program Development Form - "+LLC+": "+title, body, advancedArgs);
  }
  else
  {
    MailApp.sendEmail(username, "Program Development Form - "+title, body, advancedArgs);
  }
}

function getID(){
  var IDsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Next ID");
  var responsesheet = SpreadsheetApp.getActiveSheet();
  var row =  SpreadsheetApp.getActiveSheet().getLastRow();
  var NewID = IDsheet.getRange(1,1).getValue();
  var NextID = NewID + 1;  
  responsesheet.getRange(row,1).setValue(NewID);
  IDsheet.getRange(1,1).setValue(NextID);
  return NewID;
}

function prostaff(complex){
  //opens the staff roster
  var rosterspreadsheet=SpreadsheetApp.openById(ScriptProperties.getProperty('StaffRosterID'));
  //opens the prostaff sheet
  var prostaffsheet = rosterspreadsheet.getSheetByName('Pro Staff');
  var lastprorow = prostaffsheet.getLastRow();
  var rosterdata = prostaffsheet.getRange(2, 4, lastprorow, 2).getValues();
  var prostaff = ""; 
  //searches for the prostaff of the complex
  for(var s=0; s<rosterdata.length;s++)
  {
    if(complex==rosterdata[s][0])
    {
      //once it finds the match it saves the email in the variable and breaks the loop
      prostaff = rosterdata[s][1];
      break;  
    } 
  }
  return prostaff;
}

function gradstaff(complex){
  var gaemails = "";
  var blah2 = true;
  var rosterspreadsheet=SpreadsheetApp.openById(ScriptProperties.getProperty('StaffRosterID'));
  var gradstaffsheet = rosterspreadsheet.getSheetByName('Grad Staff');
  var lastgradrow = gradstaffsheet.getLastRow();
  var gradrosterdata = gradstaffsheet.getRange(2, 4, lastgradrow, 2).getValues();
  for(var c=0; c<gradrosterdata.length;c++)
  {
    if(complex==gradrosterdata[c][0])
    {
      if(blah2)
      {
        gaemails = gradrosterdata[c][1];
        blah2 = false;
      }
      else
      {
        gaemails = gaemails+","+gradrosterdata[c][1];
      }
    } 
  }
  return gaemails;
}