Styling Gmail html tables

Apps Script (intermediate level) posted on 23rd May 2018


Styling and html templates

When you use Gmail (or Sites), you are allowed to provide HTML input so you can have control over the content. So if you wanted to send an email with red text, you could do this.
  const htmlBody = '<p style="color:red">hello</p>';
  GmailApp.sendEmail("bruce@mcpher.com", "test styling" , "view on html device", {
    htmlBody: htmlBody
  });

And you'd get this
Normally of course when you are styling markup, you'd use CSS with your styles inside <STYLE> tags, or you might use Classes, all of which makes styling a lot less verbose and easier to change. However if you are using a templating system where your HTML will be wrapped inside a parent, as with GMAIL, <STYLE> tags get stripped out. 

A common thing to do is to send an email of a table. Here's how I  create and style tables from an array of objects.

Mail format

This is the styled table received in the mail

And it's created using a standard set of styles and fairly straightforward function. 
  GmailApp.sendEmail("bruce@mcpher.com", "test styling" , "view on html device", {
    htmlBody: makeHtmlTable (obs, styles)
  });

Source data

  const obs = [{
    "name": "Port Moresby Jacksons International Airport",
    "latitude_deg": -9.44338035583496,
    "longitude_deg": 147.220001220703,
    "elevation_ft": 146,
    "iso_country": "PG",
    "municipality": "Port Moresby",
    "iata_code": "POM"
  },{
    "name": "Edmonton International Airport",
    "latitude_deg": 53.30970001,
    "longitude_deg": -113.5800018,
    "elevation_ft": 2373,
    "iso_country": "CA",
    "municipality": "Edmonton",
    "iata_code": "YEG"
  },{
    "name": "Ottawa Macdonald-Cartier International Airport",
    "latitude_deg": 45.32249832,
    "longitude_deg": -75.66919708,
    "elevation_ft": 374,
    "iso_country": "CA",
    "municipality": "Ottawa",
    "iata_code": "YOW"
  }];

Table Style settings

const styles= {
    table: {
      "border": "1px solid #BDBDBD",
      "background-color": "#FFC107",
      "width": "100%",
      "text-align": "left",
      "border-collapse": "collapse"
    },
    td: {
      "border": "1px solid #BDBDBD",
      "padding": "2px 2px",
      "color" : "#212121"
    },
    th: {
      "border": "1px solid #BDBDBD",
      "padding": "2px 2px",
      "font-size": "11px",
      "font-weight": "bold",
      "color": "#212121",
      "border-left": "2px solid #BDBDBD"
    },
    tr: {
    },
    trEven: {
      "background": "#FFECB3"
    },
    tbody: {
      "font-size": "13px"
    },
    thead: {
      "background": "#FFA000",
      "border-bottom": "1px solid #BDBDBD"
    },
    thFirst: {
      "border-left": "none"
    }
  };

Function to make html table

 // make an html table from an array
  const makeHtmlTable = function (obs, styles) {

    // common style maker
    const makeStyle = function (ob) {
      return ob ? Object.keys(ob).map(function (k) { return k + ":" + ob[k] + ";" }).join("") : "";
    }
    // expects something like <th> and returns <th style="...">
    const insertStyle = function (style, tag) {
      return style ? tag.slice (0,-1) + (' style="' + style + '">') : tag;
    }
    // table headings
    var ths = obs.length ? 
      Object.keys(obs[0])
        .map (function (h,i) { 
          // apply th styles
          var style = makeStyle(styles.th);
          if (!i) style += makeStyle (style.thFirst);
          return insertStyle(style,'<th>')+h+'</th>';
        })
        .join("") : "";
      
    // table rows
    var trs = obs.length ? 
      obs.map (function (d,j) {
        var style = makeStyle(styles.tr);
        if (!(j % 2)) style += makeStyle (styles.trEven);
        return insertStyle(style,'<tr>') + Object.keys(obs[0])
          .map (function (h,i) {
            var style = makeStyle(styles.td);
            return insertStyle(style,'<td>')+d[h]+'</td>';
          }).join("") + '</tr>';  
      }).join("\n") : "";
    
    // construct
    const htmlBody = insertStyle(makeStyle(styles.table),'<table>') + 
      insertStyle (makeStyle(styles.thead),"<thead>") + ths + "</thead>" +
      insertStyle (makeStyle(styles.tbody),"<tbody>") + trs + "</tbody>" +
      "</table>";
   
    return htmlBody;
      
  };

Data straight from a sheet

Of course you may be taking the data straight from a sheet - the fiddler (A functional approach to fiddling with sheet data) is perfect for this. In fact it can be done in just a few lines of code.

  // get the sheet
  const sheet = SpreadsheetApp.openById('15MDlPLVH4IhnY2KJBWYGANoyyoUFaxeWVDOe-pupKxs').getSheetByName('large airports');
  
  // objectify and filter
  const data = new cUseful.Fiddler(sheet)
    .filterRows (function (row) {
      return row.elevation_ft > 5000;
    }).getData();
   
  // format and send the mail
  GmailApp.sendEmail("bruce@mcpher.com", "Here's a table of airports higher than 5000 feet" , "view on html device", {
    htmlBody: makeHtmlTable (data,styles)
  });

and the result


You want to learn Google Apps Script?

Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my my book, Going Gas - from VBA to Apps script, All formats are available from O'ReillyAmazon and all good bookshops. You can also read a preview on O'Reilly

If you prefer Video style learning I also have two courses available. also published by O'Reilly.
Google Apps Script for Developers and Google Apps Script for Beginners.

Comments