如何用脚本将Google Sheets指定单元格背景设为其他区域Hex色值

核心功能脚本 #

以下脚本可实现你需要的两种颜色背景设置需求：

// 实现Data表与Working表的颜色映射
function setDragonColorBackgrounds() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const dataSheet = ss.getSheetByName('Data');
  const workingSheet = ss.getSheetByName('Working');
  
  // 定义行映射规则：Data表行 → Working表行
  const rowMappings = [
    { dataRow: 2, workingRow: 4 },
    { dataRow: 3, workingRow: 6 },
    { dataRow: 4, workingRow: 8 }
  ];
  
  rowMappings.forEach(mapping => {
    // 获取Data表对应行的Hex色码（Z到DI列）
    const hexValues = dataSheet.getRange(`Z${mapping.dataRow}:DI${mapping.dataRow}`).getValues()[0];
    // 获取Working表对应行的目标单元格（K到CT列）
    const targetRange = workingSheet.getRange(`K${mapping.workingRow}:CT${mapping.workingRow}`);
    
    // 生成背景色数组，过滤无效Hex码
    const backgrounds = hexValues.map(hex => {
      return hex && /^#[0-9A-Fa-f]{6}$/.test(hex) ? hex : 'transparent';
    });
    
    targetRange.setBackgrounds([backgrounds]);
  });
}

// 处理命名区域的颜色设置
function setNamedRangeColors() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // 获取指定命名区域
  const colorHexRange = ss.getRangeByName('ColorHex1');
  const colorTargetRange = ss.getRangeByName('ColorRange1');
  
  if (!colorHexRange || !colorTargetRange) {
    SpreadsheetApp.getUi().alert('未找到指定命名区域，请检查名称');
    return;
  }
  
  const hexValues = colorHexRange.getValues();
  const [targetRows, targetCols] = [colorTargetRange.getNumRows(), colorTargetRange.getNumCols()];
  
  // 校验区域行列数是否匹配
  if (hexValues.length !== targetRows || hexValues[0].length !== targetCols) {
    SpreadsheetApp.getUi().alert('ColorHex1与ColorRange1行列数不匹配，请调整');
    return;
  }
  
  // 生成背景色数组
  const backgrounds = hexValues.map(row => 
    row.map(hex => hex && /^#[0-9A-Fa-f]{6}$/.test(hex) ? hex : 'transparent')
  );
  
  colorTargetRange.setBackgrounds(backgrounds);
}

// 一键执行所有颜色设置
function runAllColorSettings() {
  setDragonColorBackgrounds();
  setNamedRangeColors();
}

使用步骤 #

1. 打开你的Google表格，点击顶部「扩展程序」→「Apps脚本」；
2. 替换默认代码为上述脚本，保存项目（可命名为「龙属性颜色设置」）；
3. 按需运行对应函数：
   • setDragonColorBackgrounds：完成Data与Working表的颜色映射；
   • setNamedRangeColors：处理命名区域的颜色设置；
   • runAllColorSettings：一键执行两个任务；
4. 首次运行需完成授权，按提示操作即可。

注意事项 #

• 确保Hex色码格式为#RRGGBB标准格式；
• 命名区域ColorHex1和ColorRange1需提前创建，且行列数必须一致；
• 无效或为空的Hex码会对应设置透明背景。

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