Copy Cell Color and Send in EMAIL Body-CodePudding

Column G has been set with conditional formatting.If less than 0.95, no highlight; if between 0.95 and 1.0, highlighted in green; if over 1.0, highlighted in red. Table in sheet

Can you please help capture the color in the sheet and copy to HTML email body? Many thanks.

Please find the code as below.

      
 function last_row(){
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sheet 1");
  var data = ss.getRange(1,1,100).getValues();
  var lrow = 0;
  for (i in data){
    var row = data[i];
    if(row[0] != ""){
    lrow  ;
    }
   }

function match(){
 var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Match');
  var match = ss.getRange(1,1,19,3).getValues();

function email_to_mgmt(){

  var ss = SpreadsheetApp.getActive().getSheetByName('sheet 1');
  var lastrow = last_row();
  var data = ss.getRange(1,1,lastrow,9).getValues();
  var s = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Match');
  var match = s.getRange(1,1,19,3).getValues();

  var headers = ss.getRange("A1:H1").getValues();
  var pgm_name = headers[0][0];
  var bug_id = headers[0][1];
  var rb_bug_reporter = headers[0][2];
  var rb_bug_yq = headers[0][3];
  var rb_approved_amount = headers[0][4];
  var total_po_spend = headers[0][5];
  var rb_unilization = headers[0][6];
  var scpm_cms = headers[0][7];

  var tableRange = ss.getRange(2,1,lastrow-1,8)
  var tableRangeValues = tableRange.getDisplayValues();
  var colors = tableRange.getBackgrounds().map(([a]) =>a);


  var htmlTemplate = HtmlService.createTemplateFromFile("email");
  htmlTemplate.pgm_name = pgm_name;
  htmlTemplate.bug_id = bug_id;
  htmlTemplate.rb_bug_reporter = rb_bug_reporter;
  htmlTemplate.rb_bug_yq = rb_bug_yq;
  htmlTemplate.rb_approved_amount = rb_approved_amount;
  htmlTemplate.total_po_spend = total_po_spend;
  htmlTemplate.rb_unilization = rb_unilization;
  htmlTemplate.scpm_cms = scpm_cms;
  

<!-- begin snippet: js hide: false console: true babel: false -->

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <div>
      <h1 style="color:#3368FF">Headerxxx</h1>
       <div style= font-size:14x>The following....</div>
    </div>
  <div></div>
  <table style="border-collapse:collapse;"border = 2 cellpadding = 6;>
    <thead>
      <tr style= "background-color:#00ffff;font-weight:bold;white-space:nowrap;text-align:center;">
        <th><?= pgm_name ?></th><th><?= bug_id ?></th><th><?= rb_bug_reporter ?></th><th><?= rb_bug_yq ?></th><th><?= rb_approved_amount ?></th><th><?= total_po_spend ?></th><th><?= rb_unilization ?></th><th><?= scpm_cms ?></th>
      </tr>
    </thead>
    <tbody>
     <? tableRangeValues.forEach((r,i)=>{ ?>
      <tr style="background-color: <?= colors[i]?>">
        <td> <?= r[0] ?> </td>
        <td> <?= r[1] ?> </td>
        <td> <?= r[2] ?> </td>
        <td> <?= r[3] ?> </td>
        <td> <?= r[4] ?> </td>
        <td> <?= r[5] ?> </td>
        <td> <?= r[6] ?> </td>
        <td> <?= r[7] ?> </td>
      </tr>  
      <?})?>
    </tbody>
  </table>
  </body>
</html>

htmlTemplate.colors = colors;

  htmlTemplate.tableRange = tableRange;
  htmlTemplate.tableRangeValues = tableRangeValues;

  var htmlForEmail = htmlTemplate.evaluate().getContent();

  var week = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Match").getRange(1,6).getValue();

  MailApp.sendEmail({
    to: "[email protected]",
    subject:xxx,
    htmlBody: htmlForEmail});
}

CodePudding user response：

I believe your goal as follows.

You want to set the background color of the column "G" in the HTML data. The color information is from sheet 1 sheet in the Spreadsheet.

In this case, how about the following modified script?

Modified script:

Google Apps Script side:

function email_to_mgmt() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("sheet 1");
  var range = sheet.getRange("A1:H"   sheet.getLastRow());
  var [[pgm_name, bug_id, rb_bug_reporter, rb_bug_yq, rb_approved_amount, total_po_spend, rb_unilization, scpm_cms], ...tableRangeValues] = range.getDisplayValues();
  var [, ...colors] = range.getBackgrounds().map(e => e[6]);
  var htmlTemplate = HtmlService.createTemplateFromFile("email");
  var obj = {pgm_name, bug_id, rb_bug_reporter, rb_bug_yq, rb_approved_amount, total_po_spend, rb_unilization, scpm_cms, tableRangeValues, colors};
  htmlTemplate.obj = obj;
  var htmlForEmail = htmlTemplate.evaluate().getContent();
  MailApp.sendEmail({ to: "[email protected]", subject: xxx, htmlBody: htmlForEmail });
}

I thought that the header row and the value rows can be retrieved by one call.
I thought that when an object is used for sending to HTML template, the script might be simpler.

HTML side:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <div>
      <h1 style="color:#3368FF">Headerxxx</h1>
       <div style= font-size:14x>The following....</div>
    </div>
  <div></div>
  <table style="border-collapse:collapse;"border = 2 cellpadding = 6;>
    <thead>
      <tr style= "background-color:#00ffff;font-weight:bold;white-space:nowrap;text-align:center;">
        <th><?= obj.pgm_name ?></th><th><?= obj.bug_id ?></th><th><?= obj.rb_bug_reporter ?></th><th><?= obj.rb_bug_yq ?></th><th><?= obj.rb_approved_amount ?></th><th><?= obj.total_po_spend ?></th><th><?= obj.rb_unilization ?></th><th><?= obj.scpm_cms ?></th>
      </tr>
    </thead>
    <tbody>
     <? obj.tableRangeValues.forEach((r,i)=>{ ?>
      <tr>
        <td> <?= r[0] ?> </td>
        <td> <?= r[1] ?> </td>
        <td> <?= r[2] ?> </td>
        <td> <?= r[3] ?> </td>
        <td> <?= r[4] ?> </td>
        <td> <?= r[5] ?> </td>
        <td style="background-color: <?= obj.colors[i]?>"> <?= r[6] ?> </td>
        <td> <?= r[7] ?> </td>
      </tr>  
      <?})?>
    </tbody>
  </table>
  </body>
</html>

When you want to change the background color of cell of column "G", please add the style to the tag td.

Modified script:

Google Apps Script side:

HTML side:

References: