求Google Sheets Apps Script：自动计时及暂停恢复功能

实现步骤与代码 #

1. 完整脚本代码 #

打开Google Sheets，点击「扩展程序」→「Apps 脚本」，粘贴以下代码并保存：

function onEdit(e) {
  const sheet = e.source.getActiveSheet();
  const range = e.range;
  const row = range.getRow();
  const col = range.getColumn();
  
  // 跳过表头行（假设第1行是表头）
  if (row <= 1) return;

  // 处理B列输入：记录开始时间到I列
  if (col === 2 && range.getValue() !== "") {
    const startTimeCell = sheet.getRange(row, 9); // I列是第9列
    if (startTimeCell.getValue() === "") {
      startTimeCell.setValue(new Date());
      startTimeCell.setNumberFormat("yyyy-MM-dd HH:mm:ss");
    }
  }

  // 处理H列状态选择
  if (col === 8) {
    const status = range.getValue().toLowerCase();
    const startTime = sheet.getRange(row, 9).getValue();
    const endTimeCell = sheet.getRange(row, 10); // J列是第10列
    const totalTimeCell = sheet.getRange(row, 11); // K列是第11列
    const suspendStartCell = sheet.getRange(row, 12); // L列存暂停开始时间
    const totalSuspendCell = sheet.getRange(row, 13); // M列存总暂停时长

    switch(status) {
      case "done":
        // 记录结束时间
        if (endTimeCell.getValue() === "") {
          endTimeCell.setValue(new Date());
          endTimeCell.setNumberFormat("yyyy-MM-dd HH:mm:ss");
        }
        // 计算总处理时长（结束-开始-总暂停时长）
        if (startTime !== "") {
          const endTime = endTimeCell.getValue();
          const totalSuspend = totalSuspendCell.getValue() || 0;
          const totalMs = endTime.getTime() - startTime.getTime() - totalSuspend;
          // 转换为小时:分钟:秒格式
          const hours = Math.floor(totalMs / 3600000);
          const minutes = Math.floor((totalMs % 3600000) / 60000);
          const seconds = Math.floor((totalMs % 60000) / 1000);
          totalTimeCell.setValue(`${hours}:${minutes.toString().padStart(2, '0')}:${seconds.toString().padStart(2, '0')}`);
        }
        break;
      case "suspend":
        // 记录暂停开始时间（仅当未处于暂停状态时）
        if (suspendStartCell.getValue() === "") {
          suspendStartCell.setValue(new Date());
          suspendStartCell.setNumberFormat("yyyy-MM-dd HH:mm:ss");
        }
        break;
      case "resume":
        // 计算本次暂停时长并累加到总暂停时长
        const suspendStart = suspendStartCell.getValue();
        if (suspendStart !== "") {
          const suspendEnd = new Date();
          const suspendMs = suspendEnd.getTime() - suspendStart.getTime();
          const currentTotalSuspend = totalSuspendCell.getValue() || 0;
          totalSuspendCell.setValue(currentTotalSuspend + suspendMs);
          // 清空暂停开始时间
          suspendStartCell.clearContent();
        }
        break;
      case "pend":
        // 无操作，保持计时状态
        break;
    }
  }
}

// 初始化H列下拉选项（首次运行一次即可）
function initStatusDropdown() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const range = sheet.getRange("H2:H"); // 从第2行开始的H列
  const rule = SpreadsheetApp.newDataValidation()
    .requireValueInList(["done", "pend", "suspend", "resume"], true)
    .setAllowInvalid(false)
    .build();
  range.setDataValidation(rule);
}

2. 脚本使用说明 #

• 初始化下拉选项：首次使用时，在Apps脚本编辑器中点击「运行」→ 选择initStatusDropdown函数，授权后即可为H列添加指定的下拉选项。
• 自动触发逻辑：
  • 当B列某行输入内容时，若对应I列无数据，自动写入当前时间作为开始时间。
  • 选择H列的suspend时，L列会记录暂停开始时间；选择resume时，会计算本次暂停时长并累加到M列的总暂停时长。
  • 选择done时，J列记录结束时间，K列自动计算总处理时长（结束时间 - 开始时间 - 所有暂停时长的总和），格式为时:分:秒。
  • 选择pend时无任何操作，计时继续。
• 隐藏辅助列：L列（暂停开始时间）和M列（总暂停时长）为辅助列，可右键点击列标选择「隐藏列」，避免干扰视图。

3. 注意事项 #

• 脚本依赖Google Sheets的简单触发器onEdit，无需手动设置触发器，编辑表格时自动触发。
• 时间格式可根据需求修改setNumberFormat中的参数，比如改为"HH:mm:ss"仅显示时分秒。
• 若需要重复编辑B列不覆盖已有的开始时间，脚本已做判断，仅当I列为空时才写入。

内容的提问来源于stack exchange，提问作者t-max