CODEWITHSUNDEEP
javascriptgoogle-apps-script
Zieyra
Zieyra

Reputation: 5

How to run script without duplicate

So, I came across this very good script of how to send an email (Gmail) using data taken from gsheet.

The problem is, every time a new row is added (a new email address is added), an email will be sent to the new email address and the existing one.

I would like to know how to run a script but still able to skip the existing data.

function sendEmail() {

var ss = SpreadsheetApp.getActiveSpreadsheet()
var sheet1=ss.getSheetByName('OMS');
var sheet2=ss.getSheetByName('Email');
var subject = sheet2.getRange(2,1).getValue();;
var message = sheet2.getRange(2,2).getValue();
var n=sheet1.getLastRow();
for (var i = 2; i < n+1 ; i++ ) {
var emailAddress = sheet1.getRange(i,4).getValue();
MailApp.sendEmail(emailAddress, subject, message);
}

}

Thanks in advance

Upvotes: 0

Views: 47

Answers (1)

iansedano
iansedano

Reputation: 6481

Modified Script

function sendEmail() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheetByName("OMS");
  var sheet2 = ss.getSheetByName("Email");
  var subject = sheet2.getRange(2, 1).getValue();
  var message = sheet2.getRange(2, 2).getValue();
  var n = sheet1.getLastRow();
  for (var i = 2; i < n + 1; i++) {
 
    if (sheet1.getRange(i, 5).getValue() === "") { // NEW LINE

      var emailAddress = sheet1.getRange(i, 4).getValue();
      MailApp.sendEmail(emailAddress, subject, message);

      sheet1.getRange(i, 5).setValue("EMAIL SENT") // NEW LINE
    } // NEW LINE
  }
}

This will require you to add an extra column (column 5), that this script will now check for an empty value. If the value is not empty, then it will skip this row. If it is empty, then it will add "EMAIL SENT" to column 5.

if statement documentation if you are interested to learn more about conditional execution.

Upvotes: 1

Related Questions