Push Variables Into HTML Using Google Script

Question

I would like to use Google Script variables into a "mail_template" HTML document. I do not understand why my variables can't be used into this HTML document, while I can use them into the google script itself.

I have 3 documents:

A google script ( .gs ) linked to a Google sheet ( Let's call it "X" )
An html document linked to the above "X" Google sheet
And the "X" *Google sheet itself

The purpose of this work is to send designed email using Google sheet data.

I am beginner, and the documentation seems to be for more advanced users. https://developers.google.com/apps-script/guides/html/templates

Some plugins are doing the same work, but they are not authorized into my workspace. It's very difficult, I am trying to solve that since 3 days, but I still can't.

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <? solution1 ?>
  
  </body>
 </html>

// This constant is written in column O for rows for which an email
// has been sent successfully.
var EMAIL_SENT = "EMAIL_SENT";

function testSchemas() { {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = sheet.getLastRow();  
  var numRows = 1;  
  var dataRange = sheet.getRange(startRow, 1, numRows, 15)
  var data = dataRange.getValues();
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var name = row[2]; 
    var surname = row[3]; 
    var salesRepEmail = row[4]; 
    var qualityAnalystEmail = "[email protected]"
    var customerEmail = row[5]; 
    var websiteURL = row[6]; 
    var solution1 = row[7];
    function doGet() {
  return HtmlService
      .createTemplateFromFile('mail_template')
      .evaluate();
}

    
    
    var htmlBody = HtmlService.createHtmlOutputFromFile('mail_template').getContent(); 
    var emailSent = row[14];     // Third column
    if (emailSent != "EMAIL_SENT") {  // Prevents sending duplicates
     /*MailApp.sendEmail(customerEmail, subject, message, {
        cc: "",
        bcc: qualityAnalystEmail + ", " + salesRepEmail
      }); */
    MailApp.sendEmail({
    to: customerEmail,
    bcc: qualityAnalystEmail + ", " + salesRepEmail,
    subject: 'Résumé De Notre Consultation Du Site Mobile ' + websiteURL,
    htmlBody: htmlBody,
  }); 
}
      Logger.log(name);
      sheet.getRange(startRow + i, 15).setValue(EMAIL_SENT);
      // Make sure the cell is updated right away in case the script is interrupted
      SpreadsheetApp.flush();
    }
  }
}

Anton Dementiev · Accepted Answer · 2017-07-21 23:35:05Z

3

If you'd like to pass variables from .gs file to your HTML template, try this before calling evaluate() on the template object:

var template = HtmlService.createTemplateFromFile('mail_template');
template.myVar = myVar;
return template.evaluate();

Calling evaluate() executes the JS scriptlets (inline code wrapped in <? ?>). To make the variable accessible to the template, simply attach it as object property. Finally, the correct notation for printing your variable to the page is

<?= myVar ?>

answered Jul 21, 2017 at 23:35

Anton Dementiev

5,7344 gold badges23 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Marvin JERNIDIER Over a year ago

Hi Anton, thanks for your help. it's still not working, that's may be something stupid I did. I try this code into the "doGet function, to replace the existant one.

Anton Dementiev Over a year ago

Sorry, Marvin, maybe it's just the formatting, but looking at your code, are you actually trying to call the doGet() function inside another function? If yes, it will not work this way. Your script needs to be deployed as a web app first. When you access the published web app by opening it's URL, the app receives the incoming GET request and executed the doGet() function

Mina · Accepted Answer · 2020-05-31 12:33:27Z

1

I had a similar question. Anton's solution got me most of the way, but I had to add .getContent() after calling evaluate() and assign the evaluated template to a variable.

var template = HtmlService.createTemplateFromFile('mail_template');
template.myVar = myVar;
var emailToSend = officeTempl.evaluate().getContent();

htmlBody: emailToSend

https://developers.google.com/apps-script/reference/html/html-template#evaluate

answered May 31, 2020 at 12:33

Mina

2507 silver badges16 bronze badges

Collectives™ on Stack Overflow

Push Variables Into HTML Using Google Script

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related