Send HTML email from Google Sheets

In this tutorial, I'll show you how to send HTML email from Google Sheets using Apps Script. I'll also show you how to create the HTML email template using Gmail and Google Docs.

In a previous tutorial, I described how to build a Stock Watcher application to track the prices of stocks. The Stock Watcher app sends an email every morning with updated prices for the stocks that you are tracking. That email looks like this:

Plain text email with the prices of the following stocks: Microsoft, Apple, Tesla and Walmart.

This tutorial will teach you how to make that email look really nice by using HTML. Isn't the email below a lot better? The tabular structure and HTML formatting also make it easier to read the information in the email.

Prerequisites

In this tutorial, I assume that:

This tutorial is divided into two sections

  • Section 1: Create the HTML email template.

  • Section 2: Create an apps script to send HTML emails using the template.

Section 1: Create the HTML email template

In order to send HTML email, we first need to create an email template using HTML. I'll show you how to create one using Google Sheets, Docs & Gmail.

There are 3 steps

  • Use Gmail to create an email that looks like the one that you want to send using Apps Script.

  • Send this email to yourself and use Gmail to get the HTML code for this email.

  • Create an HTML template by using this code.

Use Gmail to create an email that looks like the one that you want to send using Apps Script

First, begin composing an email in Gmail. Then, copy paste the table containing stock information from the sheet into the email body.

Send this email to yourself and use Gmail to get the HTML code for this email

Send the email to yourself and confirm that it looks OK.

From the three-dot menu in Gmail, select Show original.

Click Copy to clipboard to copy the contents of the message and paste it into a Google Doc.

At the end of this step, you should have a Google Doc containing the detailed email message, including its text and HTML content.

Next, we need to delete all the unnecessary content from the doc so you only have the HTML code left.

Search for Content-Type: text/html; in the doc. You'll see the HTML code for the email right below where this text appears in the doc.

Delete everything else in the document except this HTML. I've highlighted the HTML code in the screenshots below to help you locate it in your document.

You also need to delete a line at the very end (after the HTML section ends).

When you're done, your document should only contain the encoded HTML code for the email that you sent yourself.

The next step is to decode the HTML using a quoted-printable decoder like the one here: https://www.webatic.com/quoted-printable-convertor.

  • Open the URL https://www.webatic.com/quoted-printable-convertor in your web browser and paste the encoded HTML into the "Encoded" box on the page.

  • Click the Decode button.

  • Copy the decoded HTML from the "Decoded" box and replace the encoded HTML in the Google Doc with this decoded HTML.

When you're done replacing the encoded HTML with the decoded HTML, your doc should look like this:

It can be hard to follow the structure of the decoded HTML so I recommend "prettifying" it using an HTML prettifier like the one at: https://www.browserling.com/tools/html-prettify. Once you prettify it, the HTML structure will be a lot easier to follow.

