Google Apps Script复制条件格式报错，求跨工作表复制解决方案

嘿，这个报错我之前也碰到过！原因很简单：你直接把原工作表的规则数组复制过去时，每个规则的应用范围还是指向第一个工作表（比如Sheet1!A1:C20），而Google Sheets的条件格式规则不允许引用其他工作表的范围，所以才会触发这个错误。

下面是修改后的代码，能完美解决这个问题：

function copyConditionalFormatting() {
  const mySpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const allSheets = mySpreadsheet.getAllSheets();
  const sourceSheet = allSheets[0];
  const sourceRules = sourceSheet.getConditionalFormatRules();

  // 遍历除源工作表外的所有工作表
  for (let i = 1; i < allSheets.length; i++) {
    const destinationSheet = allSheets[i];
    const newRules = [];

    // 逐个处理每个条件格式规则，替换范围为目标工作表
    sourceRules.forEach(originalRule => {
      // 获取原规则的A1格式范围（不带工作表名）
      const sourceRangeA1 = originalRule.getRange().getA1Notation();
      // 在目标工作表中匹配相同的范围
      const targetRange = destinationSheet.getRange(sourceRangeA1);
      // 复制原规则的条件和格式，替换范围后生成新规则
      const newRule = originalRule.copy().setRange(targetRange).build();
      newRules.push(newRule);
    });

    // 将新规则批量应用到目标工作表
    destinationSheet.setConditionalFormatRules(newRules);
  }
}

代码逻辑说明 #

1. 先拿到当前表格的所有工作表，以及源工作表的条件格式规则列表。
2. 对每个目标工作表，我们不直接复用原规则数组，而是逐个复制规则：
   • 用originalRule.copy()复制原规则的所有条件（比如单元格值大于10）和格式设置（比如背景色变红）。
   • 用setRange(targetRange)把规则的应用范围替换成目标工作表的对应区域（和源工作表的单元格位置完全一致）。
   • 调用build()生成可应用的新规则，加入到新规则数组中。
3. 最后把新规则数组设置给目标工作表，这样就彻底避免了跨表引用的问题。

额外处理：带自定义公式的规则 #

如果你的条件格式里用了自定义公式，而且公式里硬编码了源工作表的名称（比如=Sheet1!B1>50），那上面的代码还需要加一段处理逻辑，把公式里的源工作表名替换成目标工作表名：

在sourceRules.forEach循环里，添加这段判断：

// 检查规则是否是基于自定义公式的布尔条件
if (originalRule.getBooleanCondition()) {
  const originalFormula = originalRule.getBooleanCondition().getCriteriaValues()[0];
  // 检查公式里是否包含源工作表名称
  if (typeof originalFormula === 'string' && originalFormula.includes(sourceSheet.getName())) {
    // 替换公式中的源工作表名为目标工作表名
    const updatedFormula = originalFormula.replace(
      new RegExp(sourceSheet.getName(), 'g'),
      destinationSheet.getName()
    );
    // 重新构建布尔条件
    const newCondition = SpreadsheetApp.newBooleanCondition()
      .setCriteria(originalRule.getBooleanCondition().getCriteriaType(), updatedFormula)
      .setBackground(originalRule.getBooleanCondition().getBackground())
      .setFontColor(originalRule.getBooleanCondition().getFontColor())
      // 可以根据需要添加其他格式属性
      .build();
    // 构建新规则并加入数组
    const formulaRule = SpreadsheetApp.newConditionalFormatRule()
      .withBooleanCondition(newCondition)
      .setRange(targetRange)
      .build();
    newRules.push(formulaRule);
    return; // 跳过默认的规则复制逻辑
  }
}

这样就能处理带跨表引用公式的条件格式规则了。

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