Goal
Sending an HTML email based on whether a person has already been sent an email, and conditionally adding certain links to this email based on the content of a cell in the applicable row on the google spreadsheet.
Background
I'm using a standalone script (because this is just one function in it which can actually stand alone on itself) that executes and most times will send an email. I set up my tests so that it the conditions would be met for sending two emails, however, most times only one is sent. I initially thought this was an issue with needing a success handler because I call out a createTemplateFromFile() method and evaluate it three lines later, then send the email. Its part of a for loop, so I expected two emails. I have three rows of test data on my spreadsheet, but the first row is a header row.
Code
Code.gs
function sendForms () {
/*this function will determine which people are new entries and then send them an email with a link to the form.
the email should include a short code which will be used as a verification*/
var masterSS = SpreadsheetApp.openById("1GxM-NGk4kLOwaxFygJMfvafiL0lJmOCbCNsbmDmfJbU");
var masterSheet = masterSS.getSheets()[0];
var masterRange = masterSheet.getDataRange();
var masterData = masterRange.getValues();
var startRow = 1;
for (var i = 1; i < masterData.length; i++) {
var row = masterData[i];
var emailAddress = row[2];
var routineContact = row[5];
var requireDrive = row[6];
var compCheck = row[7];
var affidavit = " ";
if (routineContact != "SENT" && routineContact != "RECEIVED" && routineContact != "No") {
var affidavit = "<li>Form: <a href='www.duckduckgo.com'>Affidavit of Compliance with PA Background Checks</a> Verification Code: 169827C9</li>";
masterSheet.getRange(startRow + i, 6).setValue("SENT");
}
var motorVehicleUsage = " ";
if (requireDrive != "SENT" && requireDrive != "RECEIVED" && requireDrive != "No") {
var motorVehicleUsage = "<li>Form: <a href='www.yahoo.com'>Motor Vehicle Agreement</a> Verification Code: 13444C9</li>";
masterSheet.getRange(startRow + i, 7).setValue("SENT");
}
var workersCompForm = " ";
if (compCheck != "SENT" && compCheck != "RECEIVED") {
var workersCompForm = "<li>Form: <a href='www.google.com'>Workers Compensation</a> Verification Code: 13589C9</li>";
masterSheet.getRange(startRow + i, 8).setValue("SENT");
}
if (motorVehicleUsage != " " && affidavit != " ") {
//var html = function() {
var sendingDate = new Date();
var formatSendingDate = Utilities.formatDate(sendingDate, "GMT-05:00", 'DD');
masterSheet.getRange(startRow + i, 10).setValue(formatSendingDate);
var subject = "Additional Onboarding Forms";
var template = HtmlService.createHtmlOutputFromFile('Initial Form Email Message').getContent();
template.workersCompForm = workersCompForm;
template.motorVehicleUsage = motorVehicleUsage;
template.affidavit = affidavit;
var html = template.evaluate().getContent();//original
MailApp.sendEmail(emailAddress, subject, "Requires HTML", {htmlBody:html});
}
}
}
html file snippet
<ul>
<?!= workersCompForm ?><?!= motorVehicleUsage ?><?!= affidavit ?>
</ul>
Research and Solution?
I think it may be an issue of recursion, based on what I read from a similar post about passing a variable into an HTML File, but it didn't quite answer my issue. I read two additional sources about recursion (iteration and recursion, and Java and recursion), but I'm not intentionally trying to create a recursive function, at least I don't think I am because I really barely understand it.
The execution logs show the values for row two and row three being updated with the setValues() method, which is the behavior I wanted, but then most times show just one instance of the email being sent. The email is evaluated correctly for the recipient, but only one email is being sent and I'm looking for one per row in this instance.
I don't really know how to fix it though. I was thinking that I need to put the sending of the email into a seperate function and pass parameters into it. If I did this, I'm not really sure what the parameters would be, since I thought using 'return' would end the function.
My other thought was if I had to make a function for each of the three variables I wanted to print on the HTML file, but put them directly on the HTML file.
I just really want to understand what is going on with my code, and also I want to fix it, but understand why a fix would work.
I've added viewable copies of my spreadsheet and script. I was using my actual email address though, from the spreadsheet. But these were the conditions I was testing which only gave me one email.
