如何编写可记录服务起止时间数据的电子表格配套脚本
服务管控电子表格自动记录服务时间脚本实现方案
核心功能对齐
完全匹配参考截图要求的记录维度,实现无手动操作的自动留痕:
- 服务状态切换为「进行中」时,自动捕获当前时间写入开始时间列
- 服务状态切换为「已完成」时,自动捕获当前时间写入结束时间列
- 根据自动写入的起止时间,自动计算单条服务的总时长
- 同步留痕当前操作的账号信息,避免后续责任追溯无依据
落地配置步骤
1. 前置表结构配置
先固定列位顺序,避免脚本匹配错位(可根据实际需求调整列位,脚本内同步修改对应列号即可):
- A列:服务事项名称,手动录入
- B列:服务状态,设置下拉选项(待分配/进行中/已完成)
- C列:服务开始时间,自动写入,单元格格式设为日期时间型
- D列:服务结束时间,自动写入,单元格格式设为日期时间型
- E列:服务总时长,自动计算,单元格格式设为数值/时长型
- F列:操作人,自动捕获
2. 脚本逻辑配置
打开表格内置的脚本编辑器,粘贴以下可直接运行的通用脚本代码:
// 绑定单元格编辑触发事件 function onEdit(e) { const activeSheet = e.source.getActiveSheet(); // 仅作用于指定的服务管控工作表,避免影响其他表页 if (activeSheet.getName() !== "服务管控总表") return; const editRange = e.range; const editRowIndex = editRange.getRow(); // 跳过表头行,不做自动写入 if (editRowIndex === 1) return; const currentEditValue = editRange.getValue(); // 仅匹配B列(服务状态列)的编辑操作 if (editRange.getColumn() !== 2) return; const recordTime = new Date(); const operator = Session.getActiveUser().getEmail(); // 状态切为进行中时,写入开始时间 if (currentEditValue === "进行中" && !activeSheet.getRange(editRowIndex, 3).getValue()) { activeSheet.getRange(editRowIndex, 3).setValue(recordTime); activeSheet.getRange(editRowIndex, 6).setValue(operator); } // 状态切为已完成时,写入结束时间、计算服务时长 if (currentEditValue === "已完成") { const startTime = activeSheet.getRange(editRowIndex, 3).getValue(); // 校验是否存在有效开始时间,避免无效记录 if (!startTime) { SpreadsheetApp.getUi().alert("该服务未标记为进行中,无法直接标记完成"); // 回退错误的状态修改 editRange.setValue(activeSheet.getRange(editRowIndex, 2).getValue()); return; } activeSheet.getRange(editRowIndex, 4).setValue(recordTime); // 计算时长:毫秒差值换算为分钟,保留1位小数 const durationMinutes = (recordTime.getTime() - startTime.getTime()) / (1000 * 60); activeSheet.getRange(editRowIndex, 5).setValue(durationMinutes).setNumberFormat("0.0\"分钟\""); } }
3. 权限与验证配置
- 保存脚本项目,给项目命名为「服务时间自动记录工具」
- 按照弹窗提示完成脚本授权,确保脚本拥有表格的编辑权限
- 测试流程:在测试行将服务状态切换为「进行中」,确认开始时间、操作人自动写入;再将状态切换为「已完成」,确认结束时间、服务时长自动计算
常见问题排查
- 脚本无响应时:检查脚本绑定的触发类型为「编辑时自动触发」,确认工作表名称和代码内配置的名称完全一致,无多余空格、中英文差异
- 时间记录偏移时:核对表格设置的时区,和脚本运行的系统时区保持一致
- 时长计算报错时:确认开始/结束时间列的单元格为日期时间格式,未被手动修改为纯文本格式
内容的提问来源于stack exchange,提问作者Thiago Bezerra
相关产品推荐
相关产品推荐

