如何检查同一目录下多个Google Sheets工作簿的重复数据并高亮

以下是修改后的脚本，可实现检查当前编辑单元格的值是否在同一目录下的其他Google Sheets工作簿中存在重复，并高亮相关单元格：

function checkCrossWorkbookDuplicates(e) {
  // 获取当前编辑的核心信息
  const currentSpreadsheet = e.source;
  const activeSheet = currentSpreadsheet.getActiveSheet();
  const editedRange = e.range;
  const editedValue = editedRange.getValue();
  
  // 空值不进行查重
  if (editedValue === "") {
    editedRange.setBackground(null);
    return;
  }

  // --------------------------
  // 1. 保留原有的同工作表查重逻辑
  // --------------------------
  const sheetData = activeSheet.getDataRange().getValues();
  const editedRow = editedRange.getRow() - 1;
  const editedCol = editedRange.getColumn() - 1;
  let hasSheetDuplicate = false;

  for (let i = 0; i < sheetData.length; i++) {
    for (let j = 0; j < sheetData[i].length; j++) {
      if (i === editedRow && j === editedCol) continue;
      if (sheetData[i][j] === editedValue) {
        activeSheet.getRange(i + 1, j + 1).setBackground("#ff0000");
        hasSheetDuplicate = true;
      }
    }
  }

  // --------------------------
  // 2. 新增跨同目录工作簿查重逻辑
  // --------------------------
  let hasCrossDuplicate = false;
  try {
    // 获取当前工作簿所在的父文件夹
    const currentFile = DriveApp.getFileById(currentSpreadsheet.getId());
    const parentFolders = currentFile.getParents();
    if (!parentFolders.hasNext()) {
      console.log("当前工作簿未在任何文件夹中");
      return;
    }
    const targetFolder = parentFolders.next();

    // 搜索文件夹下所有Google Sheets文件（排除当前工作簿）
    const sheetFiles = targetFolder.searchFiles(
      "mimeType='application/vnd.google-apps.spreadsheet' and not title contains '" + currentSpreadsheet.getName() + "'"
    );

    // 遍历每个外部工作簿
    while (sheetFiles.hasNext()) {
      const file = sheetFiles.next();
      // 跳过当前工作簿（避免误判）
      if (file.getId() === currentSpreadsheet.getId()) continue;

      const externalSpreadsheet = SpreadsheetApp.open(file);
      // 遍历工作簿内所有工作表
      const sheets = externalSpreadsheet.getSheets();
      for (const sheet of sheets) {
        const externalData = sheet.getDataRange().getValues();
        // 遍历工作表内所有单元格
        for (let i = 0; i < externalData.length; i++) {
          for (let j = 0; j < externalData[i].length; j++) {
            if (externalData[i][j] === editedValue) {
              // 高亮外部工作簿中的重复单元格
              sheet.getRange(i + 1, j + 1).setBackground("#ff0000");
              hasCrossDuplicate = true;
            }
          }
        }
      }
    }

    // 高亮当前编辑的单元格（如果存在跨工作簿或同表重复）
    if (hasCrossDuplicate || hasSheetDuplicate) {
      editedRange.setBackground("#ff0000");
    } else {
      editedRange.setBackground(null);
    }
  } catch (error) {
    console.error("查重过程中出现错误:", error);
    editedRange.setBackground("#ffff00"); // 用黄色标记错误
  }
}

关键修改说明 #

• 权限适配：原onEdit是简单触发器，无权限访问Drive和外部工作簿，因此改用可安装触发器（需手动创建）
• 目录遍历：通过DriveApp获取当前工作簿所在文件夹，搜索所有同类型工作簿
• 重复标记：同时保留同工作表查重逻辑，并新增跨工作簿查重，找到重复后同时标记原单元格和外部重复单元格
• 异常处理：添加错误捕获，出现权限或文件访问问题时用黄色标记编辑单元格

配置步骤 #

1. 打开你的Google Sheet，点击「扩展程序」→「Apps脚本」
2. 删除原有代码，粘贴上述脚本
3. 点击「保存」，命名项目（比如CrossWorkbookDuplicateChecker）
4. 创建可安装触发器：
   • 点击左侧「触发器」图标（⏱️）
   • 点击「添加触发器」
   • 选择：
     • 选择要运行的函数：checkCrossWorkbookDuplicates
     • 选择部署类型：「头部部署」
     • 选择事件源：「从电子表格」
     • 选择事件类型：「编辑时」
   • 点击「保存」，按提示完成授权（需允许脚本访问Drive和Sheets）

优化建议 #

• 限定检查列：如果只需要检查特定列（比如A列），可在遍历数据时添加列判断，减少遍历量
• 大小写不敏感：若需忽略大小写，可将值转为小写后比较：externalData[i][j].toString().toLowerCase() === editedValue.toString().toLowerCase()
• 性能优化：如果目录下工作簿或数据量极大，可考虑使用getValues()一次性获取数据后批量处理，避免频繁API调用

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