CODEWITHSUNDEEP
javascriptemailgoogle-apps-scriptgoogle-sheets
user2122765
user2122765

Reputation: 13

Sending emails from google doc spreadsheet

Workign on a script to send out emails when job has been completed. I added in a piece which I thought would prevent from emailing twice. Having trouble figuring out why. 

function sendEmail() 
{
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;
  var numRows = sheet.getLastRow();
  var EMAIL_SENT = "EMAIL_SENT";
  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[1];
    var message = "Correction Completed for " + row[2]; 
    var subject = "Correction Completed";
    var emailSent = row[9];

    if (emailSent != "EMAIL_SENT") //prevent sending duplicate messages
    {
    MailApp.sendEmail(emailAddress, subject, message);
      sheet.getRange(startRow + i, 9).setValue(EMAIL_SENT);

      SpreadsheetApp.flush();  
    }

  }

}

My thinking was putting the sendEmail portion in an if statement, it would not send. However it still sends an email regardless. Thank you in advance.

Upvotes: 1

Views: 1669

Answers (2)

Serge insas
Serge insas

Reputation: 46794

Let me clarify 2 things :

  1. When you use sheet.getRange(startRow + i, 9).setValue(EMAIL_SENT); you are indeed writing the EMAIL-SENT label in column 9 because getRange works with column numbers starting from 1.
  2. When yo read data in an array (that comes from a sheet range) the index in this array starts on 0, that means that you should use var emailSent = row[8]; to get the corresponding 9th value
  3. (yes I know I said 2 things ;-) The array must at least contain this 9th column so you should read it using an appropriate range : var dataRange = sheet.getRange(startRow, 1, numRows, 9) for example...(but not less than 9 in any case)

Hoping this is clear enough.

Upvotes: 0

Mostafa Shahverdy
Mostafa Shahverdy

Reputation: 2725

Your problem is your var dataRange = sheet.getRange(startRow, 1, numRows, 3). Try to change 3 to something bigger than 9, because of var emailSent = row[9];. When you read emailSent pay attention to read previous written status.

Upvotes: 1

Related Questions