如何在Google Sheets日程中用条件格式或脚本高亮重叠参与者？

一、条件格式自动高亮重叠参与者 #

假设表格列对应：A列=时间，B列=活动，C列=参与者，D列=场地，按以下步骤操作：

• 选中参与者列的所有数据行（比如C2:C，从第二行开始，避开表头）
• 点击顶部菜单「格式」→「条件格式」
• 在格式规则里选择「自定义公式」，粘贴以下公式：
  =SUMPRODUCT(($A$2:$A$100>=A2)*($A$2:$A$100<(A2+TIME(1,0,0)))*ISNUMBER(SEARCH(TRIM(MID(SUBSTITUTE(C2," ",REPT(" ",99)),(ROW(INDIRECT("1:"&LEN(C2)-LEN(SUBSTITUTE(C2," ",""))+1))-1)*99+1,99)),$C$2:$C$100))>COUNTIF(C2,"*"&TRIM(MID(SUBSTITUTE(C2," ",REPT(" ",99)),(ROW(INDIRECT("1:"&LEN(C2)-LEN(SUBSTITUTE(C2," ",""))+1))-1)*99+1,99))&"*")
  • 说明：公式默认按1小时时间段检测重叠，若活动时长不同，把TIME(1,0,0)改成对应时长，比如半小时就写TIME(0,30,0)
• 设置高亮格式（比如浅红色填充），点击「完成」即可，之后只要有参与者时间重叠，对应单元格会自动高亮

二、按钮触发脚本检查重叠 #

如果偏好手动触发检查而非实时高亮，按以下步骤操作：

• 点击顶部菜单「扩展程序」→「Apps Script」
• 删除默认代码，粘贴以下脚本：

function checkOverlaps() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = sheet.getDataRange().getValues();
  const timeCol = 0; // A列对应索引0，时间列
  const participantCol = 2; // C列对应索引2，参与者列
  const currentDuration = 1 * 60 * 60 * 1000; // 默认1小时，单位毫秒，改时长直接修改此处数值
  
  // 清除之前的高亮标记
  sheet.getRange(2, participantCol+1, data.length-1, 1).clearFormat();
  
  // 遍历每行数据查找重叠
  for (let i = 1; i < data.length; i++) {
    const currentTime = new Date(data[i][timeCol]);
    const currentParticipants = data[i][participantCol].split(" ").filter(p => p !== "");
    
    for (let j = 1; j < data.length; j++) {
      if (i === j) continue;
      const compareTime = new Date(data[j][timeCol]);
      // 判断时间是否重叠
      if (!(currentTime >= compareTime + currentDuration || compareTime >= currentTime + currentDuration)) {
        const compareParticipants = data[j][participantCol].split(" ").filter(p => p !== "");
        const overlaps = currentParticipants.filter(p => compareParticipants.includes(p));
        if (overlaps.length > 0) {
          // 高亮存在重叠的参与者单元格
          sheet.getRange(i+1, participantCol+1).setBackground("#ffcccc");
          sheet.getRange(j+1, participantCol+1).setBackground("#ffcccc");
        }
      }
    }
  }
}

• 保存脚本，命名为「OverlapChecker」
• 返回表格，点击「插入」→「绘图」，绘制一个按钮（比如矩形，输入文字“检查重叠”），保存后点击该按钮，选择「分配脚本」，输入checkOverlaps并确定
• 后续点击这个按钮，就会自动检查所有行，把存在时间重叠的参与者单元格标红

注意事项 #

• 脚本里的timeCol、participantCol要和实际列对应，比如参与者列是D列就改成3
• 活动时长不是1小时的话，修改currentDuration数值，比如半小时就是0.5 * 60 * 60 * 1000

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