Google Sheets keeps sending emails

Question

Been building a landing page to start an email list. Decided to use Google Sheets as the backend to store emails & Google Scripts to send the first welcome message.

I hooked up an HTML page to Google Sheets with an API. The user submits their name & email, and then it goes to the Google Sheets. When the document is edited, this function gets triggered which sends a welcome message.

To prevent duplicate messages from going out, I put a simple system in place. When a user has been emailed, the third row (C) is filled in with "EMAIL_SENT" automatically.

When the function is triggered, it should only send the user an email if if there is no "EMAIL_SENT" but every time a new user submits their info, every single email on the list gets another welcome message.

I will attach an image of the spreadsheet

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

/**
 * Sends non-duplicate emails with data from the current spreadsheet.
 */
function sendEmails2() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2; // First row of data to process
  var numRows = sheet.getLastRow();
  // Fetch the range of cells A2:B3
  var dataRange = sheet.getRange(startRow, 1, numRows, 3);
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var emailAddress = row[0]; // First column
    var nameThisMan = row[1]; // Person's name
    var emailSent = row[2]; // Third column
    
    if (emailSent !== EMAIL_SENT) { // Prevents sending duplicates
    var message = "Hey there " + nameThisMan + "," + "\n \n Thanks for joining the email list! You’re about to be getting some AMAZING programming resources. Here’s the link to the top 10 open source Github repos PDF. \n https://www.dropbox.com/s/wm8ctsdojlsoss6/10%20Most%20Useful%20Open%20Source%20Repos%20for%20Developers.pdf?dl=0\n\n And here’s the link to the archive of all the resources from this email list. These are from all past emails. \n https://docs.google.com/document/d/1hWmGNYkn0czRI29JJu9HgUVC6L4AsNYBxwt45ABnT6w/edit?usp=sharing \n \n Thank you again for joining the list! I will do my best to give you absolutely amazing programming resources. \n \n best, \n Michael Macaulay, Techtime.";
    var replyTo = "[email protected]";
    var subject = "Welcome to TechTime's Email List";
      MailApp.sendEmail(emailAddress, replyTo, subject, message);
      sheet.getRange(startRow + i, 3).setValue(EMAIL_SENT);
      // Make sure the cell is updated right away in case the script is interrupted
      SpreadsheetApp.flush();
    }
  }
}

Answer 1

function sendEmails2() {
  var sheet=SpreadsheetApp.getActiveSheet();
  var startRow=2;
  var numRows=sheet.getLastRow()-startRow+1;
  var dataRange=sheet.getRange(startRow, 1, numRows, 3);
  var data=dataRange.getValues();
  for (var i=0; i < data.length; ++i) {
    var row=data[i];
    var emailAddress=row[0];
    var nameThisMan=row[1];
    var emailSent=row[2]; 
    var replyTo="[email protected]";
    var subject="Welcome to TechTime's Email List";
    if (emailSent!='EMAIL_SENT') { 
      var message="Hey there " + nameThisMan + "," + "\n \n Thanks for joining the email list! You’re about to be getting some AMAZING programming resources. Here’s the link to the top 10 open source Github repos PDF. \n https://www.dropbox.com/s/wm8ctsdojlsoss6/10%20Most%20Useful%20Open%20Source%20Repos%20for%20Developers.pdf?dl=0\n\n And here’s the link to the archive of all the resources from this email list. These are from all past emails. \n https://docs.google.com/document/d/1hWmGNYkn0czRI29JJu9HgUVC6L4AsNYBxwt45ABnT6w/edit?usp=sharing \n \n Thank you again for joining the list! I will do my best to give you absolutely amazing programming resources. \n \n best, \n Michael Macaulay, Techtime.";
      MailApp.sendEmail(emailAddress, replyTo, subject, message);
      sheet.getRange(startRow + i, 3).setValue('EMAIL_SENT');
    }
  }
}

Answer 2

Solution

The method getLastRow() will return you the index of the last row with content. In the example you have provided this would be 3. However, what you actually want for performing getRange() is the number of rows you need to take. If you use three you would be taking an extra empty row and therefore, you would be getting the wrong values.

To change this to get the number of rows you need, you can simply do:

var numrows = sheet.getLastRow() - 1; instead of var numRows = sheet.getLastRow();

as you need to substract from the index the number of rows you are not wanting to get in your range (in your case as you are ignoring the first row only 1 needs to be substracted).

I hope this has helped you. Let me know if you need anything else or if you did not understood something. :)