sendEmail function in Google Sheets

Question

I have been struggling for days to get this appScript to function within my Google Sheets. Currently it is getting "stuck" on MailApp.sendEmail line, particularly on emailAddress. It gives me this error message "Invalid email: undefined". I based the majority of the script on their Sending Emails from a Spreadsheet Tutorial. So I'm not too sure where it might be going wrong.

Any help is much appreciated!

function sendEmails() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();  
  var sh0 = ss.getSheetByName("Candidates Ready for Offer");
  var startRow = 2;  // First row of data to process
  var numRows = ss.getSheetByName("Candidates Ready for Offer").getLastRow()-1;
  // Fetch recent additions
  var dataRange = ss.getSheetByName("Candidates Ready for Offer").getRange(startRow, 3)
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (i in data) {
    var row = data[i];
    var emailAddress = row[5];  
    var message = "row[1]" + "row [2]" + "at" + "row[4]" + "is ready for an offer!"
    var subject = "Candidate is Ready for Offer";
    MailApp.sendEmail(emailAddress, subject, message);
  }
}

Oli Trussell Oli Trussell · Accepted Answer · 2015-03-31T18:18:37

Thanks for sharing your sheet. There was actually a number of reasons why it wasn't working which I'll try and explain. Anyway, your script on the shared sheet will now function as you want it to. Here is what it looks like:

function sendEmails() {
var ss = SpreadsheetApp.getActiveSpreadsheet();  
var sh0 = ss.getSheetByName("Candidates Ready for Offer");
var startRow = 4;  // First row of data to process
var numRows = sh0.getLastRow()-startRow+1;
// Fetch recent additions
var dataRange = sh0.getRange(startRow,1,numRows,6);
// Fetch values for each row in the Range.
var data = dataRange.getValues();
Logger.log(data);
for (i in data) {
var row = data[i];
var emailAddress = row[3]; 
Logger.log(emailAddress);
var message = "row[1]" + "row [2]" + "at" + "row[4]" + "is ready for an offer!"
var subject = "Candidate is Ready for Offer";
MailApp.sendEmail(emailAddress, subject, message);
}
}

There's no need to keep calling the same sheet when you've already defined it as the variable sh0
The data range was including the first 3 rows. You didn't want this. Some simple maths in the var numRows allows the spreadsheet to only take the range containing data starting at row 4.
You were referencing the wrong column for email address. It was in column 4.

I hope this helps/makes sense.

Oli

sendEmail function in Google Sheets

2 Answers