Populate HTML body with google sheet fields using MailApp.sendEmail script

Question

I'm trying to use an HTML template to send custom emails using data collected on a google sheet. I've linked to code to a button so that I select the row/rows I want to email, click the button and it sends to the recipient in that row. I also want the body of the email to contain data from the row. I am using an html body script. I tried to use the same email[column #] to call data from the sheet in the html script but to no avail. How can I get fields from my sheet to fill the html script in order to send a custom email?

This is the code I'm currently using:

function email() {
  var htmlBody = HtmlService.createHtmlOutputFromFile('mail_template').getContent();
  var rng = SpreadsheetApp.getActiveSheet().getActiveRange()
  var email = rng.getValues()[0];
  MailApp.sendEmail({
    to: email[23],
    subject: 'Show Seats ' + email[1],
    htmlBody: htmlBody,
    replyTo:'[email protected]',
  });
}

and I have a separate html file also saved in the script. I prefer to keep it in a separate file because it is cleaner for editing.

    <html>
  <head>
  <script>
  </script>
  </head>
  <body>
      <p>Hello,</p>
      <p>&nbsp;</p>
      <p>You are CONFIRMED!</p>
      <p>No. of Tickets: email[10]</p>
      <p>Date/Time:email[2]</p>
      <p>CC Charge: $email[14]</p>
      <p>Held Under:email[1]</p>
      <p>&nbsp;</p>
      <p>Tickets will be held at the  Box Office. If you are picking up your tickets in advance, they will be available 48 hours before your selected performance. Please note there is no late seating.</p>
      <p>&nbsp;</p>
      <p>Thanks!</p>
<div><p><a href="http://www.gap.com/" style="font-family: Tahoma; font-size: 13px;"><img src="https://image.ibb.co/d0aDt6/DEH_Signature_Book.png" /></a></p></div>

  </body>
</html>

random-parts random-parts · Accepted Answer · 2017-11-10T00:29:16

Take a look at using Scriptlets with evaluate()

function email() {
  var htmlBody = HtmlService.createTemplateFromFile('mail_template');  
  var rng = SpreadsheetApp.getActiveSheet().getActiveRange();
  var email = rng.getValues()[0];

  // set the values for the placeholders
  htmlBody.tickets = email[10];
  htmlBody.datetime = email[2];
  htmlBody.cc = email[14];
  htmlBody.held_under = email[1];

  // evaluate and get the html
  var email_html = htmlBody.evaluate().getContent();

  MailApp.sendEmail({
    to: email[23],
    subject: 'Show Seats ' + email[1],
    htmlBody: email_html,
    replyTo:'[email protected]',
  });
}

In the HTML Template, add the placeholders/printing scriptlets :

<html>
  <body>
    <p>Hello,</p>
    <p>&nbsp;</p>
    <p>You are CONFIRMED!</p>
    <p>No. of Tickets: <?= tickets ?></p>
    <p>Date/Time:<?= datetime ?></p>
    <p>CC Charge: <?= cc ?></p>
    <p>Held Under:<?= held_under ?></p>
    <p>&nbsp;</p>
    <p>Tickets will be held at the  Box Office. If you are picking up your tickets in advance, they will be available 48 hours before your selected performance. Please note there is no late seating.</p>
    <p>&nbsp;</p>
    <p>Thanks!</p>
    <div><p><a href="http://www.gap.com/" style="font-family: Tahoma; font-size: 13px;"><img src="https://image.ibb.co/d0aDt6/DEH_Signature_Book.png" /></a></p></div>
  </body>
</html>

Populate HTML body with google sheet fields using MailApp.sendEmail script

1 Answers