Understanding and applying withsuccesshandler in google apps

Question

Background

I have this problem that I don't really understand how to fix. The desired behaviour is to access a google spreadsheet and send an HTML email from a template that contains three scriptlets for links to different forms based on the data in the spreadsheet. My call to evaluate the template is near the end of the script.

I'm using two rows of test data. When I read the execution logs, everything works out but the strange thing is that sometimes two emails are sent, sometimes one email is sent (when I'm expecting two) and its not always the email I expect. But the results are not the same every time. I think it is because when HtmlService.createTemplateFromFile('file').evaluate() is called, its calculating on the server but my code just keeps going and finishes before I get a response. Its just speculation. I've read google's HtmlService Page on communicating with servers and the references on google.script.run.

Code and Efforts

function sendForms () {
  var masterSS = SpreadsheetApp.openById("id");
  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='https://docs.google.com/forms/d/formid/viewform?usp=send_form'>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='https://docs.google.com/forms/d/formlink/viewform?usp=send_form'>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='https://docs.google.com/forms/d/formid/viewform?usp=send_form'>Workers Compensation</a> Verification Code: 13589C9</li>";
      masterSheet.getRange(startRow + i, 8).setValue("SENT");
  }    
      if (motorVehicleUsage != " " && workersCompForm != " " && affidavit != " ") {
      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.createTemplateFromFile('Initial Form Email Message');
      template.workersCompForm = workersCompForm;
      template.motorVehicleUsage = motorVehicleUsage;
      template.affidavit = affidavit;
      var html = template.evaluate().getContent();
      MailApp.sendEmail(emailAddress, subject, "Requires HTML", {htmlBody:html});
      }
    }
  }

And here is the section of my HTML file with the scriptlets.

<p>We would like you to complete the following additional...</p>
            <ul>
                <?!= workersCompForm ?><?!= motorVehicleUsage ?><?!= affidavit ?>
            </ul>

I originally had the if statement above the var html and had var html = template.evaluate().getContent(); at the bottom, just above MailApp. Thats when I had the inconsistent results.

The google.script.run.html() doesn't work at all, because I have no idea how to use it even after reading the documentation.

Question

I'm pretty sure I need a withSuccessHandler somewhere in this script, but I just cannot conceptualize where to put it or how to make to work. I tried to create the var html = function(); but had no luck in making it work. Should I use a withSuccessHandler and where would I put it? I just can't wrap my head around communicating with the server and working the timing.

Update

Added the HTML file that is part of the Apps Script project to this question, changed the code file to my original code without the google.script.run call per Sandy's advice that it doesn't run in the .gs file. I still have the same issue though with the seemingly random email that is being sent. Sometimes its the first for loop that gets the email sent, sometimes its the second for loop that gets the email sent, when I've set up the conditions on the spreadsheet to send to send emails for both loops.

I replaced the var template = HtmlService.createTemplateFromFile('Initial Form Email Message'); with var template = HtmlService.createHtmlOutputFromFile('Initial Form Email Message'); with no luck, so I tried var template = HtmlService.createHtmlOutputFromFile('Initial Form Email Message').getContent(); and with those attempts could not get any email sent. But the spreadsheet always updates with the correct values.

Answer 1

google.script.run is what's called a "Client Side API". "Client Side" means that it runs in the browser on the users computer. google.script.run is not meant to be used from .gs server code. So, you aren't going to get google.script.run to work from a script file with a .gs extension. google.script.run will run in an event attribute of HTML, like onclick() or in a <script> tag along with other JavaScript.

You can create HTML in the server code, and send it out somewhere. You can either do that with literal strings, which you have in your code, or you can have an .html file that the server evaluates, and then turns into content. But that isn't done with google.script.run, it's done with HTML Service.

var theHtml = HtmlService.createHtmlOutputFromFile(filename).getContent();