I am trying to have this code send emails faster and not hang up so much. Taking along time to end the script. Emails sent but still running 10mins later - How can I send the emails and end it quicker? That way if I need to send out more emails it is not continuing to run before runing the script again.
function sendEmails() {
//Current spreadsheet emails are sent from
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Search Index").activate();
var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var lr = ss.getLastRow();
var RowCountEmailPresent = 0
for (var i = 5;i<=lr;i ) {
var currentEmail = ss.getRange(i, 2).getValue();
if (currentEmail.includes('@')) {
RowCountEmailPresent = RowCountEmailPresent 1
}
}
var templateText = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Template").getRange(1, 1).getValue();
//How Many Sends We have left
var quotaLeft = MailApp.getRemainingDailyQuota();
if((RowCountEmailPresent) > quotaLeft){
Browser.msgBox("You have " quotaLeft "left and you're trying to send " (lr-1) " emails. Emails were not sent.");
}
else {
for (var i = 5;i<=lr;i ) {
//What values are being placed in the email
var currentEmail = ss.getRange(i, 2).getValue();
var ccmail = ss.getRange(i, 1).getValue();
var AlreadySent = ss.getRange(i, 3).getValue();
var currentStatus = ss.getRange(i, 7).getValue();
var currentOrdernumber = ss.getRange(i, 6).getValue();
var currentMon = ss.getRange(i, 6).getValue();
var code = ss.getRange(i, 13).getValue();
//Actual email being sent to Reps and TLs
var messageBody = templateText.replace("{Order Number}", currentMon).replace("{Jep Code}", code);
var subjectLine = "Offline Support - Order Status: " currentStatus " - Mon#: " currentOrdernumber;
if (currentEmail.includes('@')) {
if (AlreadySent < 1){
MailApp.sendEmail(currentEmail, subjectLine, messageBody, {cc: ccmail})
ss.getRange(i, 3).setValue('1');
}
}
} // close for loop
} //close else statment
SpreadsheetApp.getUi().alert("Congratulations, your email has been sent", SpreadsheetApp.getUi().ButtonSet.OK);
}
CodePudding user response:
See If this improves your efficiency.
var AlreadySent = ss.getRange(i, 3).getValue();
if (AlreadySent < 1){
var currentEmail = ss.getRange(i, 2).getValue();
if (currentEmail.includes('@')) {
var ccmail = ss.getRange(i, 1).getValue();
var currentStatus = ss.getRange(i, 7).getValue();
var currentOrdernumber = ss.getRange(i, 6).getValue();
var currentMon = ss.getRange(i, 6).getValue();
var code = ss.getRange(i, 13).getValue();
var messageBody = templateText.replace("{Order Number}", currentMon).replace("{Jep Code}", code);
var subjectLine = "Offline Support - Order Status: " currentStatus " - Mon#: " currentOrdernumber;
MailApp.sendEmail(currentEmail, subjectLine, messageBody, {cc: ccmail})
ss.getRange(i, 3).setValue('1');
}
}