Format a table and send in email

Question

I am trying to send an email containing a table, I have managed to create the table but can not figure out how I copy over the format including the background colour and number format, as some cells contain currency as well as percentages and plain numbers.

As the data is colour coded, it is important to copy those over into the email.

here is the code I have, if someone could point me in the right direction, it would be extremely helpful, here is my code so far....

function createTable(data){ 
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  
  //To be looped in the future, 10 in range to be replaced with k
  var dataRange = sheet.getRange(10,4,1,9);
  var data = dataRange.getValues();

  //Table Header
  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>";

  //table Body
 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>"
    

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

score 3 · Accepted Answer · answered Feb 18 '22 at 12:36

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

@Daniel Lawrie Thank you for replying and testing it. I'm glad your issue was resolved. Thank you, too. — Tanaike, Feb 18 '22 at 13:17

score 0 · Answer 2 · answered Feb 18 '22 at 13:09

Well, there is no direct way of doing this since you are trying to convert Sheets data scraped from a spreadsheet into plain HTML.

However, this can be still easily accomplished if you do some simple formatting. To your example I've added:

Background color: I've done this by gathering the background colors of all cells in the range using the getBackgrounds() function (you can read more about it here) and then styling each cell with that background color using CSS.
Basic formatting: I've accomplished that by using the getNumberFormats() function (of which you can read more here) and then crudely adding a dollar sign or percentage if that cell is formatted as a currency or percentage.
Not related to your inquiry, but I've moved the sendEmail part of your code out of the loop. In the code you provided, you would be sending one email per each row of data gathered.

function createTable(data){ 
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  
  //To be looped in the future, 10 in range to be replaced with k
  var dataRange = sheet.getRange(10,4,1,9);
  var data = dataRange.getValues();
  var colors = dataRange.getBackgrounds();
  var formats = dataRange.getNumberFormats();

  //Table Header
  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>";

  //table Body
 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++){
          var cellColor = colors[i][u];
          var format = formats[i][u];

          var finalValue;
          if (format.includes("$")){finalValue = "$"+cells[u];}
          else if (format.includes("%")){finalValue = (parseFloat(cells[u]) * 100).toString() + " %"}
          else {finalValue = cells[u];}

          table = table + "<td style='background-color:"+cellColor +"'>"+ finalValue +"</td>";
      }
    table = table + "</tr>"
    
  }

  //Send the email:
  MailApp.sendEmail({
    to: "oriol@egs-sbt100.xyz", 
    subject: "Example",
    htmlBody: table});
     
 }

Format a table and send in email

2 Answers2

Modified script:

Reference: