stephenjbell

Google Form - Script code to send email

Nov 20th, 2012
137
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. // Settings specific to project
  2. var formTitle = "Nursing Application";
  3. var formLink = "http://www.oc.edu/academics/nursing/"; // Top logo link back to event or informational page
  4.  
  5. // Email Addresses - Who will be notified, and who will get the email when the visitor hits the "reply" button?
  6. var staffemail="youremailaddress";
  7. var staffemailcc="";
  8. var staffemailbcc="";
  9. var staffReply="youremailaddress";
  10.  
  11. var visitorEmailColumn = 22; // Column "V" is column #22
  12.  
  13.  
  14. var ss = SpreadsheetApp.getActiveSpreadsheet();
  15. var form = ss.getSheetByName('Sheet1'); // This is the default when you make a spreadsheet - change if necessary
  16.  
  17. function distributeForm(){
  18.   var lastrow=form.getLastRow();
  19.   sendInfo(lastrow);
  20. }
  21.  
  22. function sendInfo(trow){
  23.   var visitorReply=form.getRange(trow,visitorEmailColumn).getValue();
  24.   var visitorFirstName = form.getRange(trow,2).getValue();
  25.   var visitorLastName = form.getRange(trow,4).getValue();
  26.  
  27.   var sbj=visitorFirstName+' '+visitorLastName+' - '+formTitle;
  28.   var thismessage='<HTML>'
  29.       +'<head></head>'
  30.       +'<body style="background-color:#ECF1F7;padding:0;margin:0;font-family:Helvetica, Arial, Verdana, sans-serif;font-size:12px;color:#fff;text-align:center;"><a href="'+formLink+'"><img src="http://www.oc.edu/lucid/ochome/images/oclogo.png"></a><br><table align="center" width="550px;" style="border:1px solid #D2D8DF;background:#ffffff;color:#000000;">'
  31.       +'<tr><td colspan="2" style="border-bottom:1px solid #D2D8DF;"><b>'+ formTitle +'</td></tr>';
  32.   var topArray=form.getRange(1,1,1,form.getLastColumn()).getValues();
  33.   var trowArray=form.getRange(trow,1,1,form.getLastColumn()).getValues();
  34.   for(var ff=1;ff<=form.getLastColumn();ff++){
  35.     thismessage+='<tr><td style="border-bottom:1px solid #D2D8DF;">'+topArray[0][ff-1]+'</td><td style="border-bottom:1px solid #D2D8DF;">'+trowArray[0][ff-1]+'</td></tr>';
  36.   }
  37.   thismessage+='</table>'
  38.       +'</body>'
  39.       +'</HTML>';
  40.   MailApp.sendEmail(staffemail,sbj,'', {name:formTitle,htmlBody:'Notification to '+formTitle+':<br>'+thismessage,bcc:staffemailbcc,replyTo:visitorReply});
  41.   MailApp.sendEmail(visitorReply,'Confirmation - '+formTitle,'', {name:formTitle,htmlBody:'Confirmation to Visitor<br>'+thismessage,bcc:staffemailbcc,replyTo:staffReply});
  42.   form.getRange(trow,1).setComment('emailed');
  43. }
  44.  
  45. function arrayNotation(){
  46.   var thisArray=form.getRange(1,1,1,form.getLastColumn()).getValues();
  47.   Browser.msgBox(thisArray[0][2]);
  48. }
Add Comment
Please, Sign In to add comment