Google Apps Script sendEmail: multiple recipients string?

3
votes

Using sendEmail, how can I send an email to multiple comma-separated recipients by combining two form fields? It seems to work when (lastrow,4) has only one value ([email protected]) but not for more than one ([email protected], [email protected]). Current code is below, and the variable in question is recipientsTo.

function FormEmail() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetform = ss.getSheetByName("Sheet1"); // Name of the sheet which contains the results
  var lastrow = sheetform.getLastRow();
  var recipientsTO = sheetform.getRange(lastrow,3).getValue() + "@domain.com";
  var recipientsCC = ""
  var team = sheetform.getRange(lastrow,5).getValue();
  var datestamp = Utilities.formatDate(sheetform.getRange(lastrow,1).getValue(), "GMT - 5", "yyyy-MM-dd");
  var html = "Intro text;

  //The questions order in the email will be the order of the column in the sheet  
    for (var i = 2; i < 11; ++i) {
    html = html + "<b>" + sheetform.getRange(1,i).getValue() + "</b><br>";
    html = html + sheetform.getRange(lastrow,i).getValue() + "</p><br>";
       }

  //Add additional emails if entered in form field
  if (sheetform.getRange(lastrow,4).getValue() !== "") {
    recipientsTO = recipientsTO + "," + sheetform.getRange(lastrow,4).getValue()
  }

  //CC if response to a question is "Yes"
  if (sheetform.getRange(lastrow,10).getValue() == "Yes") {
    recipientsCC = "[email protected]"
  }


  MailApp.sendEmail({
    to: recipientsTO,
    cc: recipientsCC,
    subject: "Subject",
    htmlBody: html,
});


}
2
google-apps-script
You might try adding Logger.log(recipientsTO) just below the if statement that adds the additional email addresses and then viewing the log to see if you can see any malformed addesses. – ScampMichael

2 Answers

3
votes

According to the sendEmail(message) documentation, the TO field only has one recipient. Whereas the CC field can have multiple recipients separated by comma.

http://goo.gl/CGjiJ

`to - String - the address of the recipient.

cc -String - a comma separated list of email addresses to CC`

Another option would be to use sendEmail(String,String,String,Object) in that function "recipient String the addresses of the recipients, separated by commas".

Hope this helps.

0
votes

Here is code from my production script:

//check quota and log
const emailsLeft = MailApp.getRemainingDailyQuota();
console.log( emailsLeft + " emails left in quota");

//get list of emails from spreadsheet itself
//filter out empty rows
const emails = getTab("Config").getRange("D2:D").getValues().map(function(el){ return el[0]; }).filter(function(el){ return el != '' });  

//send emails from NO_REPLY email, subject and HTML body
MailApp.sendEmail({
  to: emails.join(","),
  subject: subject,
  htmlBody: html,
  noReply: true
});

getTab() and other helper functions can be found here https://github.com/tim-kozak/google-spreadsheet-helpers