Google表格时间戳脚本在菲律宾区域部分功能失效求助

问题背景 #

我们开发了一款任务追踪Google表格，通过脚本在勾选复选框时自动生成时间戳，用于计算特定客户/供应商的任务平均完成时长（商业工具无法满足同时运行多个计时器的需求）。当前脚本如下：

function onEdit() {   
  var s = SpreadsheetApp.getActiveSheet();
  if( s.getName() == "Rep" ) { 
  var r = s.getActiveCell();       

  if( r.getColumn() == 1 ) { //This is for the checkbox in A for Timestamp in B
    var nextCell = r.offset(0, 1);
  if( r.getValue() === true)
    nextCell.setValue(new Date()).setNumberFormat("MM/dd/yyyy hh:mm");
    
  else nextCell.clearContent()
};  
    if( r.getColumn() == 9 ) { //This is for the checkbox in I for Timestamp in J
        var nextCell = r.offset(0, 1);
    if( r.getValue() === true)
    nextCell.setValue(new Date()).setNumberFormat("MM/dd/yyyy hh:mm");
    else nextCell.clearContent()
    };
    }
    }

美东时区用户使用时，B列和J列时间戳生成正常，但菲律宾团队成员点击I列复选框时，J列无法生成时间戳，尝试设置时区格式（setNumberFormat("UTC -7 MM/dd/yyyy hh:mm")）失败。

问题排查与修复方案 #

1. 脚本鲁棒性修复 #

原脚本依赖getActiveCell()，在批量编辑或权限受限场景下容易失效，改用事件对象e的版本更可靠：

function onEdit(e) {
  // 仅处理指定工作表
  const targetSheet = e.source.getActiveSheet();
  if (targetSheet.getName() !== "Rep") return;

  const editedCol = e.range.getColumn();
  const targetCell = e.range.offset(0, 1);

  // 统一处理复选框触发逻辑
  const handleTimestamp = (isChecked) => {
    if (isChecked) {
      const timestamp = new Date();
      targetCell.setValue(timestamp);
      // 用表格默认时区格式化，避免跨时区显示混乱
      targetCell.setNumberFormat("MM/dd/yyyy hh:mm");
    } else {
      targetCell.clearContent();
    }
  };

  // A列复选框触发B列时间戳
  if (editedCol === 1) {
    handleTimestamp(e.value === "TRUE");
  }
  // I列复选框触发J列时间戳
  if (editedCol === 9) {
    handleTimestamp(e.value === "TRUE");
  }
}

2. 时区问题修正 #

你之前的时区格式语法错误，正确的设置方式：

• 打开表格「文件」→「设置」，将时区统一设置为美东时区（America/New_York），确保所有用户看到的时间戳一致
• 如果需要强制指定时区格式化，使用标准格式：targetCell.setNumberFormat('[GMT-4]MM/dd/yyyy hh:mm')（美东夏令时为UTC-4，冬令时为UTC-5）

3. 权限问题排查 #

如果菲律宾用户仍无法触发脚本：

• 确认用户拥有表格的编辑权限（至少能编辑I列）
• 将简单触发改为可安装触发：
  1. 打开脚本编辑器
  2. 点击「编辑」→「当前项目的触发器」
  3. 添加新触发：选择onEdit函数，事件类型为「电子表格」→「编辑」，触发时机为「从电子表格提交」

多任务计时与统计优化建议 #

针对同时运行多个计时器的需求，可做以下优化：

• 允许添加多行任务条目，支持同时勾选多个任务的开始/结束复选框
• 增加「客户」「供应商」「任务类型」分类列，用函数自动计算平均时长：
  示例：计算某客户的任务平均完成时长

  =AVERAGEIFS(J:J, C:C, "客户名称", A:A, TRUE, I:I, TRUE)
  

  注：J列为结束时间，B列为开始时间，可先计算单任务时长=J2-B2，再用AVERAGEIF统计平均值

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