function sendEmails() from Google Sheet : error message = Exception: Failed to send email: no recipient (line 17, file "SendEmail")

Question

I have some trouble to run the function sendEmails() from my Google sheet. I based my script from this tutorial: https://developers.google.com/apps-script/articles/sending_emails it's from May 2009, I don't know if it's always up to date. The script from the tutorial is this one:

/**
 * Sends emails with data from the current spreadsheet.
 */
function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2; // First row of data to process
  var numRows = 2; // Number of rows to process
  // Fetch the range of cells A2:B3
  var dataRange = sheet.getRange(startRow, 1, numRows, 2);
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (var i in data) {
    var row = data[i];
    var emailAddress = row[0]; // First column
    var message = row[1]; // Second column
    var subject = 'Sending emails from a Spreadsheet';
    MailApp.sendEmail(emailAddress, subject, message);
  }
}

I have follow what I have to do on Google sheet (create 1 column Email Adress, 1 column message). My columns will be fill automatically from Google Form. That's why I adapt the script, and change "var numRows = 2;" by "var numRows = 1000;" to include the 1000 first row.

The script "works" when I run it manually (click on play button). Emails are sent, but I receive an error message : Exception: Failed to send email: no recipient (line 17, file "SendEmail")

However, it doesn't work with my trigger. I set a trigger: "SendEmails" / "From spreadsheet" / "On form submit" (I supposed that is how I have to configure? But I'm trying with event type "On Edit" and "On change" and it seems the same issue = doesn't work) how I set the triger

When I check on "My executions", I can see failed status: [in french] Error Exception: Les paramètres ((class),String,String) ne correspondent pas à la signature de la méthode "MailApp.sendEmail". at sendEmails(SendEmail:17:13) [in english I supposed] Error Exception: The parameters ((class), String, String) do not correspond to the signature of the "MailApp.sendEmail" method. at sendEmails (SendEmail: 17:13)

Do you have ideas where I don't set correctly the script and/or the trigger? And of course, how to fix it?

Thanks a lot for your answers!

I'd love to help you but I need to see an image of your spreadsheet. — Cooper, Mar 03 '20 at 18:00

score 3 · Accepted Answer · edited Jun 20 '20 at 09:12

While the code is probably working okay I would rewrite it like this:

function sendEmails() {
  var ss=SpreadsheetApp.openById("ssid");
  var sheet=ss.getSheetByName('**********Your sheet name***********');
  var startRow=2; 
  var numRows=2;
  var dataRange=sheet.getRange(startRow, 1, numRows, 2);
  //changed to getDisplayValues
  var data=dataRange.getDisplayValues();
  //changed to for loop
  for (var i=0;i<data.length;i++) {
    var row=data[i];
    var emailAddress=row[0]; 
    var message=row[1];
    var subject='Sending emails from a Spreadsheet';
    MailApp.sendEmail(emailAddress, subject, message);
  }
}

The error message indicates that there is something wrong with your data. MailApp.send expects three strings and the error indicates that there is something wrong with the column A. It could be something on your spreadsheet or in the way that your reading it.

So perhaps changing from getValues() to getDisplayValues() might be a remedy.

.....

You should also avoid using this for (var i in data) { when the data object is a standard array object. For a more complete explanation please read here

Your spreadsheet should look something like this:

Another possibility is that since your triggering the code from an onFormSubmit trigger then the active page will always be the first sheet on the left. If that's not the page you wish then you should get the page by name as shown in the recommended rewrite.

function sendEmails() from Google Sheet : error message = Exception: Failed to send email: no recipient (line 17, file "SendEmail")

1 Answers1