Emailing Google Sheet Range (With or Without Formatting) as a HTML Table in a Gmail Message

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

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 = 'test@email.com';

  var subject = 'Test Email';

  convSheetAndEmail(dataRange, emailUser, subject);
}

Google sheets | Sending a cell range to email body

This is a script I have that pulls multiple values and puts them into an array and emails them. Make sure after .getValues() you have .toString() to converts the value into strings which might be where you are encountering your "Ljava.lang.Object;@7d193b9b" problem. It will just produce a list with values separated by commas.

so like:

var list = refsheet.getRange(2, 14, 2+i).getValues().toString()

then for complete example:

    function AttendanceAlert() {
      var refsheet = SpreadsheetApp.getActive().getSheetByName("AttendanceNotification")
      var column = refsheet.getRange('N2:N');
      var cell = refsheet.getRange(1, 10).getValue(); //get the date I want to search for
      var celldate = new Date(cell);
      var date = Utilities.formatDate(celldate, "GMT","EEE, MM-dd-yy");
      var values = column.getValues(); // get all data in one call
      var ct = 0;
      while ( values[ct][0] != "" ) {
        ct++;
      }
     var numbers = ct

      for(var i = 0; i < numbers; i++) {
      var list = refsheet.getRange(2, 14, 2+i).getValues().toString()
      Logger.log(list) 
      }
       var recipientsTO = "recipient@email.com" + "," + "recipient@email.com";
      MailApp.sendEmail(recipientsTO, "Attendance Update :"+" "+ date , "These individuals have 5 or more total infractions (see parentheses for total), and one recent infraction as of "+ date + ":" +" "+ list + '\n' +

}

Now for your example:

sendSpreadsheetToPdf(3, shName, ('email@gmail.com'), sh.getRange('B3').getValue(), Utilities.formatDate(sh.getRange('B4').getValue(), "NZ", "EEE MMM dd"), sh.getRange(28,4,13,1).getValues().toString());

Let me know if that works.

Update:

To get the values seperated by line breaks rather than commas, try this:

sendSpreadsheetToPdf(3, shName, ('email@gmail.com'), sh.getRange('B3').getValue(), Utilities.formatDate(sh.getRange('B4').getValue(), "NZ", "EEE MMM dd"), sh.getRange(28,4,13,1).getValues().join("\n"));

if that doesn't work, try:

sendSpreadsheetToPdf(3, shName, ('email@gmail.com'), sh.getRange('B3').getValue(), Utilities.formatDate(sh.getRange('B4').getValue(), "NZ", "EEE MMM dd"), sh.getRange(28,4,13,1).getValues().join('<br/>'));

Format a table and send in email

I believe your goal is as follows.

• You want to create a HTML table including the cell background colors and the number format.

In this case, how about the following modification?

Modified script:

function createTable() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var dataRange = sheet.getRange(10, 4, 1, 9);
  var backgrounds = dataRange.getBackgrounds(); // Added
  var data = dataRange.getDisplayValues(); // Modified
  var table = "<html><body><br><table border=1><tr><th>Compliance Score</th><th>STSOR Value</th><th>STSOR %</th><th>ZZ lines</th><th>ZZ%</th><th>PI lines Counted</th><th>PI %</th><th>NRDS Value</th><th>NRDS %</tr></br>";
  for (var i = 0; i < data.length; i++) {
    cells = data[i];
    table = table + "<tr>";
    for (var u = 0; u < cells.length; u++) {
      table = table + `<td style="background-color:${backgrounds[i][u]}">` + cells[u] + "</td>"; // Modified
    }
    table = table + "</tr>"
    MailApp.sendEmail({
      to: "example@gmail.com",
      subject: "Example",
      htmlBody: table
    });
  }
}

Reference:

• getDisplayValues()

How to send a table in an email in Google Scripts?

Instead of pushing the html code into an array, join the new strings in the same variable. Also, use htmlbody as your email body. Notice the variable table in the for loops:

function createTable(data){ 
  var cells = [];
  //This would be the header of the table
  var table = "<html><body><br><table border=1><tr><th>Column A</th><th>Column B</th><th>Column C</th><th>Column D</th><th>Column E</tr></br>";

  //the body of the table is build in 2D (two foor loops)
  for (var i = 0; i < data.length; i++){
      cells = data[i]; //puts each cell in an array position
      table = table + "<tr>";

      for (var u = 0; u < cells.length; u++){
          table = table + "<td>"+ cells[u] +"</td>";
      }
  table = table + "</tr>"
  }

  table=table+"</table></body></html>";
  

  //Send the email:
 MailApp.sendEmail({
    to: "example@mail.com", 
    subject: "Example",
    htmlBody: table}); 

}