<div dir="ltr">
    <div>Hi there,</div>
    <div>
        <br>
    </div>
    <div>
        <table cellspacing="0" cellpadding="0" dir="ltr" border="1" style="table-layout:fixed;font-size:10pt;font-family:Arial;width:0px;border-collapse:collapse;border:none">
            <colgroup>
                <col width="130">
                    <col width="100">
                        <col width="100">
            </colgroup>
            <tbody>
                <tr style="height:29px">
                    <td style="overflow:hidden;padding:2px 3px;vertical-align:bottom;background-color:rgb(189,189,189);color:rgb(0,0,0);border:1px solid rgb(204,204,204)">Name</td>
                    <td style="overflow:hidden;padding:2px 3px;vertical-align:bottom;background-color:rgb(189,189,189);color:rgb(0,0,0);border:1px solid rgb(204,204,204)">Ticker</td>
                    <td style="overflow:hidden;padding:2px 3px;vertical-align:bottom;background-color:rgb(189,189,189);color:rgb(0,0,0);text-align:center;border:1px solid rgb(204,204,204)">Price</td>
                </tr>
                <tr style="height:29px">
                    <td style="overflow:hidden;padding:2px 3px;vertical-align:bottom;border:1px solid rgb(204,204,204)">Microsoft</td>
                    <td style="overflow:hidden;padding:2px 3px;vertical-align:bottom;border:1px solid rgb(204,204,204)">MSFT</td>
                    <td style="overflow:hidden;padding:2px 3px;vertical-align:bottom;text-align:center;border:1px solid rgb(204,204,204)">151.75</td>
                </tr>
                <tr style="height:29px">
                    <td style="overflow:hidden;padding:2px 3px;vertical-align:bottom;background-color:rgb(243,243,243);border:1px solid rgb(204,204,204)">Apple</td>
                    <td style="overflow:hidden;padding:2px 3px;vertical-align:bottom;background-color:rgb(243,243,243);border:1px solid rgb(204,204,204)">AAPL</td>
                    <td style="overflow:hidden;padding:2px 3px;vertical-align:bottom;background-color:rgb(243,243,243);text-align:center;border:1px solid rgb(204,204,204)">270.71</td>
                </tr>
                <tr style="height:29px">
                    <td style="overflow:hidden;padding:2px 3px;vertical-align:bottom;border:1px solid rgb(204,204,204)">Tesla</td>
                    <td style="overflow:hidden;padding:2px 3px;vertical-align:bottom;border:1px solid rgb(204,204,204)">TSLA</td>
                    <td style="overflow:hidden;padding:2px 3px;vertical-align:bottom;text-align:center;border:1px solid rgb(204,204,204)">335.89</td>
                </tr>
                <tr style="height:29px">
                    <td style="overflow:hidden;padding:2px 3px;vertical-align:bottom;background-color:rgb(243,243,243);border:1px solid rgb(204,204,204)">Walmart</td>
                    <td style="overflow:hidden;padding:2px 3px;vertical-align:bottom;background-color:rgb(243,243,243);border:1px solid rgb(204,204,204)">WMT</td>
                    <td style="overflow:hidden;padding:2px 3px;vertical-align:bottom;background-color:rgb(243,243,243);text-align:center;border:1px solid rgb(204,204,204)">119.78</td>
                </tr>
                <tr style="height:29px">
                    <td style="overflow:hidden;padding:2px 3px;vertical-align:bottom;border:1px solid rgb(204,204,204)">McDonald&#39;s</td>
                    <td style="overflow:hidden;padding:2px 3px;vertical-align:bottom;border:1px solid rgb(204,204,204)">MCD</td>
                    <td style="overflow:hidden;padding:2px 3px;vertical-align:bottom;text-align:center;border:1px solid rgb(204,204,204)">195.35</td>
                </tr>
            </tbody>
        </table>
    </div>
</div>

That's it. You now have the HTML code you need to create the template.

Create an HTML template using the HTML code

Create a new HTML file called Template.html in the Apps Script editor. Select File → New → HTML file.

Enter Template.html as the file name.

An HTML file will be created with some default content populated in it.

Replace the default content with the HTML code from the previous step and save the file.

You're done creating the HTML template. The next step is to use Apps Script to send HTML emails using the template that you just created.

Section 2: Use Apps Script send HTML emails using the template

Instead of starting from scratch, we will modify the script from the previous tutorial. Here is the script that we will modify (please see the other tutorial for an explanation of what this code does):

function sendEmail() {
  var stockData = getData();
  var body = getEmailText(stockData);
  
  MailApp.sendEmail({
    to: "youremail@example.com",
    subject: "Stock update",
    body: body
  });
}

function getEmailText(stockData) {
  var text = "";
  stockData.forEach(function(stock) {
    text = text + stock.name + "\n" + stock.ticker + "\n" + stock.price + "\n-----------------------\n\n";
  });
  return text;
}

/**
 * @OnlyCurrentDoc
 */
function getData() {
  var values = SpreadsheetApp.getActive().getSheetByName("Data").getRange("Stocks").getValues();
  values.shift(); //remove headers
  var stocks = [];
  values.forEach(function(value) {
    var stock = {};
    stock.name = value[0];
    stock.ticker = value[1];
    stock.price = value[2];
    stocks.push(stock);
  })
  //Logger.log(JSON.stringify(stocks));
  return stocks;
}

The above script sends out emails that look like this:

Sending out an HTML email is pretty easy.

First, create a new function called getEmailHtml(stockData) that takes stock prices as input and returns the HTML body to use in the email message.

function getEmailHtml(stockData) {
  var htmlTemplate = HtmlService.createTemplateFromFile("Template.html");
  var htmlBody = htmlTemplate.evaluate().getContent();
  return htmlBody;
}

