Google Aps Script: script to send auto-emails on new form response sends both new and previous emails

user_18633057 0
Viewed 41

I wrote a script to send a specific types of emails based on type of information submitted through Google forms. Its triggered on form submission. However, when a new submission is made, the right email for that submission is sent, but also the last email that was sent. (if that makes sense). So I get the new email (that i want), but a repeat of the last email that was sent (not wanted). How do I get to only send the new email?

function checkPurpose()
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Mostrecent").getRange("C3");
  var purpose = sheet.getValue();
  Utilities.sleep(2000);
  
  if (purpose === "Agent Information Change"){
    var agentSheet = ss.getSheetByName("Agentinfo");
    var a = agentSheet.getLastRow();
    for (var b = 2; b < a + 1; b++ ) {
        var emailAddress = agentSheet.getRange(b,1).getValue();
        var subject = agentSheet.getRange(b,2).getValue();
        var message = agentSheet.getRange(b,3).getValue();
        MailApp.sendEmail(emailAddress, subject, message);
     }
  }
   else if (purpose === "Manager Information Change"){
     var managerSheet = ss.getSheetByName("Managerinfo");
     var x = managerSheet.getLastRow();
     for (var y = 2; y < x + 1 ; y++) {
       var emailAddress2 = managerSheet.getRange(y,1).getValue();
       var subject2 = managerSheet.getRange(y,2).getValue();
       var message2 = managerSheet.getRange(y,3).getValue();
       MailApp.sendEmail(emailAddress2, subject2, message2);
     }
  }

 else if (purpose === "Agent Onboard"){
    var onboardSheet = ss.getSheetByName("Agentonboard");  
    var n = onboardSheet.getLastRow();
    for (var i = 2; i < n + 1 ; i++ ) {
    var emailAddress3 = onboardSheet.getRange(i,4).getValue();
    var subject3 = onboardSheet.getRange(i,5).getValue();
    var message3 = onboardSheet.getRange(i,6).getValue();
    MailApp.sendEmail(emailAddress3, subject3, message3);
   }
 }
}

google-apps-script email javascript
1 Answers

I ran into this very same problem in the past, you can try this:

You may want to add an extra column to your sheet that will hold a string like: 'yes' that string will be added after an email is sent.

enter image description here

We will evaluate that every time an email is sent so emails are not repeated.

Using a global variable like:

var isMailSent = 'Yes';

Using the last else if in your code:

 var onboardSheet = ss.getSheetByName("Agentonboard");
    var n = onboardSheet.getLastRow();
    var startRow = 2;
    for (var i = 2; i < n + 1; i++) {

      if (isMailSent !== 'Yes' && n[1] !== "") { //if the row for colum mailSent does not contain 'YES' and it is not empty proceed with the following
        var emailAddress3 = onboardSheet.getRange(i, 4).getValue();
        var subject3 = onboardSheet.getRange(i, 5).getValue();
        var message3 = onboardSheet.getRange(i, 6).getValue();
        MailApp.sendEmail(emailAddress3, subject3, message3);
        sheet.getRange(startRow + i, 8).setValue(isMailSent);   //Here 8 is whe column number you added where isMailSent = 'Yes'; will be inserted
    }
    }
  }

Something to consider:

I see you are repeating a lot of code in the sample shared like:

 var emailAddress = agentSheet.getRange(b,1).getValue();
        var subject = agentSheet.getRange(b,2).getValue();
        var message = agentSheet.getRange(b,3).getValue();
        MailApp.sendEmail(emailAddress, subject, message);

You may want to include all of that in a function so you call the function instead of writing the entire code all over. If it helps here is a small representation of what I have (that one includes HTML so disregard that)

Disclaimer:

I'm not a pro at coding so if the above can be improved, I'm open to feedback.

user_103441960
Related