Need help - 2:41:00 PM Error Exception: Failed to send email: no recipient sendEmail @ Send email to recipeint.gs:42

[C. Omar Kebbeh]

Hi all,

I have the following code which sends an email back to those who submit a google form. In column 2, I collect their emails, and in column 14 I restrict the recipients to only those who just submitted. It worked for while but now it is showing the error below even though the emails are in column 2. 

Any thoughts.... 

2:41:00 PM

Error

Exception: Failed to send email: no recipient

sendEmail

@ Send email to recipeint.gs:42

function sendEmail() {

var ss = SpreadsheetApp.getActiveSpreadsheet()

var sheet1=ss.getSheetByName('Form responses 1');

var sheet2=ss.getSheetByName('Clearances');

var subject = sheet2.getRange(2,7).getValue();

var n=sheet1.getLastRow();

for (var i = 2; i < n+1 ; i++ ) {

var emailAddress=sheet1.getRange(i,2).getValue();  

var name=sheet1.getRange(i,3).getValue();

var details=sheet1.getRange(i,5).getValue();

var title=sheet1.getRange(i,6).getValue();

var link=sheet1.getRange(i,7).getValue();

var urgent=sheet1.getRange(i,9).getValue();

var clearance_date=sheet2.getRange(i,45).getValue();

var casenumber=sheet2.getRange(i,15).getValue();

var poclearancedate=sheet2.getRange(i,6).getValue();

var datereceived=sheet2.getRange(i,4).getValue();

var team=sheet2.getRange(i,41).getValue();

var teamEmail=sheet2.getRange(i,42).getValue();

var emailSent=sheet1.getRange(i,14).getValue();

var tracker="https://docs.google.com/spreadsheets/d/1GGAvcrZK1Sc-O-C5PSaYdv3iVhdUcool_yG-pFbvr9M/edit?resourcekey#gid=782709859";

if ( !(emailSent == 'EMAIL_SENT')){

var subject="The PO Received Your Clearance/Approval Request!";  

var message = "Dear " + name + "," + "<br><br>"+

                    "The Program Office has received your clearance/approval request with the following details:" + "<br><br>"+

                    "Date Received in PO: " + datereceived + "<br><br>"+

                    "Your case number is: " + casenumber + "<br><br>"+

                    "Requested clearance date: " + clearance_date + "<br><br>"+

                    "PO standard clearance date: " + poclearancedate + "<br><br>"+

                    "Type of Clearance/Approval Requested: " + details + "<br><br>"+

                    "Title of the document/correspondence: " + title + "<br><br>"+

                    "Link to document or folder: " + link + "<br><br>"+

                    "Urgent: " + urgent + "<br><br>"+

                    "Your request has been assigned to: "+team+ "<br><br>"+

                    "You may contact the team: "+teamEmail+ "<br><br>"+

                    "You can track the status of your clearance at: " + tracker + "<br><br>"+

                    "Regards,"+ "<br><br>"+

                    "Program Office"+ "<br><br>";

GmailApp.sendEmail(emailAddress, subject, "",{htmlBody:message});

  sheet1.getRange(i, 14).setValue('EMAIL_SENT')

}

}

}

[George Ghanem]

Hi,

Your script has a for loop and it is going through all the non blank rows. This means it is reading all the rows whether they were updated by the form or not.

Try deleting all the rows and start fresh.

Another way to fix this would be just to use a trigger to do the send when a form entry is submitted. In that case, just look at that one row instead of looping over the whole spreadsheet.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/505bd0a0-8992-458d-a99c-1c1a7b2137fan%40googlegroups.com.