如何提取Google Sheet单元格条件格式颜色并写入HTML邮件正文

问题点排查 #

原有代码存在2个核心问题导致G列条件格式背景色无法同步到邮件：

• 获取背景色后做了错误的降维处理，仅保留了每行第一个单元格的背景色，直接丢失了G列的条件格式颜色值
• HTML模板中给整行统一设置背景色，没有针对单个单元格设置对应背景色

修正后代码 #

Google Apps Script 代码（.gs文件） #

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++;
    }
  }
  return lrow; // 补全原代码遗漏的返回值
}

function email_to_mgmt(){
  var ss = SpreadsheetApp.getActive().getSheetByName('sheet 1');
  var lastrow = last_row();
  var headers = ss.getRange("A1:H1").getValues()[0];
  
  var tableRange = ss.getRange(2,1,lastrow-1,8);
  var tableRangeValues = tableRange.getDisplayValues();
  var colors = tableRange.getBackgrounds(); // 保留二维数组结构，不做降维，保留所有单元格背景色

  var htmlTemplate = HtmlService.createTemplateFromFile("email");
  htmlTemplate.headers = headers;
  htmlTemplate.tableRangeValues = tableRangeValues;
  htmlTemplate.colors = colors;

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

  MailApp.sendEmail({
    to: "xxx@gmail.com",
    subject: "自定义邮件标题" + week,
    htmlBody: htmlForEmail
  });
}

HTML 模板代码（email.html文件） #

<!DOCTYPE html>
<html>
<head>
  <base target="_top">
</head>
<body>
  <div>
    <h1 style="color:#3368FF">Headerxxx</h1>
    <div style="font-size:14px">The following....</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;">
        <? headers.forEach(header=>{ ?>
          <th><?= header ?></th>
        <? }) ?>
      </tr>
    </thead>
    <tbody>
     <? tableRangeValues.forEach((row,rowIndex)=>{ ?>
      <tr>
        <? row.forEach((cell,colIndex)=>{ ?>
          <!-- 逐单元格设置对应背景色，完整同步条件格式效果 -->
          <td style="background-color: <?= colors[rowIndex][colIndex] ?>"><?= cell ?></td>
        <? }) ?>
      </tr>  
      <? }) ?>
    </tbody>
  </table>
</body>
</html>

改动说明 #

• 补全了last_row函数的返回值，避免获取行数为空报错
• 取消了背景色数组的错误降维处理，保留每行每个单元格的独立背景色
• HTML模板中改为逐单元格设置对应背景色，G列的条件格式高亮可以完整同步到邮件表格
• 优化了表头遍历逻辑，不需要单独定义每个表头变量

内容的提问来源于stack exchange，提问作者Vivian Liu