Data and email list in two different sheet

1
votes

I have the data in the Backend sheet and I have the email list on Subscribers sheet, but the email is not sent to the list, where did I go wrong?

function sendEmail(e) {
var thisSheet = e.source.getActiveSheet();
if (thisSheet.getName() !== 'Backend' || e.range.columnStart !== 17 || e.range.rowStart == 1 || e.value !== 'LOCAL') return;
var body, headers = thisSheet.getRange(1, 1, 1, 6)
    .getValues()[0],
    thisRow = thisSheet.getRange(e.range.rowStart, 1, 1, 6)
    .getValues()[0],
    vehicle = thisRow[3],
    vehicle2 = thisRow[4],
    subject = "⚫ Vehicle Ready "+vehicle +" "+vehicle2
    
    var emailList = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Subscribers").getRange("A2:A").getValues();
    
    body = "<i>The following vehicle is ready after wash</i><br><br>",
    i = 0;
while (i < 6) {
    body += '<font style="font-size:14px;color:grey;font-family: Arial">'+headers[i] +'</font>'+' - <b><font style="font-size:14px;font-family: Arial">' + thisRow[i] +'</font></b><br><hr width="30%" align="left" Color="#bfbfbf" size="0.75">';
    i++;
}

MailApp.sendEmail(emailList, subject, body, {htmlBody: body, name: "company"}); }enter code here

1
javascriptgoogle-apps-scriptgoogle-sheetstriggers

1 Answers

1
votes

How about this modification?

Modification points:

  • The values retrieved by getValues() is 2 dimensional array. In your case, emailList of var emailList = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Subscribers").getRange("A2:A").getValues() is like [["email1"],["email2"],,,]. And also, in the case of range "A2:A", no values might be included.
  • At MailApp.sendEmail(recipient, subject, body, options), recipient is a string.

By above points, such error occurs. When you want to send the email to all emails in emailList, it is required to use a loop. Or, when you want to use the emails in emailList as cc, it is not required to use the loop. So here, I would like to propose the following 2 patterns.

Pattern 1:

In this pattern, all emails in emailList is used as recipient.

Modified script:

When your script is modified, please modify as follows.

MailApp.sendEmail(emailList, subject, body, {htmlBody: body, name: "company"});

emailList.forEach(([email]) => {
  if (email != "") {
    MailApp.sendEmail(email, subject, body, {htmlBody: body, name: "company"});
  }
})

Pattern 2:

In this pattern, all emails in emailList is used as cc.

Modified script:

When your script is modified, please modify as follows.

MailApp.sendEmail(emailList, subject, body, {htmlBody: body, name: "company"});

MailApp.sendEmail("email address", subject, body, {htmlBody: body, name: "company", cc: emailList.filter(String).toString()});

or

MailApp.sendEmail(null, subject, body, {htmlBody: body, name: "company", cc: emailList.filter(String).toString()});
  • In this case, when null is used as recipient, the email doesn't include the email address of to.

Note:

  • Please use this modified script with enabling V8.

References: