AppScript生成HTML表格时子标题格式异常问题求助

问题描述 #

我编写了一段Google AppScript，预期实现以下功能：

• 创建两个可折叠容器（已正常运行）
• 每个容器内嵌入从Google Sheets拉取数据的表格
• 表格包含顶行标题和子标题，子标题在Google Sheets中以[]包裹定义

目前脚本其他功能正常，但子标题存在两个样式问题：

• 无法修改子标题的字体颜色
• 子标题行底纹会继承上一行的样式

问题原因 #

1. 非表头行的子标题<th>未添加section-header类，导致对应的CSS样式无法生效
2. 生成子标题时额外插入了新的<tr>标签，同时保留了原循环生成的空<tr>，既造成HTML结构冗余，又让奇偶行底纹规则错误作用在子标题行上

修改后的完整代码 #

function convertGrantsAndLoansTableToHtml() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var grantsSheetName = "Grants"; // 修改为你的Grants工作表名称
  var loansSheetName = "Loans"; // 修改为你的Loans工作表名称

  var grantsSheet = spreadsheet.getSheetByName(grantsSheetName);
  var loansSheet = spreadsheet.getSheetByName(loansSheetName);

  var grantsRange = grantsSheet.getDataRange();
  var loansRange = loansSheet.getDataRange();

  var grantsData = grantsRange.getValues();
  var loansData = loansRange.getValues();

  var html = '<style> \
.expandable-section { margin-bottom: 10px; } \
.expandable-section input[type="checkbox"] { display: none; } \
.expandable-section label { \
display: block; \
cursor: pointer; \
font-family: "Calibri", sans-serif; /* 在这里修改字体 */ \
font-weight: bold; \
font-size: 20px; \
color: #FCFCFC; \
background-color: #84B83F; \
padding: 4px 6px; \
border-radius: 3px; \
} \
.expandable-section input[type="checkbox"]:checked + label { \
background-color: #84B83F; \
color: #FCFCFC; \
} \
.expandable-section-content { \
display: none; \
} \
.expandable-section input[type="checkbox"]:checked ~ .expandable-section-content { \
display: block; \
margin-top: 5px; \
} \
table { \
border-collapse: collapse; \
font-size: medium; \
width: 100%; \
font-family: "Calibri", sans-serif; /* 在这里修改字体 */ \
} \
th, td { \
border: 1px solid black; \
padding: 8px; \
} \
th { \
font-weight: bold; \
font-size: larger; \
text-align: left; \
color: #F37525; \
} \
th.section-header { \
font-size: smaller; \
text-align: center; \
color: #84B83F; \
} \
tr.section-header-row { \
background-color: transparent; /* 重置子标题行底纹 */ \
} \
th:first-child, td:first-child { \
font-weight: bold; \
} \
tr:not(.section-header-row):nth-child(even) { \
background-color: #E4E4E4; \
} \
</style>';

  html += '<div class="expandable-section">';
  html += '<input type="checkbox" id="grantsToggle">';
  html += '<label for="grantsToggle">Grants Form Fields</label>';

  html += '<div class="expandable-section-content">';
  html += '<table>';

  for (var i = 0; i < grantsData.length; i++) {
    var cellValue = grantsData[i][0];
    if (cellValue.startsWith('[') && cellValue.endsWith(']')) {
      // 处理子标题行，直接生成带类的tr和th
      html += '<tr class="section-header-row"><th colspan="' + grantsData[i].length + '" class="section-header">' + cellValue.substring(1, cellValue.length - 1) + '</th></tr>';
      continue; // 跳过当前行的其他处理
    }
    
    html += '<tr>';
    for (var j = 0; j < grantsData[i].length; j++) {
      cellValue = grantsData[i][j];
      var isHeaderRow = (i === 0);
      var isLeftColumn = (j === 0);

      if (isHeaderRow) {
        if (cellValue.startsWith('[') && cellValue.endsWith(']')) {
          html += '<th class="section-header">' + cellValue.substring(1, cellValue.length - 1) + '</th>';
        } else {
          html += '<th>' + cellValue + '</th>';
        }
      } else {
        if (isLeftColumn) {
          html += '<td><b>' + cellValue + '</b></td>';
        } else {
          html += '<td>' + cellValue + '</td>';
        }
      }
    }
    html += '</tr>';
  }

  html += '</table>';
  html += '</div>'; // End of expandable-section-content
  html += '</div>'; // End of expandable-section

  html += '<div class="expandable-section">';
  html += '<input type="checkbox" id="loansToggle">';
  html += '<label for="loansToggle">Loans Form Fields</label>';

  html += '<div class="expandable-section-content">';
  html += '<table>';

  for (var i = 0; i < loansData.length; i++) {
    var cellValue = loansData[i][0];
    if (cellValue.startsWith('[') && cellValue.endsWith(']')) {
      // 处理子标题行，直接生成带类的tr和th
      html += '<tr class="section-header-row"><th colspan="' + loansData[i].length + '" class="section-header">' + cellValue.substring(1, cellValue.length - 1) + '</th></tr>';
      continue; // 跳过当前行的其他处理
    }
    
    html += '<tr>';
    for (var j = 0; j < loansData[i].length; j++) {
      cellValue = loansData[i][j];
      var isHeaderRow = (i === 0);
      var isLeftColumn = (j === 0);

      if (isHeaderRow) {
        if (cellValue.startsWith('[') && cellValue.endsWith(']')) {
          html += '<th class="section-header">' + cellValue.substring(1, cellValue.length - 1) + '</th>';
        } else {
          html += '<th>' + cellValue + '</th>';
        }
      } else {
        if (isLeftColumn) {
          html += '<td><b>' + cellValue + '</b></td>';
        } else {
          html += '<td>' + cellValue + '</td>';
        }
      }
    }
    html += '</tr>';
  }

  html += '</table>';
  html += '</div>'; // End of expandable-section-content
  html += '</div>'; // End of expandable-section


  var date = new Date();
  var dateString = date.toLocaleDateString().replace(/\//g, '-');
  var timeString = date.toLocaleTimeString().replace(/:/g, '-');
  var fileName = "output_" + dateString + "_" + timeString + ".html";
  var folderName = "Output"; // 文件保存的文件夹名称
  var parentFolder = DriveApp.getRootFolder();
  var folders = DriveApp.getFoldersByName(folderName);

  if (folders.hasNext()) {
    parentFolder = folders.next();
  } else {
    parentFolder = parentFolder.createFolder(folderName);
  }

  var file = parentFolder.createFile(fileName, html, "text/html");
  Logger.log('File created: ' + file.getUrl());
}

修改说明 #

1. CSS样式调整：

   • 添加tr.section-header-row规则，重置子标题行的背景色为透明，避免继承奇偶行底纹
   • 修改奇偶行底纹的选择器为tr:not(.section-header-row):nth-child(even)，排除子标题行

2. HTML生成逻辑修复：

   • 提前判断当前行是否为子标题行，直接生成带section-header-row类的<tr>和带section-header类的<th>
   • 跳过原循环中对子标题行的空<tr>生成，解决结构冗余问题
   • 给非表头行的子标题<th>添加section-header类，让字体颜色样式生效

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