Using spreadsheet values in html body of email with GmailApp.sendEmail

Question

I have a script that sends an email using GmailApp with an html body. I would like the html body to include specific values from the Spreadsheet that contains it, but each time the email is sent, the scriptlet I add is displayed as plain text.

email.gs:

function sendEmail() {



  var mailTo = 'example@gmail.com'


  var NAME = SpreadsheetApp.getActiveSheet().getRange('Sheet1!A1').getValue();


  var subject = 'Hello!'
  var html = HtmlService.createHtmlOutputFromFile('body').getContent();



  GmailApp.sendEmail('', subject, '', {


    name: 'Friend',
    htmlBody: html,
    bcc: mailTo


  });


}

body.html:

<h1>Good Evening, <? NAME ?> </h1>
<h3>Hello Friend!</h3>

"NAME" should be pulled from the specified cell in the sheet whenever the script is run.

I am guessing that the reason it is displaying as text is GmailApp.sendEmail pastes the raw html into the email and assumes the client will read it, so how could I first get it to process through the script and then paste into the email?

Possible duplicate of [Adding a property to an Html template gives error "Object does not allow properties to be added or changed"](https://stackoverflow.com/questions/49725049/adding-a-property-to-an-html-template-gives-error-object-does-not-allow-propert) — TheMaster, Jan 10 '19 at 22:05

score 7 · Accepted Answer · answered Jan 10 '19 at 17:54

Or you could use createTemplateFromFile and note "=" in

function sendEmail() {
  var mailTo = 'example@gmail.com'
  var subject = 'Hello!'
  var html = HtmlService.createTemplateFromFile('body');
  html.NAME = SpreadsheetApp.getActiveSheet().getRange('Sheet1!A1').getValue();
  html = html.evaluate().getContent();

  GmailApp.sendEmail('', subject, '', {
    name: 'Friend',
    htmlBody: html,
    bcc: mailTo
  });

}

<h1>Good Evening, <?= NAME ?> </h1>
<h3>Hello Friend!</h3>

This was it! createTemplate was the key! – user3117534 Jan 17 '19 at 23:13 — user3117534, Jan 17 '19 at 23:13

score 1 · Answer 2 · answered Jan 10 '19 at 16:00

1

You have the html body in a different file, and is not accessible by the NAME variable. Try this.

function sendEmail() {



  var mailTo = 'example@gmail.com'


  var NAME = SpreadsheetApp.getActiveSheet().getRange('Sheet1!A1').getValue();

var bodyText = "<h1>Good Evening,"+ NAME +" </h1><br><h3>Hello Friend!</h3>"

  var subject = 'Hello!'


  GmailApp.sendEmail('', subject, '', {


    name: 'Friend',
    htmlBody: bodyText,
    bcc: mailTo


  });


}

answered Jan 10 '19 at 16:00

Redskins and Tesla

113
1
2
14

Thanks! The thing is, I want to apply formatting to the email, including the variables, so it would be easier to just have it in a separate file. – user3117534 Jan 10 '19 at 16:37
You would have to figure out how the html file would be able to see those variables. I can't think of a way those variables would be visible outside of your sendEmail function in your code.gs with how you have the functions set up now. – Redskins and Tesla Jan 10 '19 at 16:44

Using spreadsheet values in html body of email with GmailApp.sendEmail

2 Answers2