onFormSubmit trigger not working for sending out email upon form submission

Question

I have a piece of code here which, on form submission, is supposed to:

Perform some calculations in a spreadsheet using the form responses, create a pdf file containing the user's results, and email this to them.

I have set up a trigger which runs "onFormSubmit", with events "From Spreadsheet", "onFormSubmit" and Here is my code:

//Set out global variables
var docTemplate = "1Ff3SfcXQyGeCe8-Y24l4EUMU7P9TsgREsAYO9W6RE2o";
var docName="Calculations";

//createOnFormSubmitTrigger();
function onFormSubmit(e){

  //Variables

  var ssID = '1dMmihZoJqfLoZs9e7YMoeUb_IobW4k6BbOuMDOTTLGk';
  var ss = SpreadsheetApp.openById(ssID);
  ss.setActiveSheet(ss.getSheetByName("Sheet3"));
  var totalOutstandingPrincipalDebt = SpreadsheetApp.getActiveSheet().getRange("G25").getValue();
  var totalOutstandingInterest = SpreadsheetApp.getActiveSheet().getRange("H25").getValue();
  var totalOutstandingCompensation = SpreadsheetApp.getActiveSheet().getRange("I25").getValue();
  var dailyInterestRate = SpreadsheetApp.getActiveSheet().getRange("J25").getValue();
  var grandTotal = SpreadsheetApp.getActiveSheet().getRange("K25").getValue();
  var userEmail = SpreadsheetApp.getActiveSheet().getRange("H24").getValue();

  //Template Info

  var copyId=DriveApp.getFileById(docTemplate).makeCopy(docName+' for '+userEmail).getId();
  var copyDoc = DocumentApp.openById(copyId);
  var copyBody = copyDoc.getActiveSection();

  //Putting the data into the file
  copyBody.insertParagraph(1,'Total Outstanding Principal Debt: £' + totalOutstandingPrincipalDebt);
  copyBody.insertParagraph(2,'Total Outstanding Interest: £'+ totalOutstandingInterest );
  copyBody.insertParagraph(3,'Total Outstanding Compensation: £'+ totalOutstandingCompensation);
  copyBody.insertParagraph(4,'Grand Total: £' + grandTotal);
  copyBody.insertParagraph(5,'Daily Interest Rate: £'+ dailyInterestRate);
  copyDoc.saveAndClose();

 //email pdf document as attachment 
  var pdf = DriveApp.getFileById(copyId).getAs("application/pdf");
  var subject = "Calculations";
  var body = "Thank you very much for using our online calculator. Please find your results attached.";

  MailApp.sendEmail(userEmail, subject, body, {htmlBody: body, attachments: pdf});

  //Deletes temporary Document
  DriveApp.getFileById(copyId).setTrashed(true);
}

The script will sometimes run fine when I am in the script editor (not always?!), but when I submit a form, I receive the following error notification: "Failed to send email: no recipient (line 40, file "Code")", where line 40 is the line:

MailApp.sendEmail(userEmail, subject, body, {htmlBody: body, attachments: pdf});

I have tried using getNote() instead of getValue() for the userEmail variable but that didn't work either! I have also made sure the cell reference on the spreadsheet is formatted as plain text rather than as a number, but I'm not sure what else to try now! Any suggestions would be greatly appreciated!

Thanks so much in advance :)

Zehrazjp20 Zehrazjp20 · Accepted Answer · 2015-07-15T12:04:07

It's working now since I changed:

var ssID = '1dMmihZoJqfLoZs9e7Y.............';
var ss = SpreadsheetApp.openById(ssID);
ss.setActiveSheet(ss.getSheetByName("Sheet3"));
var totalOutstandingPrincipalDebt = SpreadsheetApp.getActiveSheet().getRange("G25").getValue();
var totalOutstandingInterest = SpreadsheetApp.getActiveSheet().getRange("H25").getValue();
var totalOutstandingCompensation = SpreadsheetApp.getActiveSheet().getRange("I25").getValue();
var dailyInterestRate = SpreadsheetApp.getActiveSheet().getRange("J25").getValue();
var grandTotal = SpreadsheetApp.getActiveSheet().getRange("K25").getValue();
var userEmail = SpreadsheetApp.getActiveSheet().getRange("H24").getValue();

to:

 var ssID = '1dMmihZ.................';
var ss = SpreadsheetApp.openById(ssID);
var sheet = SpreadsheetApp.setActiveSheet(ss.getSheets()[0]);
var totalOutstandingPrincipalDebt = sheet.getRange("G25").getValue();
var totalOutstandingInterest = sheet.getRange("H25").getValue();
var totalOutstandingCompensation = sheet.getRange("I25").getValue();
var dailyInterestRate = sheet.getRange("J25").getValue();
var grandTotal = sheet.getRange("K25").getValue();
var userEmail = sheet.getRange("H24").getValue();

onFormSubmit trigger not working for sending out email upon form submission

1 Answers