I'm new to scripts and not very familiar with code so I think this issue has to do with the StartRow and LastRow, but I'm not sure how to edit it to work as intended, pulling data from only one row at a time.
I have the below script, which I got from this question. The script sends an email when Column H = "Y" and I <> "Y".
The issue I'm experiencing is I want an email sent to notify me of each new user (1 new user = 1 new row) is added to the sheet, but many times multiple users/rows are added at one time, since they're added through a data connector, connecting Salesforce data to the sheet and the data connector refreshes the database every 24 hours.
Each individual email should contain Request, Account, Appointment, and User information from each individual row - this is what I want for each new row:
Request: TEST
Account: TEST
Appointment Created Date: Thu Oct 06 2022 00:00:00 GMT-0700 (Pacific Daylight Time)
User: TEST
However, since there are multiple new rows being added at a time, the first row's email will look good but the email for the 2nd row will have BOTH its own row information and the first row's - this is what I DO NOT want, but currently get:
Request: TEST
Account: TEST
Appointment Created Date: Fri Oct 07 2022 00:00:00 GMT-0700 (Pacific Daylight Time)
User: TEST
Request: TEST
Account: TEST
Appointment Created Date: Thu Oct 06 2022 00:00:00 GMT-0700 (Pacific Daylight Time)
User: TEST
The 3rd row's email will have information of all 3 rows in the body of the email. How do I limit each email to only contain the information relevant to the individual row?
Thanks in advance!
function email()
{
//setup function
var ActiveSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("First Time Users");
var StartRow = 2; //first row of data to process
var LastRow = ActiveSheet.getLastRow();
var RowRange = LastRow - StartRow 1;
var WholeRange = ActiveSheet.getRange(StartRow,1,RowRange,9);
var AllValues = WholeRange.getValues();
var message = ""
for (var i=0;i<AllValues.length;i )
{
var CurrentRow = AllValues[i];
var EmailSent = CurrentRow[8];
if (CurrentRow[7] == "Y" && EmailSent!= "Y")
{
message = //set HTML template for information
"<p><b>Request: </b>" CurrentRow[0] "</p>"
"<p><b>Account: </b>" CurrentRow[1] "</p>"
"<p><b>Appointment Created Date: </b>" CurrentRow[4] "</p>"
"<p><b>User: </b>" CurrentRow[5] "</p>";
var setRow = i StartRow;
var SendTo = "[email protected]";
var Subject = "First Time User Submitted Ask: " CurrentRow[1]; //set subject line
//send the actual email
MailApp.sendEmail
({
to: SendTo,
cc: "",
subject: Subject,
htmlBody: message,
});
ActiveSheet.getRange(setRow, 9).setValue("Y"); //update the row if email is sent
}
}
}
CodePudding user response：
Modification points:
- In your script,
var message = ""is declared the outside of the loop. And, the value is added usingmessage =. In this case, the value ofmessageis grown every loop. I thought that this might be the reason for your issue. - When
setValueis used in a loop, the process cost becomes high. Ref
In order to avoid this issue, how about the following modification?
Modified script:
function email() {
var ActiveSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("First Time Users");
var StartRow = 2;
var LastRow = ActiveSheet.getLastRow();
var RowRange = LastRow - StartRow 1;
var WholeRange = ActiveSheet.getRange(StartRow, 1, RowRange, 9);
var AllValues = WholeRange.getValues();
var ranges = [];
for (var i = 0; i < AllValues.length; i ) {
var CurrentRow = AllValues[i];
var EmailSent = CurrentRow[8];
if (CurrentRow[7] == "Y" && EmailSent != "Y") {
var message =
"<p><b>Request: </b>" CurrentRow[0] "</p>"
"<p><b>Account: </b>" CurrentRow[1] "</p>"
"<p><b>Appointment Created Date: </b>" CurrentRow[4] "</p>"
"<p><b>User: </b>" CurrentRow[5] "</p>";
var setRow = i StartRow;
var SendTo = "[email protected]";
var Subject = "First Time User Submitted Ask: " CurrentRow[1];
MailApp.sendEmail
({
to: SendTo,
cc: "",
subject: Subject,
htmlBody: message,
});
ranges.push("I" setRow);
}
}
ActiveSheet.getRangeList(ranges).setValue("Y");
}