0
312
ViewsGoogle Aps Script: script to send auto-emails on new form response sends both new and previous emails
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);
}
}
}1 answers
Answer question0
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.
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.