Google Sheets中高亮重叠时间及标记时间块可用性的实现方案

PART A：高亮存在时间重叠的整行 #

方案1：条件格式自定义公式 #

假设开始时间列是R，结束时间列是N，单日数据范围为第2行到第35行：

1. 选中需要高亮的整行范围（例如A2:Z35，覆盖所有数据列）
2. 打开「条件格式」→「添加规则」→「自定义公式」
3. 输入以下公式：

=SUMPRODUCT(($R$2:$R$35<$N2)*($N$2:$N$35>$R2))>1

4. 设置高亮样式（例如浅黄色背景）后保存

原理：通过SUMPRODUCT统计与当前行时间存在重叠的行数，若结果大于1则说明存在重叠（排除自身匹配）。

方案2：App Script脚本 #

1. 打开Google Sheet的「扩展程序」→「Apps脚本」
2. 粘贴以下代码：

function highlightOverlappingRows() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  // 读取开始时间（R列，第18列）和结束时间（N列，第14列），范围2-35行
  const timeData = sheet.getRange(2, 18, 34, 2).getValues();
  const allRowsRange = sheet.getRange(2, 1, 34, sheet.getLastColumn());
  const rowBackgrounds = allRowsRange.getBackgrounds();
  
  timeData.forEach((currentRow, idx) => {
    const currentStart = currentRow[0];
    const currentEnd = currentRow[1];
    let hasOverlap = false;
    
    // 遍历所有行检查重叠
    timeData.forEach((compareRow, cmpIdx) => {
      if (idx !== cmpIdx) {
        const cmpStart = compareRow[0];
        const cmpEnd = compareRow[1];
        if (currentStart < cmpEnd && currentEnd > cmpStart) {
          hasOverlap = true;
        }
      }
    });
    
    // 标记重叠行
    if (hasOverlap) {
      rowBackgrounds[idx].fill('#ffffcc'); // 可替换为你需要的高亮颜色
    }
  });
  
  allRowsRange.setBackgrounds(rowBackgrounds);
}

3. 点击运行授权后，即可执行脚本高亮重叠行；可设置「触发器」实现编辑后自动执行。

PART B：时间表高亮已用/可用时间段 #

方案1：条件格式自定义公式 #

假设时间表的时间标记在A列（例如A2:A25为每个时间块的起始时间），需要高亮的单元格在B列：

高亮已用时间段（灰色）

1. 选中B2:B25范围
2. 条件格式自定义公式：

=SUMPRODUCT(($R$2:$R$35<=A2)*($N$2:$N$35>A2))>0

3. 设置灰色背景样式

高亮可用时间段（绿色）

1. 选中B2:B25范围
2. 条件格式自定义公式：

=SUMPRODUCT(($R$2:$R$35<=A2)*($N$2:$N$35>A2))=0

3. 设置绿色背景样式

原理：检查当前时间块是否被任意登录时间段覆盖，通过SUMPRODUCT统计匹配数，判断是否标记。

方案2：App Script脚本 #

在之前的脚本基础上，添加时间表处理逻辑：

function highlightTimeSlots() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  // 读取登录时间数据
  const loginTimes = sheet.getRange(2, 18, 34, 2).getValues();
  // 读取时间表时间列（A列）和待高亮列（B列）
  const slotTimes = sheet.getRange(2, 1, sheet.getLastRow()-1, 1).getValues();
  const slotHighlightRange = sheet.getRange(2, 2, sheet.getLastRow()-1, 1);
  const slotBackgrounds = slotHighlightRange.getBackgrounds();
  
  slotTimes.forEach((slot, idx) => {
    const slotTime = slot[0];
    let isUsed = false;
    
    loginTimes.forEach((login) => {
      const loginStart = login[0];
      const loginEnd = login[1];
      if (loginStart <= slotTime && loginEnd > slotTime) {
        isUsed = true;
      }
    });
    
    // 设置背景色：灰色=已用，绿色=可用
    slotBackgrounds[idx][0] = isUsed ? '#e0e0e0' : '#b7e1cd';
  });
  
  slotHighlightRange.setBackgrounds(slotBackgrounds);
}

执行脚本后，时间表会自动标记已用/可用时间段。

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