I have bug where when I run my send email function. its sending multiple emails instead of just one email notification here is my code what am I doing wrong??!?! I got 31 of the same emails. I believe the issue the for loop is sending an email each time the if statement is true instead of just one time if its true help.
here is my code:
function sendEmail(){
var ss = SpreadsheetApp.getActiveSpreadsheet(); //get active spreadsheet only! to get the url for the filter view
var SpreadsheetID = ss.getSheetId(); // get the sheet Id
var spreadsheetURL = ss.getUrl(); // get the current active sheet url
var SpreadsheetID = spreadsheetURL.split("/")[5]; // using the last / for getting the last parts of the email
var filterViewName = 'PO_Log Precentage'; // Name of the filter view you want to get the url from & MAKE SURE Title matches view name account for "spaces" too
var filterViewID = filterId(SpreadsheetID, filterViewName); // Getting filter view id
var url = createURL(spreadsheetURL, filterViewID); // creating the url to send the filter view id
Logger.log(url);// Testing to see the correct url is created
var po_numID = ss.getSheetByName("Purchase Orders List").getRange("A2").getDisplayValue().substr(0,3);// Gets the Purchase Order List Sheet and the PO# the first 3 Characters of the PO in A2
Logger.log(po_numID);
var email_va = ss.getSheetByName("Purchase Orders List");
//gonna build statuses to look for into array
var statusesToEmail = ['On-going', '']
//"Status" is in Column T (Col 2)
//"Precent" is in Column Q (Col 3)
var data = email_va.getDataRange().getValues()
// //var headerRowNumber = 1; // When checking for emails in the sheet you want to exclude the header/title row
var emailDataSheet = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/17G0QohHxjuAcZzwRtQ6AUW3aMTEvLnmTPs_USGcwvDA/edit#gid=1242890521").getSheetByName("TestA"); // Get The URL from another spreadsheet based on URL
Logger.log(emailDataSheet.getSheetName());
var emailData = emailDataSheet.getRange("A2:A").getDisplayValues().flat().map(po => po.substr(0,3));
Logger.log(emailData)///Working to get the first 3 charcters in column A
var subject = po_numID " Po Log Daily Notification "; // Unique PoTitle of the email
var options = {} // Using the html body for the email
options.htmlBody = "Hi All, " "The following" '<a href=\"' url '" > Purchase Orders </a>' "are over 90% spent" "";
for(var i = 0; i < data.length; i ){
let row = data[i];
if( statusesToEmail.includes(row[1]) & (row[2] >= .80)){
emailData.every((po, index) => {
if (po == po_numID){
const email = emailDataSheet.getRange(index 2,7).getValue();//Getting the last colmun on the same row when the Po# are the same.
console.log(email);
MailApp.sendEmail(email, subject, '', options); // Sending the email which includes the url in options and sending it to the email address after making sure the first 3 Charcters Of the PO_log are the same as
return false;
} else {
return true;
}
});
}
}
}
here is the spreadsheet https://docs.google.com/spreadsheets/d/1QW5PIGzy_NSh4MT3j_7PggxXq4XcW4dCKr4wKqIAp0E/edit#gid=611584429
CodePudding user response:
you have to use the break function if u wish to stop the loop once the loop has been fulfiled, because if im not wrong , the email is sent if the IF condition is met , thus in the block that has mailapp.sendemail , you have to add in a break otherwise the loop will keep on happening. this is the basic of javascript and you should read up more about the FOR loop here
break as in just type "break" at the end of the code so the script will not continue to loop once the condition has been met.