基于Google Apps Script实现数据转热力图表格并添加单元格批注

需求概述 #

从左侧数据表格提取行、列、DBH必填字段，自动生成右侧热力图配置表格，同时将自定义字段（如ID、DBH）插入对应单元格的批注中，最终生成可直接用于配置热力图的表格结构。

分步实现代码 #

以下代码对应需求中的全部流程步骤，可直接在Google Sheets脚本编辑器中运行：

function generateHeatmapTable() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const dataSheet = ss.getSheetByName("数据表格"); // 替换为你的数据表格名称
  const heatmapSheet = ss.getSheetByName("热力图表格"); // 替换为你的热力图表格名称

  // 获取数据范围（假设数据从A1开始，包含ID、Row、Column、DBH列）
  const dataRange = dataSheet.getDataRange();
  const dataValues = dataRange.getValues();
  const headers = dataValues[0];
  const rowColIndex = headers.indexOf("Row");
  const colColIndex = headers.indexOf("Column");
  const dbhColIndex = headers.indexOf("DBH");
  const idColIndex = headers.indexOf("ID");

  // 获取最大行号和列号，确定热力图尺寸
  const maxRow = Math.max(...dataValues.slice(1).map(row => row[rowColIndex]));
  const maxCol = Math.max(...dataValues.slice(1).map(row => row[colColIndex]));

  // --------------------------
  // 步骤1&2：处理蓝色表头行
  // --------------------------
  // 步骤1：在G1添加加粗的"Column"文本
  const columnHeader = SpreadsheetApp.newRichTextValue()
    .setText("Column")
    .setTextStyle(0, 7, SpreadsheetApp.newTextStyle().setBold(true).build())
    .build();
  heatmapSheet.getRange("G1").setRichTextValue(columnHeader);

  // 步骤2：填充N个编号列（本例为1、2）
  const colNumbers = Array.from({length: maxCol}, (_, i) => [i+1]);
  heatmapSheet.getRange(2, 7, 1, maxCol).setValues([colNumbers.flat()]);

  // --------------------------
  // 步骤3&4：处理灰色行标题
  // --------------------------
  // 步骤3：在F2添加加粗的"Row"文本
  const rowHeader = SpreadsheetApp.newRichTextValue()
    .setText("Row")
    .setTextStyle(0, 3, SpreadsheetApp.newTextStyle().setBold(true).build())
    .build();
  heatmapSheet.getRange("F2").setRichTextValue(rowHeader);

  // 步骤4：填充热力图行列值
  const rowNumbers = Array.from({length: maxRow}, (_, i) => [i+1]);
  heatmapSheet.getRange(3, 6, maxRow, 1).setValues(rowNumbers);

  // --------------------------
  // 步骤5：复制DBH值到对应单元格
  // --------------------------
  dataValues.slice(1).forEach(row => {
    const heatmapRow = row[rowColIndex] + 2; // 对应F3开始的行号
    const heatmapCol = row[colColIndex] + 6; // 对应G2开始的列号
    heatmapSheet.getRange(heatmapRow, heatmapCol).setValue(row[dbhColIndex]);

    // --------------------------
    // 步骤6：添加单元格批注（ID+DBH）
    // --------------------------
    const commentText = `ID: ${row[idColIndex]}\nDBH: ${row[dbhColIndex]}`;
    heatmapSheet.getRange(heatmapRow, heatmapCol).clearComment().addComment(commentText);
  });

  // 步骤7：完成后表格可直接用于热力图配置
  SpreadsheetApp.getUi().alert("热力图表格已生成完成！");
}

代码说明 #

• 替换代码中的数据表格和热力图表格为实际工作表名称
• 若需调整批注中的自定义字段，只需修改commentText变量的内容即可
• 表头字段名称需与代码中的Row、Column、DBH、ID保持一致，若字段名不同，需修改对应indexOf的参数

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