求Google Sheets脚本：按事件名称匹配Tab2色值动态着色指定区域

编写App Script实现事件颜色匹配与自动更新 #

以下脚本可实现需求：根据第一个标签页J列的事件名称，匹配第二个标签页的十六进制颜色代码为B6:R区域着色，且日期下拉选择变更时自动同步颜色。

function setEventColors() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  // 替换为你的第一个标签页（时间表）名称
  const scheduleSheet = ss.getSheetByName("时间表");
  // 替换为你的第二个标签页（颜色映射表）名称
  const colorSheet = ss.getSheetByName("颜色配置");
  
  if (!scheduleSheet || !colorSheet) {
    SpreadsheetApp.getUi().alert("找不到指定标签页，请检查名称是否正确");
    return;
  }
  
  // 构建事件-颜色映射：假设颜色表中事件名称在H列（索引7），颜色代码在I列（索引8）
  const colorRange = colorSheet.getDataRange().getValues();
  const colorMap = {};
  for (let i = 1; i < colorRange.length; i++) {
    const eventName = colorRange[i][7]?.trim();
    const hexColor = colorRange[i][8]?.trim();
    if (eventName && hexColor) {
      colorMap[eventName] = hexColor;
    }
  }
  
  // 获取目标着色区域
  const targetRange = scheduleSheet.getRange("B6:R");
  const rowCount = targetRange.getNumRows();
  const colCount = targetRange.getNumColumns();
  const backgroundColors = [];
  
  // 逐行设置颜色
  for (let i = 0; i < rowCount; i++) {
    // 获取当前行J列（索引9）的事件名称
    const eventName = scheduleSheet.getRange(6 + i, 10).getValue()?.trim();
    const color = colorMap[eventName] || "#FFFFFF"; // 无匹配时用白色
    backgroundColors.push(new Array(colCount).fill(color));
  }
  
  targetRange.setBackgrounds(backgroundColors);
}

function onEdit(e) {
  // 检测日期选择单元格变更（假设日期在B3，替换为你的实际单元格地址）
  const editedSheet = e.range.getSheet();
  if (editedSheet.getName() === "时间表" && e.range.getA1Notation() === "B3") {
    setEventColors();
  }
}

配置与使用步骤 #

1. 打开你的Google表格，点击顶部菜单栏「扩展程序」→「Apps 脚本」进入编辑器
2. 清空默认代码，粘贴上述脚本
3. 修改关键参数：
   • 替换getSheetByName中的标签页名称，确保和你的表格标签一致
   • 如果颜色映射表的事件名称/颜色代码不在H/I列，修改colorRange[i][7]和colorRange[i][8]的索引（列索引从0开始，A列=0，B列=1...）
   • 如果日期下拉选择不在B3单元格，修改e.range.getA1Notation() === "B3"中的单元格地址
4. 保存脚本（命名任意，比如EventColorSync）
5. 首次运行授权：点击编辑器的「运行」按钮，选择setEventColors，按提示完成授权（需要允许脚本访问表格数据）

注意事项 #

• 确保颜色代码是标准十六进制格式（如#FF5733），不要遗漏开头的#
• 事件名称前后的空格会被trim()处理，避免因空格导致匹配失败
• 如果需要调整无匹配事件的默认颜色，修改|| "#FFFFFF"中的颜色值为你需要的背景色
• 若后续修改颜色映射表，可手动运行setEventColors函数立即更新所有颜色

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