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
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
More on this topic available here
- A functional approach to fiddling with sheet data
- Unique values with data fiddler
- More sheet data fiddling
- Fiddling with text fields that look like dates
- A functional approach to updating master sheet
- Populating sheets with API data using a Fiddler
- Header formatting with fiddler
- Formatting sheet column data with fiddler
- Styling Gmail html tables
- Sorting Google Sheet DisplayValues