Google Apps Script：查找排班范围中指定员工的排班日期与地点

方法1：使用Google Sheets数组公式（无需脚本） #

假设你的排班数据结构如下：

• A列是日期（行标签，从A2开始）
• B1、C1...是地点名称（列标题）
• B2及以下对应各地点的排班员工数据

要查询员工John Doe的排班记录，可在空白单元格输入以下公式：

=ARRAYFORMULA(QUERY(SPLIT(FLATTEN(A2:A&"|"&B1:1&"|"&B2:B),"|"),"select Col1,Col2 where Col3 = 'John Doe'"))

公式逻辑： #

1. FLATTEN(A2:A&"|"&B1:1&"|"&B2:B)：将每行的「日期+地点+员工」合并为带分隔符的字符串，再平铺成单列
2. SPLIT(..., "|")：把平铺后的字符串拆分为三列（日期、地点、员工）
3. QUERY(..., "select Col1,Col2 where Col3 = 'John Doe'")：筛选出员工匹配的记录，仅保留日期和地点列

方法2：使用Apps Script脚本（灵活可扩展） #

如果需要和你的日历同步逻辑整合，或者处理大规模数据，脚本方案更适配。以下是可直接使用的函数：

function getEmployeeShifts(employeeName = "John Doe") {
  const activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sourceSheet = activeSpreadsheet.getActiveSheet();
  const allData = sourceSheet.getDataRange().getValues();
  
  // 提取地点列标题（排除第一列的日期标题）
  const locationHeaders = allData[0].slice(1);
  const matchedShifts = [];

  // 遍历所有日期行（跳过第一行标题）
  for (let rowIndex = 1; rowIndex < allData.length; rowIndex++) {
    const currentRow = allData[rowIndex];
    const currentDate = currentRow[0];

    // 遍历每个地点列（从第二列开始）
    for (let colIndex = 1; colIndex < currentRow.length; colIndex++) {
      const assignedEmployee = currentRow[colIndex];
      if (assignedEmployee === employeeName) {
        matchedShifts.push([currentDate, locationHeaders[colIndex - 1]]);
      }
    }
  }

  // 将结果写入新工作表（不存在则创建）
  const outputSheet = activeSpreadsheet.getSheetByName("员工排班记录") || activeSpreadsheet.insertSheet("员工排班记录");
  outputSheet.clearContents();
  if (matchedShifts.length > 0) {
    outputSheet.getRange(1, 1, matchedShifts.length, 2).setValues(matchedShifts);
  }
  
  // 返回结果数组，可直接传给你的日历同步函数
  return matchedShifts;
}

使用步骤： #

1. 在Google Sheets中打开「扩展程序」→「Apps脚本」
2. 粘贴上述代码并保存项目
3. 运行函数时可传入目标员工姓名，默认值为John Doe
4. 执行后会自动创建（或复用）名为「员工排班记录」的工作表，输出匹配的日期和地点列表

注意事项 #

• 公式法可将固定范围改为整列/整行（如A2:A、B1:1），适配动态更新的数据，但需注意空行/空列的干扰
• 脚本首次运行需完成权限授权，按提示操作即可

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