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})
}

Solution

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);
}

Comments

[Excel Cabbage]

Could you be more specific on how to concatenate it into a string and send it on through part? What do I need to change in my code? I tried using htmltable += but it get an error Missing ; before statement. (line 7, file "SendMail") . I'm pretty new to JS so I'm sure I'm missing something obvious.

[Gerneio]

Just tested with the code I posted above and it works just fine.

[Excel Cabbage]

Your change did the trick! Thanks! EDIT: I didn't see your edit when posting my comment above! I've marked your answer as accepted.

[Gerneio]

👍🏼 your welcome! I also tested with the array.join() method, but it would come up with just an entire row of commas, but the rest of the table showed up fine. So either way, one should work. If you ever plan on trying the question 2 part and find a good solution, post back here and let us all know! I'd sure like to know.

[Gerneio]

I've been fiddling around with your question 2 part and I've gotten to the point to where I can pull in the html, and navigate the structure so that I only get the table data. However I'm having trouble getting the CSS along with it because it's contained in the <head> tag section. I'll probably try some regex later on when I have more time to dig for the solution.

[Excel Cabbage]

Perhaps check how this library does it: github.com/mogsdad/SheetConverter/blob/master/SheetConverter‌​/… </br> I just don't get how the guy gets the value from it. Apparently it is possible to get said data if I'm reading this correctly: developers.google.com/apps-script/reference/document/…

[Gerneio]

See above for solution to 2

[Excel Cabbage]

Just tested and it works great. I tried that library before but it would only give me part of the range as HTML and wouldn't close the table. Probably some bug in my noobie code. Thanks a lot.

[Gerneio]

👍🏼👍🏼 I think we both got something out of this 😁

[Elad Benda]

I get an error when I import the SheetConverter library

[DanCue]

I'm getting an error as well. First, I couldn't find out how to add the library using the new editor. I had to go into legacy editor, add the library and then go back to the new editor. When I try to save after that it tells me "You no longer have permission to edit this project"