Send emails to a specific email address only when row is not empty-CodePudding

I have a code that sends out emails to a specific email address (in this case lets say its [email protected]) based on the values in my Google Sheet tab named 'Send Emails [Team A]'. It runs when I click on the menu item 'To Team A'. The code is sending out emails fine, however it also runs on rows that are blank, thus sending out blank emails to [email protected]. The sheet will be updated from time to time which is why I did not limit the range until a specific row. Is there a way to make the code run only on rows that are not blank?

Here's the code that I'm using:

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu("Send Emails")
      .addItem("To Team A", "toTeamA")
      .addToUi();

// This constant is written in column C for rows for which an email
// has been sent successfully.
var EMAIL_SENT = "Email Sent";

function toTeamA() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("Send Emails [Team A]");
  var startRow = 2; // First row of data to process would be '2' because the first row is header
  var numRows = 1000; // Number of rows to process
  var dataRange = sheet.getRange('A2:D') // Gets the data range
  var data = dataRange.getValues();
  for (var i = 0; i < data.length;   i) {
    var row = data[i];
    var emailAddress = "[email protected]";
    var subject = row [1];      // Second column
    var message = row [0];      // First column
    var emailSent = row [2];    // Third column
    if (emailSent != EMAIL_SENT) { // Prevents sending duplicates
      MailApp.sendEmail(emailAddress, subject, message);
      sheet.getRange(startRow   i, 3).setValue(EMAIL_SENT);
      // Makes sure cell is updated right away with "Email Sent" in case the script is interrupted
      SpreadsheetApp.flush();  
    }
  }
}

Any help is much appreciated!

CodePudding user response：

Sure, assuming your rows are empty, e.g. rows 1-100 contain content, whereas 101 onwards it is empty. You can find the last row with data using sheet.getLastRow(), see docs.

So instead of doing sheet.getRange("A2:D") you can do sheet.getRange("A2:D" sheet.getLastRow()) which should fix the problem.