Then, we'll use this HTML content to send out the HTML email.

function sendEmail() {
  var stockData = getData();
  var body = getEmailText(stockData);
  var htmlBody = getEmailHtml(stockData);
  
  MailApp.sendEmail({
    to: "youremail@example.com",
    subject: "Stock update",
    body: body,
    htmlBody: htmlBody
  });
}

When you run the sendEmail() function, you should receive an HTML email like the one below.

Are we done? Not yet! There is just one other thing you need to do to complete this tutorial. The above code does not use the stock price info at all. That is, the function getEmailHtml(stockData) does not use stockData anywhere.

We need the table in the HTML email to use the stock price information coming from the sheet. To do this, we need to make the variable stockData available to the template. That will enable us to make the template dynamic by using this data.

The statement htmlTemplate.stocks = stockData; makes the variable stockData available for use within the template through the property stocks. This property can be used like a variable within scriptlets in the Template.html file.

function getEmailHtml(stockData) {
  var htmlTemplate = HtmlService.createTemplateFromFile("Template.html");
  htmlTemplate.stocks = stockData;
  var htmlBody = htmlTemplate.evaluate().getContent();
  return htmlBody;
}

The next step is to populate the table in the HTML email by using the contents of the stocks variable that we made available to the template.

First, delete the HTML code for all the rows in the table other than the header row and the first row. The resulting HTML template should look like this:

<div dir="ltr">
    <div>Hi there,</div>
    <div>
        <br>
    </div>
    <div>
        <table cellspacing="0" cellpadding="0" dir="ltr" border="1" style="table-layout:fixed;font-size:10pt;font-family:Arial;width:0px;border-collapse:collapse;border:none">
            <colgroup>
                <col width="130">
                    <col width="100">
                        <col width="100">
            </colgroup>
            <tbody>
                <tr style="height:29px">
                    <td style="overflow:hidden;padding:2px 3px;vertical-align:bottom;background-color:rgb(189,189,189);color:rgb(0,0,0);border:1px solid rgb(204,204,204)">Name</td>
                    <td style="overflow:hidden;padding:2px 3px;vertical-align:bottom;background-color:rgb(189,189,189);color:rgb(0,0,0);border:1px solid rgb(204,204,204)">Ticker</td>
                    <td style="overflow:hidden;padding:2px 3px;vertical-align:bottom;background-color:rgb(189,189,189);color:rgb(0,0,0);text-align:center;border:1px solid rgb(204,204,204)">Price</td>
                </tr>
                <tr style="height:29px">
                    <td style="overflow:hidden;padding:2px 3px;vertical-align:bottom;border:1px solid rgb(204,204,204)">Microsoft</td>
                    <td style="overflow:hidden;padding:2px 3px;vertical-align:bottom;border:1px solid rgb(204,204,204)">MSFT</td>
                    <td style="overflow:hidden;padding:2px 3px;vertical-align:bottom;text-align:center;border:1px solid rgb(204,204,204)">151.75</td>
                </tr>
            </tbody>
        </table>
    </div>
</div>

When you send an email using this template, the email you receive will only have one row.

Next, we will use a for loop to dynamically create rows in the HTML table using the information in the stocks variable.

The characters <? and ?> tell Apps Script that the text in between them should be evaluated as code. They're called a standard scriptlet.

<? for(var i = 0; i < stocks.length; i++) { ?>
  <tr style="height:29px">
    <td style="overflow:hidden;padding:2px 3px;vertical-align:bottom;border:1px solid rgb(204,204,204)">Microsoft</td>
    <td style="overflow:hidden;padding:2px 3px;vertical-align:bottom;border:1px solid rgb(204,204,204)">MSFT</td>
    <td style="overflow:hidden;padding:2px 3px;vertical-align:bottom;text-align:center;border:1px solid rgb(204,204,204)">151.75</td>
  </tr>
<? } ?>

At this time, the contents of your Template.html file should look like this:

