Google Sheets赛事对阵表条件格式迁移及规则优化技术求助

问题背景 #

我和妻子共用一份Google Sheets制作的32队赛事对阵表，通过条件格式判断赛事结果猜测是否正确：

• 正确猜测：用公式=MATCH(B5, INDIRECT("S6 Official!B5"), 0)标记绿色背景
• 错误猜测：采用类似逻辑标记对应格式
  整个对阵表需要设置62条独立的条件格式规则。

现在要切换到S7赛季，手动修改所有规则过于繁琐，尝试用App Script实现自动化迁移，已编写部分代码提取原规则并将公式中的"S6"替换为"S7"，但不知道如何将处理后的字符串数组转换为ConditionalFormatRules数组并应用到新工作表；同时希望了解是否可以将全表62条规则简化为「正确」「错误」两条通用规则。

现有代码 #

function setFormat() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Name S6');
  var sheet2 = ss.getSheetByName('Name S7');

  var rules = sheet.getConditionalFormatRules();
  var rules2 = []
  for (i=0; i < rules.length; i++) {
    var bool = rules[i].getBooleanCondition();
    var rule = bool.getCriteriaValues();
    rule = rule.toString().replace("S6", "S7");
    rule = rule.toString().replace("S6", "S7");
    rules2.push(rule);
  }
}

解决方案 #

一、自动化迁移规则的修正代码 #

原代码仅提取并修改了条件公式的字符串值，但未重新构建完整的ConditionalFormatRule对象。以下是修复后的代码，可直接将原S6表的条件格式规则批量迁移并替换为S7：

function migrateConditionalFormatRules() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sourceSheet = ss.getSheetByName('Name S6');
  const targetSheet = ss.getSheetByName('Name S7');
  
  if (!sourceSheet || !targetSheet) {
    SpreadsheetApp.getUi().alert('指定工作表不存在，请检查名称');
    return;
  }

  const sourceRules = sourceSheet.getConditionalFormatRules();
  const targetRules = [];

  sourceRules.forEach(rule => {
    const booleanCondition = rule.getBooleanCondition();
    if (!booleanCondition) return; // 跳过非布尔条件的规则（如数据条等）

    // 获取原规则的条件类型、格式、范围
    const criteriaType = booleanCondition.getCriteriaType();
    const format = booleanCondition.getFormat();
    const ranges = rule.getRanges();

    // 获取并替换公式中的S6为S7
    let criteriaValues = booleanCondition.getCriteriaValues();
    criteriaValues = criteriaValues.map(value => {
      if (typeof value === 'string') {
        return value.replace(/S6/g, 'S7');
      }
      return value;
    });

    // 重新创建布尔条件
    const newBooleanCondition = SpreadsheetApp.newBooleanCondition()
      .setCriteria(criteriaType, criteriaValues)
      .setBackground(format.getBackground())
      .setFontColor(format.getFontColor())
      // 可根据需要添加其他格式设置（如字体样式等）
      .build();

    // 创建新的条件格式规则并添加到目标数组
    const newRule = SpreadsheetApp.newConditionalFormatRule()
      .withBooleanCondition(newBooleanCondition)
      .setRanges(ranges)
      .build();

    targetRules.push(newRule);
  });

  // 将新规则应用到目标工作表（覆盖原有规则）
  targetSheet.setConditionalFormatRules(targetRules);
}

二、简化为两条通用规则 #

完全不需要62条独立规则，利用相对引用公式可实现全表覆盖，仅需两条规则：

1. 正确猜测规则

• 应用范围：选择所有需要判断的猜测单元格（如B5:XX66，根据实际数据范围调整）
• 条件类型：自定义公式
• 公式：=MATCH(INDIRECT(ADDRESS(ROW(), COLUMN())), INDIRECT("S7 Official!"&ADDRESS(ROW(), COLUMN())), 0)
• 格式设置：绿色背景（或其他你需要的正确标记格式）

2. 错误猜测规则

• 应用范围：与正确规则相同
• 条件类型：自定义公式
• 公式：=NOT(ISNUMBER(MATCH(INDIRECT(ADDRESS(ROW(), COLUMN())), INDIRECT("S7 Official!"&ADDRESS(ROW(), COLUMN())), 0)))
• 格式设置：红色背景（或其他错误标记格式）

注意：在条件格式面板中，需将「正确规则」放在「错误规则」上方，因为条件格式会按顺序匹配，匹配到正确规则后就不会再判断错误规则。

内容的提问来源于stack exchange，提问作者B.A. Ceradsky