More information about MailApp and htmlbody here.

Maintaining table status when fetching Google Sheets values

Building the html table

function WeeklyReminder() {
  var ss=SpreadsheetApp.getActive();
  var sheet=ss.getSheetByName("Automated WVS Weekly Reminder Email");
  var vs=sheet.getRange(1,4,sheet.getLastRow(),2).getDisplayValues();
  var html='<style>td,th{border:1px solid black;}</style><table>';
  vs.forEach(function(r,i){
    if(i==0) {
      html+=Utilities.formatString('<tr><th>%s</th><th>%s</th></tr>',r[0],r[1]);//I changed the range so that it included the headers
    }else{
      html+=Utilities.formatString('<tr><td>%s</td><td>%s</td></tr>',r[0],r[1]);
    }
    html+='</table>';
  });
  var test_email="o...@...org";
  var volunteer_values=sheet.getRange(2,2,sheet.getLastRow()-1,1).getDisplayValues();
  MailApp.sendEmail({to: test_email,subject: 'WVS Weekly Reminder',htmlBody:html});
}

Send Email when a column value changes in a google spreadsheet

I believe your goal is as follows.

• When the column "G" is manually edited, you want to run your script by the installable OnEdit trigger.
• When the script is run, you want to retrieve the edited row and create message and send them as an email.

Modification points:

• In your script, rows is not defined. This is the reason for your issue of "ReferenceError: rows is not defined". And also, col is not declared.
• When I saw your sample Spreadsheet, the name of the named range is EmailChange. But, you are using the name EmailChanges.
• e.oldvalue always returns undefined. The property name is oldValue.
• In your template, rows is not put.
• From your showing expected goal, message is not included in the template.
• message of MailApp.sendEmail(message) has no property of message.

When these points are reflected in your script, it becomes as follows.

Modified script:

Google Apps Script: Code.gs

function processEdit(e) {
  var { source, range, value, oldValue } = e;
  var sheet = range.getSheet();
  var editedRow = range.rowStart;
  if (editedRow >= 3 && editedRow <= 1339 && range.columnStart == 7 && sheet.getSheetName() == "Change Requests") {
    var message = "The Column G Cell " + range.getA1Notation() + " was modified from '" + oldValue + "' to '" + value + "'";
    var namedRange = source.getRangeByName("EmailChange");
    var rows = namedRange.getValues();
    var headerRow = rows[0];
    var template = HtmlService.createTemplateFromFile("Template");
    var idx = editedRow - namedRange.getRow();
    template.message = message;
    template.rows = [rows[idx]];
    template.headerRow = headerRow;
    template.editedRow = editedRow;
    var html = template.evaluate().getContent();
    MailApp.sendEmail({
      to: "example@gmail.com",
      subject: "Change",
      name: 'GSD BI',
      htmlBody: html
    });
  }
}

HTML: Template.html

<p><?!= message ?></p>
<p>Please see the highlighted row below for information about the Changes.</p>

<hr>
<br>
<table cellpadding='5'>
  <tr>
    <th bgcolor='#eaeaea'><?= headerRow[0] ?></th>
    <th bgcolor='#eaeaea'><?= headerRow[1] ?></th>
    <th bgcolor='#eaeaea'><?= headerRow[2] ?></th>
  </tr>
  <? var rowIndex = 3; ?>
  <? for(var i = 0; i < rows.length; i++) { ?>
  <?   var background = rowIndex === editedRow ? "#e06666" : "#ffffff";  ?>
  <tr>
    <td bgcolor='<?= background ?>'><?= rows[i][0] ?></td>
    <td bgcolor='<?= background ?>'><?= rows[i][1] ?></td>
    <td bgcolor='<?= background ?>'><?= rows[i][2] ?></td>
  </tr>
  <?    rowIndex = rowIndex + 1; ?>
  <? } ?>
</table>

Note:

• In your script, oldValue is used. In this case, please edit the cell by directly and manually putting the value. When you copy and paste the value, oldValue is not returned. Please be careful about this.

• This modified script is for your sample Spreadsheet. If your actual Spreadsheet is different, please check the name of the named range again.

• In this script, from your showing script, it supposes that your script is run by the installed OnEdit trigger. If you directly run the script with the script editor, an error occurs because of no event object. Please be careful about this.

Reference:

• sendEmail(message) of Class MailApp
• Installable Triggers

---

Related Topics