Emailing Google Sheet range (with or without formatting) as a HTML table in a Gmail message

Question

So I have a Google Spreadsheet and want to create automated daily reports by emailing a portion of the range from my "Daily report" sheet as a HTML table using Google Spreadsheet triggers.

I have some conditional formatting in the sheet to get it to paint all cells in column that have MAX(value) in said column.

I've managed to create the code below which emails me the range but Gmail doesn't recognized it as a HTML table but plain text.

I've tried using {htmlBody: htmltable} in the MailApp.sendEmail function but Gmail just errors out ([Ljava.lang.Object;@SOME_HASH.

Question 1: How can I email the table as HTML and not plain text with all the HTML tags visible?

Question 2: How could I improve my code to get Google Sheets cell formatting and apply it to the table cells instead of using ad hoc formatting just to get the table to look OK?

function sendMail(){
 var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
 var data = sh.getRange("A2:O38").getValues();
  //var htmltable =[];

var TABLEFORMAT = 'cellspacing="2" cellpadding="2" dir="ltr" border="1" style="width:100%;table-layout:fixed;font-size:10pt;font-family:arial,sans,sans-serif;border-collapse:collapse;border:1px solid #ccc;font-weight:normal;color:black;background-color:white;text-align:center;text-decoration:none;font-style:normal;'
var htmltable = ['<table ' + TABLEFORMAT +' ">'];

for (row = 0; row<data.length; row++){

htmltable.push ('<tr>');

for (col = 0 ;col<data[row].length; col++){
  if (data[row][col] === "" || 0) {htmltable.push('<td>' + 'None' + '</td>');} 
  else
    if (row === 0)  {
      htmltable.push ('<th>' + data[row][col] + '</th>');
    }

  else {htmltable.push('<td>' + data[row][col] + '</td>');}
}

     htmltable.push('</tr>');
}

     htmltable.push ('</table>');
     Logger.log(data);
MailApp.sendEmail(Session.getActiveUser().getEmail(), 'Daily report','' ,{htmlBody: htmltable})
}

Gerneio Gerneio · Accepted Answer · 2016-04-10T15:15:55

For question 1, it should be as simple as not using an array to hold each line of your HTML table. Just concatenate it into a string and send it on through and should work just fine.

As far as question 2, I'd assume that you'd have to check certain conditions of the cells to determine how to format the table. I don't know if there's a sure straightforward way to copy all formatting.

Here's an idea though. It's possible to publish a Google sheet as HTML (look under the file tab). Maybe there's a way to pull in the HTML file via url, then parse to what you need. I just have no idea if it'll carry over any cell formatting. Worth looking into though.

Edit (concatenation):

Also added a Logger.log so that you can see how the final htmltable String object comes out. Perhaps copy that value into a typical index.html page and see how or if it loads properly.

function sendMail(){
 var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
 var data = sh.getRange("A2:O38").getValues();
  //var htmltable =[];

var TABLEFORMAT = 'cellspacing="2" cellpadding="2" dir="ltr" border="1" style="width:100%;table-layout:fixed;font-size:10pt;font-family:arial,sans,sans-serif;border-collapse:collapse;border:1px solid #ccc;font-weight:normal;color:black;background-color:white;text-align:center;text-decoration:none;font-style:normal;'
var htmltable = '<table ' + TABLEFORMAT +' ">';

for (row = 0; row<data.length; row++){

htmltable += '<tr>';

for (col = 0 ;col<data[row].length; col++){
  if (data[row][col] === "" || 0) {htmltable += '<td>' + 'None' + '</td>';} 
  else
    if (row === 0)  {
      htmltable += '<th>' + data[row][col] + '</th>';
    }

  else {htmltable += '<td>' + data[row][col] + '</td>';}
}

     htmltable += '</tr>';
}

     htmltable += '</table>';
     Logger.log(data);
     Logger.log(htmltable);
MailApp.sendEmail(Session.getActiveUser().getEmail(), 'Daily report','' ,{htmlBody: htmltable})
}

Edit (tested and working, see screenshots):

Update (solution for question 2):

After checking out the library SheetConverter from here pointed out by your comment below, I was able to send a perfectly formatted email that matched my sheet exactly! See the screenshots below.

Here's some code to that implements this solution (make sure to add the library first from the link above):

function convSheetAndEmail(rng, email, subj)
{
  var HTML = SheetConverter.convertRange2html(rng);
  MailApp.sendEmail(email, subj, '', {htmlBody : HTML});
}

Then call that function:

function doGet()
{
  // or Specify a range like A1:D12, etc.
  var dataRange = SpreadsheetApp.getActiveSpreadsheet().getDataRange();

  var emailUser = '[email protected]';

  var subject = 'Test Email';

  convSheetAndEmail(dataRange, emailUser, subject);
}

Emailing Google Sheet range (with or without formatting) as a HTML table in a Gmail message

1 Answers