<div dir="ltr">
    <div>Hi there,</div>
    <div>
        <br>
    </div>
    <div>
        <table cellspacing="0" cellpadding="0" dir="ltr" border="1" style="table-layout:fixed;font-size:10pt;font-family:Arial;width:0px;border-collapse:collapse;border:none">
            <colgroup>
                <col width="130">
                    <col width="100">
                        <col width="100">
            </colgroup>
            <tbody>
                <tr style="height:29px">
                    <td style="overflow:hidden;padding:2px 3px;vertical-align:bottom;background-color:rgb(189,189,189);color:rgb(0,0,0);border:1px solid rgb(204,204,204)">Name</td>
                    <td style="overflow:hidden;padding:2px 3px;vertical-align:bottom;background-color:rgb(189,189,189);color:rgb(0,0,0);border:1px solid rgb(204,204,204)">Ticker</td>
                    <td style="overflow:hidden;padding:2px 3px;vertical-align:bottom;background-color:rgb(189,189,189);color:rgb(0,0,0);text-align:center;border:1px solid rgb(204,204,204)">Price</td>
                </tr>
                <? for(var i = 0; i < stocks.length; i++) { ?>
                <tr style="height:29px">
                    <td style="overflow:hidden;padding:2px 3px;vertical-align:bottom;border:1px solid rgb(204,204,204)">Microsoft</td>
                    <td style="overflow:hidden;padding:2px 3px;vertical-align:bottom;border:1px solid rgb(204,204,204)">MSFT</td>
                    <td style="overflow:hidden;padding:2px 3px;vertical-align:bottom;text-align:center;border:1px solid rgb(204,204,204)">151.75</td>
                </tr>
                <? } ?>
            </tbody>
        </table>
    </div>
</div>

If you send an email by running the sendEmail() function, the email you receive will have the correct number of rows (based on your spreadsheet) but each row will have the same data.

This is because the number of rows in the table are dynamic but the actual contents of each row are still static. To fix this, replace:

  • "Microsoft" with <?= stocks[i].name ?>

  • "MSFT" with <?= stocks[i].ticker ?>

  • "151.75" with <?= stocks[i].price ?>

The final Template.html file should look like this:

<div dir="ltr">
    <div>Hi there,</div>
    <div>
        <br>
    </div>
    <div>
        <table cellspacing="0" cellpadding="0" dir="ltr" border="1" style="table-layout:fixed;font-size:10pt;font-family:Arial;width:0px;border-collapse:collapse;border:none">
            <colgroup>
                <col width="130">
                    <col width="100">
                        <col width="100">
            </colgroup>
            <tbody>
                <tr style="height:29px">
                    <td style="overflow:hidden;padding:2px 3px;vertical-align:bottom;background-color:rgb(189,189,189);color:rgb(0,0,0);border:1px solid rgb(204,204,204)">Name</td>
                    <td style="overflow:hidden;padding:2px 3px;vertical-align:bottom;background-color:rgb(189,189,189);color:rgb(0,0,0);border:1px solid rgb(204,204,204)">Ticker</td>
                    <td style="overflow:hidden;padding:2px 3px;vertical-align:bottom;background-color:rgb(189,189,189);color:rgb(0,0,0);text-align:center;border:1px solid rgb(204,204,204)">Price</td>
                </tr>
                <? for(var i = 0; i < stocks.length; i++) { ?>
                <tr style="height:29px">
                    <td style="overflow:hidden;padding:2px 3px;vertical-align:bottom;border:1px solid rgb(204,204,204)"><?= stocks[i].name ?></td>
                    <td style="overflow:hidden;padding:2px 3px;vertical-align:bottom;border:1px solid rgb(204,204,204)"><?= stocks[i].ticker ?></td>
                    <td style="overflow:hidden;padding:2px 3px;vertical-align:bottom;text-align:center;border:1px solid rgb(204,204,204)"><?= stocks[i].price ?></td>
                </tr>
                <? } ?>
            </tbody>
        </table>
    </div>
</div>

When you run the sendEmail() function, you'll see a nicely formatted HTML email that contains the data from your Google Sheet.

Whew! This ended up being a super long tutorial but hopefully you found it to be useful.

Summary

In this tutorial, I explained how to send HTML emails using Google Sheets and Apps Script. I also showed you how to create an HTML email template using Google Docs and Gmail.

Thanks for reading!


Have feedback for me?

I'd appreciate any feedback you can give me regarding this post.

Was it useful? Are there any errors or was something confusing? Would you like me to write a post about a related topic? Any other feedback is also welcome. Thank you so much!