How do I mail cell range with proper format in google sheet?

Question

Hi this is my sheet: https://docs.google.com/spreadsheets/d/1xkTt_1kLyROkUFComi8-NiKyDMan6617C1dA5UqWTSI/edit?usp=sharing

I want to share range A2:F8 in email using google script. As far as I can say this script works fine:

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("myemail@gmail.com", 'Daily report','' ,{htmlBody: htmltable})
}

The problem is when i get mail the merged cells are not in merged state. This result in 3 columns instead of 1. Can someone please tweak the code so that I get exactly like i formatted sheets.

Answer 1

I modified the code a little because I didn't want to send any emails. And if you'll move the table up to the top left corner and use sh.getDataRange() then you'll get the entire table with no problems.

This is my modified version.

function sendSomething(){
 var sh = SpreadsheetApp.getActiveSheet();
 var dataA = sh.getDataRange().getValues();
 var None = ' ';
 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<dataA.length; row++)
{
  htmltable += '<tr>';
  for(col = 0 ;col<dataA[row].length; col++)
  {
    if (dataA[row][col] === "" || 0) 
    {
      htmltable += '<td>' + None + '</td>';
    } 
    else
    if (row === 0)  
    {
      htmltable += '<th>' + dataA[row][col] + '</th>';
    }
    else 
    {
      htmltable += '<td>' + dataA[row][col] + '</td>';
    }
  }
     htmltable += '</tr>';
}
     htmltable += '</table>';
dispStatus('HTML',htmltable,800,500);
}

And this is my display routine which I incorporated into your code. It's just my version of the logger.

function dispStatus(title,html,width,height,modal)
{
  var title = typeof(title) !== 'undefined' ? title : 'No Title Provided';
  var width = typeof(width) !== 'undefined' ? width : 400;
  var height = typeof(height) !== 'undefined' ? height : 300;
  var html = typeof(html) !== 'undefined' ? html : '<p>No html provided.</p>';
  var modal = typeof(modal) !== 'undefined' ? modal : false;
  var htmlOutput = HtmlService
     .createHtmlOutput(html)
     .setWidth(width)
     .setHeight(height);
 if(!modal)
 {
   SpreadsheetApp.getUi().showModelessDialog(htmlOutput, title);
 }
 else
 {
   SpreadsheetApp.getUi().showModalDialog(htmlOutput, title);
 }
} 

You still need to format the dates. Perhaps you can use Utilities and if you want to merge cells together in the header you'll need to figure out which ones to give a colspan="3".