emailgoogle-apps-scriptgoogle-sheets

Why Is an Email Only Sent to Last Email Address in the Column?


In this script, I am trying to send an email to email addresses in range B2:B. I have included my email address in the column to test it out. I am getting an email as expected. However, no one else is.

function sendReminderEmailTest() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Send_Email");
  var range = sheet.getRange(2,2,sheet.getLastRow(),1);

  var emailAddress = range.getValues()
    .flat()           // convert a 2d array into a flat array
    .filter(String)   // remove empty elements from the array
    .pop();           // get the last element from the array

  
  var html_link = "<a href='something.com'> Site Name</a>";

  var html_body =  "Good morning,<br><br>"
                 + "This is just a reminder to check the " + html_link + " page to verify that today's attendance is up-to-date. <br>"
                 + "Thank you, <br>"
                 + "Management <br><br>"
                 + "<em>This email is an automated notification. Please do not reply.</em>"
  
  MailApp.sendEmail({
    to: emailAddress,
    subject: "Reminder",
    body: "",
    htmlBody: html_body,
    noReply: true
  });

  
}

Screenshot

Is something wrong with the script or with permissions? I have set up an installable trigger and the log indicates that the script was run without errors.

Update: The email is only sent to the last email address in the column. When I switched the data in the columns and moved my email up in the column, the email was sent to the last email address in the column.

Tried removing pop() from the code and got an error: Invalid Email.

Any help would be greatly appreciated.


Solution

  • Try it this way:

    function sendReminderEmailTest() {
      
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getSheetByName("Send_Email");
      var range = sheet.getRange(2,2,sheet.getLastRow()-1,1);//you were getting too many rows in this version of getRange the third  parameter is number of rows not the last row.
    
      var emailAddress = range.getValues()
        .flat()           // convert a 2d array into a flat array
        .join(',');       // join the emails with commas and now that you are getting the correct number of row you are not require to eliminate the last element in the array.
    
      
      var html_link = "<a href='something.com'> Site Name</a>";
    
      var html_body =  "Good morning,<br><br>"
                     + "This is just a reminder to check the " + html_link + " page to verify that today's attendance is up-to-date. <br>"
                     + "Thank you, <br>"
                     + "Management <br><br>"
                     + "<em>This email is an automated notification. Please do not reply.</em>"
      
      MailApp.sendEmail({
        to: emailAddress,
        subject: "Reminder",
        body: "",
        htmlBody: html_body,
        noReply: true
      });
    
      
    }
    

    I presummed that everything else worked