谷歌表格脚本开发求助：获取指定工作表信息、单元格数据并实现条件逻辑与格式设置

阿华AIGC实验室

2026-4-23

嘿Bruno，看起来你已经迈出了很好的第一步！我来帮你把这个自动化评估表的功能完整实现出来，咱们一步步来：

首先，先明确你的需求要点

提取除前两个工作表外的所有表名，按升序排列到一列
获取这些工作表中K43单元格的数值，写入相邻列
给数值列设置条件格式：
- 0 ≤ 数值 <7：红色背景+白色字体
- 7 ≤ 数值 <8：白色背景+黑色字体
- 8 ≤ 数值 <9：橙色背景+白色字体
- 9 ≤ 数值 ≤10：绿色背景+黑色字体
再下一列根据数值区间，显示Informatie表中B2-B5的对应内容，同时匹配对应格式

完整实现脚本

下面是整合了所有功能的代码，你可以直接复制到谷歌表格的脚本编辑器中（工具→脚本编辑器）：

function generateEvaluationSheet() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  // 假设我们把汇总数据写到名为"评估汇总"的工作表，你可以根据实际修改
  const summarySheet = ss.getSheetByName("评估汇总") || ss.insertSheet("评估汇总");
  const infoSheet = ss.getSheetByName("Informatie");
  
  // 1. 获取除前两个工作表外的所有表，按名称升序排序
  const targetSheets = ss.getSheets()
    .slice(2) // 跳过前两个表
    .sort((a, b) => a.getName().localeCompare(b.getName())); // 按表名升序排列

  // 2. 收集表名、K43值、对应信息
  const data = [];
  const infoTexts = [
    infoSheet.getRange("B2").getValue(),
    infoSheet.getRange("B3").getValue(),
    infoSheet.getRange("B4").getValue(),
    infoSheet.getRange("B5").getValue()
  ];

  targetSheets.forEach(sheet => {
    const sheetName = sheet.getName();
    const k43Value = sheet.getRange("K43").getValue();
    let infoText = "";
    
    // 根据数值区间匹配对应信息
    if (k43Value >= 0 && k43Value <7) {
      infoText = infoTexts[0];
    } else if (k43Value >=7 && k43Value <8) {
      infoText = infoTexts[1];
    } else if (k43Value >=8 && k43Value <9) {
      infoText = infoTexts[2];
    } else if (k43Value >=9 && k43Value <=10) {
      infoText = infoTexts[3];
    }
    
    data.push([sheetName, k43Value, infoText]);
  });

  // 3. 清空汇总表原有数据，写入新数据
  summarySheet.clear();
  summarySheet.getRange(1, 1, data.length, 3).setValues(data);
  // 设置表头（可选）
  summarySheet.getRange(1,1,1,3).setValues([["工作表名称", "评估得分", "评估结果"]]).setFontWeight("bold");

  // 4. 设置条件格式：得分列（B列）和结果列（C列）
  const scoreColumn = summarySheet.getRange(2, 2, data.length); // 从第二行开始的得分列
  const resultColumn = summarySheet.getRange(2, 3, data.length); // 结果列

  // 定义格式规则数组
  const formatRules = [
    {
      condition: SpreadsheetApp.newCondition().whenNumberBetween(0, 7).build(),
      scoreFormat: SpreadsheetApp.newTextStyle().setForegroundColor("#ffffff").build(),
      scoreBackground: "#ff0000",
      resultFormat: SpreadsheetApp.newTextStyle().setForegroundColor("#ffffff").build(),
      resultBackground: "#ff0000"
    },
    {
      condition: SpreadsheetApp.newCondition().whenNumberBetween(7, 8).build(),
      scoreFormat: SpreadsheetApp.newTextStyle().setForegroundColor("#000000").build(),
      scoreBackground: "#ffffff",
      resultFormat: SpreadsheetApp.newTextStyle().setForegroundColor("#000000").build(),
      resultBackground: "#ffffff"
    },
    {
      condition: SpreadsheetApp.newCondition().whenNumberBetween(8, 9).build(),
      scoreFormat: SpreadsheetApp.newTextStyle().setForegroundColor("#ffffff").build(),
      scoreBackground: "#ff9900",
      resultFormat: SpreadsheetApp.newTextStyle().setForegroundColor("#ffffff").build(),
      resultBackground: "#ff9900"
    },
    {
      condition: SpreadsheetApp.newCondition().whenNumberBetween(9, 10).build(),
      scoreFormat: SpreadsheetApp.newTextStyle().setForegroundColor("#000000").build(),
      scoreBackground: "#00ff00",
      resultFormat: SpreadsheetApp.newTextStyle().setForegroundColor("#000000").build(),
      resultBackground: "#00ff00"
    }
  ];

  // 先清除原有条件格式
  scoreColumn.clearConditionalFormatRules();
  resultColumn.clearConditionalFormatRules();

  // 应用新的条件格式规则
  formatRules.forEach(rule => {
    // 给得分列加规则
    const scoreConditionalRule = SpreadsheetApp.newConditionalFormatRule()
      .setCondition(rule.condition)
      .setBackground(rule.scoreBackground)
      .setTextStyle(rule.scoreFormat)
      .setRanges([scoreColumn])
      .build();
    
    // 给结果列加规则（基于得分列的数值）
    const resultConditionalRule = SpreadsheetApp.newConditionalFormatRule()
      .setCondition(rule.condition)
      .setBackground(rule.resultBackground)
      .setTextStyle(rule.resultFormat)
      .setRanges([resultColumn])
      .build();
    
    summarySheet.setConditionalFormatRules([
      ...summarySheet.getConditionalFormatRules(),
      scoreConditionalRule,
      resultConditionalRule
    ]);
  });

  SpreadsheetApp.getUi().alert("评估表已生成完成！");
}

脚本使用说明

先确保你的表格中有Informatie工作表，且B2-B5已经填好了对应区间的文本内容
运行generateEvaluationSheet函数，脚本会自动创建（或使用已有的）"评估汇总"工作表，写入所有数据并设置好格式
如果需要调整数值区间的边界（比如是否包含7、8这些临界值），可以修改代码中if-else判断的条件，以及条件格式的whenNumberBetween参数