CODEWITHSUNDEEP
google-apps-script
Tooa
Tooa

Reputation: 1

Non-Duplicate Email Google Sheet Script - Prevent Sending Duplicate Emails Once I Run the Script

How do I avoid sending duplicate emails once I run the script. The script I created is mentioned below. I just want to send the attributes ONCE to the email on the spreadsheet once I run the script.

var EMAIL_SENT = 'EMAIL_SENT';

function sendEmails() {

  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses 1").activate();

  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
  var lr = ss.getLastRow()

  var templateText = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Subject Template").getRange(1, 1).getValue();

  var quotaLeft = MailApp.getRemainingDailyQuota();

  if ((lr-1) > quotaLeft) {

    Browser.msgBox("You have " + quotaLeft + " left and you're trying to send " + (lr-1) + " emails. Emails were not sent.")

  } else {

    for (var i = 2;i<=lr;i++) {

      var currentEmail = ss.getRange(i, 6).getValue();
      var currentSubject = ss.getRange(i, 5).getValue()
      var currentUser = ss.getRange(i, 2).getValue();
      var currentDivision = ss.getRange(i, 3).getValue();
      var currentIssue = ss.getRange(i, 5).getValue();
      var currentSeveritylevel = ss.getRange(i, 4).getValue();
      var emailSent = ss.getRange(i, 9);

      var messageBody = templateText.replace("{User}",currentUser).replace("{division}",currentDivision).replace("{Issue}",currentIssue).replace("{Severity level}", currentSeveritylevel);
      var subjectLine = "Tech Support Issue: " + currentIssue + " for " + currentUser;

      if (emailSent !== EMAIL_SENT) {
        MailApp.sendEmail(currentEmail, subjectLine, messageBody);
        ss.getRange(i, 9).setValue('EMAIL_SENT');
      } 
    }
  }
}

Upvotes: 0

Views: 186

Answers (1)

Andrew Roberts
Andrew Roberts

Reputation: 2798

You need to add a getValue() to the line that gets emailSent. Not essential, but the single quotes also need removing from EMAIL_SENT when emailSent is being checked.

var EMAIL_SENT = 'EMAIL_SENT';

function sendEmails() {

  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses 1").activate();

  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
  var lr = ss.getLastRow()

  var templateText = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Subject Template").getRange(1, 1).getValue();

  var quotaLeft = MailApp.getRemainingDailyQuota();

  if ((lr-1) > quotaLeft) {

    Browser.msgBox("You have " + quotaLeft + " left and you're trying to send " + (lr-1) + " emails. Emails were not sent.")

  } else {

    for (var i = 2; I <=lr; i++) {

      var currentEmail = ss.getRange(i, 6).getValue();
      var currentSubject = ss.getRange(i, 5).getValue()
      var currentUser = ss.getRange(i, 2).getValue();
      var currentDivision = ss.getRange(i, 3).getValue();
      var currentIssue = ss.getRange(i, 5).getValue();
      var currentSeveritylevel = ss.getRange(i, 4).getValue();
      var emailSent = ss.getRange(i, 9).getValue();

      var messageBody = templateText.replace("{User}",currentUser).replace("{division}",currentDivision).replace("{Issue}",currentIssue).replace("{Severity level}", currentSeveritylevel);

      var subjectLine = "Tech Support Issue: " + currentIssue + " for " + currentUser;

      if (emailSent !== EMAIL_SENT) {
        MailApp.sendEmail(currentEmail, subjectLine, messageBody);
        ss.getRange(i, 9).setValue(EMAIL_SENT);
      } 
    }    
  }

Upvotes: 1

Related Questions