Google Sheets任务流程开发：步骤依赖、时间戳记录及耗时计算

需求概述 #

• 搭建包含10个步骤的任务流程：提交新表单条目后可启动步骤1、2、3；完成步骤3后方可启动步骤4、5、6；完成步骤6后方可启动步骤7、8、9、10，以此类推
• 通过onEdit触发器实现以下功能：
  • 步骤权限控制：仅当前序阶段的所有步骤完成时，用户才能编辑后续阶段的步骤
  • 时间戳记录：所有步骤的完成操作自动记录时间
  • 任务统计：计算单个任务的总完成天数，以及超出预期时长的延迟天数

现有代码局限 #

当前脚本仅针对列45和51做了简单的依赖控制与时间戳记录，未覆盖10个步骤的完整分组逻辑，也缺少总完成天数和延迟时长的计算功能。

完善后的完整代码 #

function onEdit(e) {
  const sheet = e.range.getSheet();
  const sheetName = "Not Open";
  const editedRow = e.range.getRow();
  const editedCol = e.range.getColumn();
  const startRow = 8; // 数据起始行

  // 跳过表头和非目标工作表
  if (sheet.getName() !== sheetName || editedRow < startRow) return;

  // 步骤分组配置：key为阶段，value包含前置完成列、当前阶段的步骤列
  const stepGroups = {
    stage1: { prevCompleteCol: null, stepCols: [43, 44, 45] }, // 步骤1-3，无前置
    stage2: { prevCompleteCol: 45, stepCols: [49, 50, 51] }, // 步骤4-6，前置为步骤3（列45）
    stage3: { prevCompleteCol: 51, stepCols: [55, 56, 57, 58] } // 步骤7-10，前置为步骤6（列51）
  };

  // 检查当前编辑的列属于哪个阶段
  let currentStage = null;
  for (const stage in stepGroups) {
    if (stepGroups[stage].stepCols.includes(editedCol)) {
      currentStage = stepGroups[stage];
      break;
    }
  }

  // 验证前置步骤是否完成
  if (currentStage && currentStage.prevCompleteCol) {
    const prevStepCompleted = sheet.getRange(editedRow, currentStage.prevCompleteCol).getValue();
    if (!prevStepCompleted) {
      Browser.msgBox("需完成前置步骤后，才能操作当前阶段任务");
      e.range.setValue(e.oldValue); // 回滚编辑
      return;
    }
  }

  // 记录时间戳
  addTimestamp(e, editedCol);

  // 计算总完成天数与延迟时长
  calculateTaskStats(sheet, editedRow);
}

function addTimestamp(e, editedCol) {
  const sheet = e.range.getSheet();
  const row = e.range.getRow();
  const sheetName = "Not Open";
  if (sheet.getName() !== sheetName) return;

  // 步骤列与时间戳列映射：步骤列 → 对应时间戳列
  const timestampMap = {
    43: 42, 44: 43, 45: 44, // 步骤1-3的时间戳列
    49: 48, 50: 49, 51: 50, // 步骤4-6的时间戳列
    55: 54, 56: 55, 57: 56, 58: 57 // 步骤7-10的时间戳列
  };

  const targetTimestampCol = timestampMap[editedCol];
  if (targetTimestampCol) {
    sheet.getRange(row, targetTimestampCol).setValue(new Date());
  }
}

function calculateTaskStats(sheet, row) {
  const submitDateCol = 2; // 表单提交日期列（需根据实际调整）
  const lastStepCol = 58; // 最后一步（步骤10）的列
  const expectedDaysCol = 3; // 预期完成天数列（需根据实际调整）
  const totalDaysCol = 59; // 总完成天数存储列
  const delayDaysCol = 60; // 延迟天数存储列

  const submitDate = sheet.getRange(row, submitDateCol).getValue();
  const lastStepDone = sheet.getRange(row, lastStepCol).getValue();
  const expectedDays = sheet.getRange(row, expectedDaysCol).getValue();

  if (submitDate && lastStepDone) {
    const totalDays = (new Date() - submitDate) / (1000 * 60 * 60 * 24);
    sheet.getRange(row, totalDaysCol).setValue(Math.round(totalDays * 100) / 100); // 保留两位小数

    if (expectedDays) {
      const delayDays = Math.max(0, totalDays - expectedDays);
      sheet.getRange(row, delayDaysCol).setValue(Math.round(delayDays * 100) / 100);
    }
  }
}

功能说明 #

1. 步骤依赖控制

   • 通过stepGroups配置阶段分组，明确每个阶段的前置完成条件
   • 用户尝试编辑未满足前置条件的步骤时，会弹出提示并回滚操作

2. 时间戳自动记录

   • 通过timestampMap定义每个步骤列对应的时间戳存储列
   • 编辑步骤完成状态时，自动在对应列写入当前时间

3. 任务统计计算

   • 总完成天数：用当前时间减去表单提交日期，转换为天数（可根据需求改为最后一步完成时间）
   • 延迟时长：总完成天数减去预期天数，若未超时则显示0

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