修改Google Sheets脚本：实现蓝色行重复姓名高亮及跨表告警

修改后的脚本实现 #

function findDuplicateNames() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheetA1 = ss.getSheetByName("A1");
  const sheetA2 = ss.getSheetByName("A2");
  if (!sheetA1 || !sheetA2) {
    SpreadsheetApp.getUi().alert("未找到指定工作表A1或A2");
    return;
  }

  // A1需要检查的列标题
  const a1TargetHeaders = [
    "CONFERÊNCIA PÚBLICA - Central Águas Claras",
    "DATA",
    "PRESIDENTE",
    "ORADOR",
    "LEITORES DE A SENTINELA - Central Águas Claras",
    "LEITOR",
    "PARTES MECÂNICAS - Central Águas Claras",
    "1º",
    "2º",
    "DIREITA",
    "ESQUERDA",
    "AUDIO",
    "VIDEO"
  ];
  // A2需要检查的列标题
  const a2TargetHeaders = [
    "PRESIDENTE:",
    "TESOUROS DA PALAVRA DE DEUS",
    "FAÇA SEU MELHOR NO MINISTÉRIO",
    "NOSSA VIDA CRISTÃ"
  ];

  // 定位A1目标列索引
  const a1HeaderRow = sheetA1.getRange(1, 1, 1, sheetA1.getLastColumn()).getValues()[0];
  const a1TargetColumns = a1TargetHeaders.map(header => a1HeaderRow.indexOf(header) + 1).filter(col => col > 0);
  if (a1TargetColumns.length === 0) {
    SpreadsheetApp.getUi().alert("A1工作表未找到指定目标列");
    return;
  }

  // 提取A1蓝色高亮行的日期与姓名
  const a1DataRange = sheetA1.getRange(2, 1, sheetA1.getLastRow() - 1, sheetA1.getLastColumn());
  const a1Values = a1DataRange.getValues();
  const a1Backgrounds = a1DataRange.getBackgrounds();
  const targetBlue = "#4285F4"; // 可根据实际蓝色调整此值
  const a1Records = {};

  for (let row = 0; row < a1Values.length; row++) {
    const isRowBlue = a1Backgrounds[row].every(cellBg => cellBg === targetBlue);
    if (!isRowBlue) continue;

    const dataRow = row + 2;
    const dateValue = a1Values[row][a1HeaderRow.indexOf("DATA")];
    if (!dateValue) continue;

    a1TargetColumns.forEach(colIdx => {
      const col = colIdx - 1;
      if (a1HeaderRow[col] === "DATA") return;
      const name = a1Values[row][col];
      if (name) {
        if (!a1Records[dateValue]) a1Records[dateValue] = [];
        a1Records[dateValue].push({
          name: name.trim(),
          cellRef: `A1!${sheetA1.getRange(dataRow, colIdx).getA1Notation()}`
        });
      }
    });
  }

  // 定位A2目标蓝色列索引
  const a2HeaderRow = sheetA2.getRange(1, 1, 1, sheetA2.getLastColumn()).getValues()[0];
  const a2TargetColumns = a2TargetHeaders.map(header => a2HeaderRow.indexOf(header) + 1).filter(col => col > 0);
  if (a2TargetColumns.length === 0) {
    SpreadsheetApp.getUi().alert("A2工作表未找到指定目标列");
    return;
  }

  // 提取A2蓝色列的日期与姓名（假设日期在A2第一列，可根据实际调整）
  const a2DataRange = sheetA2.getRange(2, 1, sheetA2.getLastRow() - 1, sheetA2.getLastColumn());
  const a2Values = a2DataRange.getValues();
  const a2ColumnBackgrounds = [];
  for (let col = 0; col < a2HeaderRow.length; col++) {
    a2ColumnBackgrounds.push(sheetA2.getRange(2, col + 1).getBackground());
  }
  const a2Records = {};

  for (let row = 0; row < a2Values.length; row++) {
    const dataRow = row + 2;
    const dateValue = a2Values[row][0]; // 若日期列不在第一行，修改此处索引
    if (!dateValue) continue;

    a2TargetColumns.forEach(colIdx => {
      const col = colIdx - 1;
      if (a2ColumnBackgrounds[col] !== targetBlue) return;
      const name = a2Values[row][col];
      if (name) {
        if (!a2Records[dateValue]) a2Records[dateValue] = [];
        a2Records[dateValue].push({
          name: name.trim(),
          cellRef: `A2!${sheetA2.getRange(dataRow, colIdx).getA1Notation()}`
        });
      }
    });
  }

  // 查找重复并处理
  const duplicateAlerts = [];
  const redColor = "#FF0000";

  // 遍历日期分组检查重复
  Object.keys(a1Records).forEach(date => {
    if (!a2Records[date]) return;

    const a1Names = a1Records[date];
    const a2Names = a2Records[date];

    // 检查A1内部重复
    const a1NameMap = new Map();
    a1Names.forEach(record => {
      a1NameMap.has(record.name) ? a1NameMap.get(record.name).push(record) : a1NameMap.set(record.name, [record]);
    });
    a1NameMap.forEach((records, name) => {
      if (records.length > 1) {
        records.forEach(r => ss.getRange(r.cellRef).setBackground(redColor));
        duplicateAlerts.push(`日期 ${date} 下姓名 ${name} 在A1中重复，位置：${records.map(r => r.cellRef).join(", ")}`);
      }
    });

    // 检查A2内部重复
    const a2NameMap = new Map();
    a2Names.forEach(record => {
      a2NameMap.has(record.name) ? a2NameMap.get(record.name).push(record) : a2NameMap.set(record.name, [record]);
    });
    a2NameMap.forEach((records, name) => {
      if (records.length > 1) {
        records.forEach(r => ss.getRange(r.cellRef).setBackground(redColor));
        duplicateAlerts.push(`日期 ${date} 下姓名 ${name} 在A2中重复，位置：${records.map(r => r.cellRef).join(", ")}`);
      }
    });

    // 检查跨表重复
    const a1NameSet = new Set(a1Names.map(r => r.name));
    a2Names.forEach(a2Record => {
      if (a1NameSet.has(a2Record.name)) {
        const matchedA1 = a1Names.filter(r => r.name === a2Record.name);
        matchedA1.forEach(r => ss.getRange(r.cellRef).setBackground(redColor));
        ss.getRange(a2Record.cellRef).setBackground(redColor);
        duplicateAlerts.push(`日期 ${date} 下姓名 ${a2Record.name} 跨表重复，位置：${matchedA1.map(r => r.cellRef).join(", ")} 和 ${a2Record.cellRef}`);
      }
    });
  });

  // 弹出告警
  if (duplicateAlerts.length > 0) {
    SpreadsheetApp.getUi().alert("发现重复姓名：\n\n" + duplicateAlerts.join("\n\n"));
  } else {
    SpreadsheetApp.getUi().alert("未发现重复姓名");
  }
}

修改脚本需遵循的语法格式要求 #

• 命名规范：变量与函数采用驼峰命名法（如findDuplicateNames、sheetA1），避免中文及特殊字符，命名需清晰表意。
• Spreadsheet服务规范：必须通过SpreadsheetApp.getActiveSpreadsheet()获取当前表格实例，操作工作表前需判断是否存在，避免空指针错误。
• 颜色处理：使用十六进制颜色代码（如#4285F4），需与表格中实际高亮颜色完全匹配，可通过getBackground()方法确认颜色值。
• 性能优化：优先使用批量读取方法（如getValues()、getBackgrounds()），减少单次单元格读写操作，提升脚本执行效率。
• 日期匹配：确保日期格式统一，若存在格式差异需转换为Date对象后再对比。
• 错误处理：添加关键节点的错误判断（如工作表不存在、目标列未找到），提前终止脚本并给出明确提示。
• 注释规范：对核心逻辑（如目标列定义、重复检查规则）添加注释，便于后续维护。
• 告警格式：弹窗提示需包含重复姓名、日期及单元格位置，便于快速定位问题。

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