Google Apps Script预约排班脚本执行后未生效问题求助

问题背景 #

• 基于Google Sheets搭建关联Google表单的预约排班系统，表单用于收集用户预约提交信息
• 预期效果：用户在表单选定预约时段后，周排班表中对应时段、匹配对应医生（原单元格标注A医生/B医生等）的位置自动更新为Booked状态
• 故障现象：编写的脚本运行后提示执行完成，但工作表无任何变更，未达到预期更新效果

原实现代码 #

function booking() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var cusRec = ss.getSheetByName("Customer Record");
  var appSheet = ss.getSheetByName("Appointment sheet for psychologists");
  
  var cusDate = cusRec.getRange(2,7).getValue();
  var cusTime = cusRec.getRange(2,8).getValue();
  var cusPsy = cusRec.getRange(2,9).getValue();

  var appDate = appSheet.getRange(3,2).getValue();
  var appTime = appSheet.getRange(5,1).getValue();
  var appPsy = appSheet.getRange(5,2).getValue();  

  if (cusDate == appDate && cusTime == appTime && cusPsy == appPsy)
  {
    appSheet.getRange("B5").setValue("Booked");
  }
}

参考资料 #

两个工作表结构参考截图

故障原因 #

• 预约数据读取位置硬编码：Google表单关联的工作表，新提交内容会自动追加到表尾，原代码固定读取第2行数据，无法获取最新提交的预约信息
• 排班匹配逻辑硬编码：原代码固定读取排班表B3（日期）、A5（时段）、B5（医生）三个固定单元格做比对，没有遍历排班表全量的日期、时段、医生单元格做动态匹配，只要预约信息不刚好和这三个固定值完全一致，判断条件就不会成立，不会执行写入操作
• 数据类型匹配隐患：表格中存储的日期、时间为Date对象格式，直接用==比对容易因为时区、时间精度、单元格格式差异导致判断失效

修复方案 #

替换为以下可运行代码，注意根据自己排班表的实际行列位置调整遍历起始参数：

function booking() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const cusRec = ss.getSheetByName("Customer Record");
  const appSheet = ss.getSheetByName("Appointment sheet for psychologists");
  
  // 读取最新一条预约记录（表单新提交内容在最后一行）
  const lastCusRow = cusRec.getLastRow();
  const cusDate = cusRec.getRange(lastCusRow, 7).getValue();
  const cusTime = cusRec.getRange(lastCusRow, 8).getDisplayValue();
  const cusPsy = cusRec.getRange(lastCusRow, 9).getDisplayValue();

  // 读取排班表范围参数，可根据自己表格实际结构调整
  const dateStartCol = 2; // 日期从B列开始
  const dateRow = 3; // 日期在第3行
  const timeStartRow = 5; // 时段从第5行开始
  const timeCol = 1; // 时段在A列
  const lastAppCol = appSheet.getLastColumn();
  const lastAppRow = appSheet.getLastRow();

  // 获取所有排班日期
  const dateList = appSheet.getRange(dateRow, dateStartCol, 1, lastAppCol - dateStartCol + 1).getValues()[0];
  // 获取所有排班时段
  const timeList = appSheet.getRange(timeStartRow, timeCol, lastAppRow - timeStartRow + 1, 1).getValues();

  // 遍历匹配日期
  for (let colOffset = 0; colOffset < dateList.length; colOffset++) {
    // 日期转时间戳比对，消除格式误差
    if (new Date(dateList[colOffset]).getTime() !== new Date(cusDate).getTime()) continue;
    // 匹配到日期后遍历对应列的时段和医生
    for (let rowOffset = 0; rowOffset < timeList.length; rowOffset++) {
      const currentTime = appSheet.getRange(timeStartRow + rowOffset, timeCol).getDisplayValue();
      const currentPsy = appSheet.getRange(timeStartRow + rowOffset, dateStartCol + colOffset).getDisplayValue();
      if (currentTime === cusTime && currentPsy === cusPsy) {
        appSheet.getRange(timeStartRow + rowOffset, dateStartCol + colOffset).setValue("Booked");
        return;
      }
    }
  }
}

配置提示：在Apps Script编辑器中给该函数绑定「表单提交」可安装触发器，后续每次有用户提交新预约，脚本会自动运行更新排班状态，无需手动执行。

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