I am attempting to send emails collected on a google form. I receive Error:
Failed to send email: no recipient
I am guessing this happens when the script comes to a blank cell that should have email address.
Any help with how I can make sure emails get sent when an invalid email address or blank cell would greatly appreciated.
function sendemails() {
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Emails").activate();
var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var lr = ss.getLastRow();
var templateText = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Template").getRange(1,1).getValue();
for (var i = 3;i<=lr;i++){
var Student = ss.getRange(i, 2).getValue();
var ParentName = ss.getRange(i, 3).getValue();
var currentEmail = ss.getRange(i, 6).getValue();
var currentEmail2 = ss.getRange(i, 8).getValue();
var currentEmail3 = ss.getRange(i, 10).getValue();
var currentEmail4 = ss.getRange(i, 12).getValue();
var currentEmail5 = ss.getRange(i, 14).getValue();
var currentEmail6 = ss.getRange(i, 16).getValue();
var currentEmail7 = ss.getRange(i, 18).getValue();
var currentEmail8 = ss.getRange(i, 20).getValue();
var currentEmail9= ss.getRange(i, 22).getValue();
var currentEmail10 = ss.getRange(i, 24).getValue();
var messageBody = templateText.replace("{StudentName}",Student).replace("{Parent}", ParentName);
var subjectLine = "Sweetwater Elementary Light It Up! Fundraiser from " + Student;
GmailApp.sendEmail(currentEmail, subjectLine, messageBody);
GmailApp.sendEmail(currentEmail2, subjectLine, messageBody);
GmailApp.sendEmail(currentEmail3, subjectLine, messageBody);
GmailApp.sendEmail(currentEmail4, subjectLine, messageBody);
GmailApp.sendEmail(currentEmail5, subjectLine, messageBody);
GmailApp.sendEmail(currentEmail6, subjectLine, messageBody);
GmailApp.sendEmail(currentEmail7, subjectLine, messageBody);
GmailApp.sendEmail(currentEmail8, subjectLine, messageBody);
GmailApp.sendEmail(currentEmail9, subjectLine, messageBody);
GmailApp.sendEmail(currentEmail10, subjectLine, messageBody);
}
4
You want to avoid sending an email when the recipients address was not supplied.
There are several ways of assessing whether an array value (in this case, a recipient email address) exists:
- test for a truthy value
- test for a blank value
Do NOT test for a “NULL” value since a non-reply in Google Forms does not generate a “Null” (either in the Form response or in the values transmitted to a linked spreadsheet)
Example:
Data array extract: [Wed Sep 11 01:04:28 GMT+10:00 2024, [email protected], Paul, ]
if(data[1][3] ){ // truthy
Logger.log("01:the question was answered = "+data[1][3])
}
else{
Logger.log("01:the question wasn't answered")
}
if(data[1][3] != ''){ // blank value
Logger.log("02:the question was answered = "+data[1][3])
}
else{
Logger.log("02:the question wasn't answered")
}
if(data[1][3] != null){ // test for null
Logger.log("03:the question was answered = "+data[1][3])
}
else{
Logger.log("03:the question wasn't answered")
}
Results:
- 01:the question wasn’t answered // correct
- 02:the question wasn’t answered // correct
- 03:the question was answered = // incorrect
So how to process in the script? here’s a suggestion
var Student = ss.getRange(i, 2).getValue();
var ParentName = ss.getRange(i, 3).getValue();
var messageBody = templateText.replace("{StudentName}",Student).replace("{Parent}", ParentName);
var subjectLine = "Sweetwater Elementary Light It Up! Fundraiser from " + Student;
// get recipient address, test for truthy and send email
var currentEmail = ss.getRange(i, 6).getValue();
if(currentEmail){ // test for truthy
Logger.log("the recipient is: "+currentEmail)
GmailApp.sendEmail(currentEmail, subjectLine, messageBody);
}
else{
Logger.log("the question wasn't answered; recipient is blank")
}
//Rinse and repeat for each subsequent recipient email address