CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Just
Just

Reputation: 437

How to avoid sending email duplicates after a value in a cell is updated: Javascript

I am trying to get values from a google sheet sent to email and a value in the cell updated. This works.

However, when, I ran the code, again, it picks from the initial row where the value has been updated to 3 (to avoid duplication of email).

I want it to ignore the previously sent row and only pick What row where column 41 is not set to 3. What could I be doing wrong here?

function sendEmails() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet(); 
  var sheet = SpreadsheetApp.getActiveSheet();
  var dataRange = sheet.getDataRange();
  var data = dataRange.getValues();
  for (var i = 1; i < data.length; ++i) {
    var rowData = data[i];
    var sender = rowData[1];
    var name = rowData[2];
    var client = rowData[3];
    var sdate = rowData[4];
    var edate = rowData[5];
    var loe = rowData[7];
    var emailSent = rowData[41];
    var emailAddress = '[email protected]';
    var message = 'Email:' + sender + ',\n\n' + name + ' ' + client + ',\n\n' + sdate + ',\n\n' + edate + ',\n\n' + loe;
    var subject = 'Alert- New Project';
    if (emailSent !== '3' && MailApp.getRemainingDailyQuota()>0 && emailAddress && subject && message) {       
      MailApp.sendEmail(emailAddress, subject, message);
      sheet.getRange(i+1, 41).setValue('3');
    }
  }
}

Upvotes: 0

Views: 107

Answers (1)

Cooper
Cooper

Reputation: 64092

Try this:

row[41] is column 42;

column indexes start at one

array indexes start at zero

   function sendEmails() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getActiveSheet(); 
      var dataRange = sheet.getDataRange();
      var data = dataRange.getValues();
      for (var i = 1; i < data.length; ++i) {//this skips the first row in the data array which is often a header.
        var rowData = data[i];
        var sender = rowData[1];//column2
        var name = rowData[2];//column3
        var client = rowData[3];//column4
        var sdate = rowData[4];//column5
        var edate = rowData[5];//column6
        var loe = rowData[7];//column8
        var emailSent = rowData[40];//Changed this from 41 to 40.Array index 41 is column 42
        var emailAddress = '[email protected]';
        var message = 'Email:' + sender + ',\n\n' + name + ' ' + client + ',\n\n' + sdate + ',\n\n' + edate + ',\n\n' + loe;
        var subject = 'Alert- New Project';
        if (emailSent !== '3' && MailApp.getRemainingDailyQuota()>0 && emailAddress && subject && message) {       
          MailApp.sendEmail(emailAddress, subject, message);
          sheet.getRange(i+1, 41).setValue('3');//the 41 is a column index not an array index
        }
      }
    }

Upvotes: 1

Related Questions