Google Sheets数字转时间脚本报错求助及工时表优化需求

问题概述 #

• 搭建自动工时表，已实现日期、打卡时间输入及工时计算，但需优化两点：
  1. 每日总工时改为时钟格式（如8:22 PM），而非时长格式
  2. 支持输入3/4位数字自动转换为时间格式（例：930→9:30，1845→18:45）
• 参照教程编写的Google Apps Script无法运行，报错var ss = SpreadsheetApp.getActiveSpreadsheet();中ss为null，且教程代码存在不一致和缺失问题

错误代码 #

var COLUMN_TO_CHECK = 3;
var SHEET_NAME = "Sheet1";

function onEdit(e){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();

  if(sheet.getName() === SHEET_NAME){
    var selectedCell = sheet.getActiveCell();

    selectedCell.setNumberFormat("@");

    if(selectedCell.getColumn() === COLUMN_TO_CHECK){
      var cellValue = selectedCell.getValue();
      var time = cellValue.substr(0,2)+":"+cellValue.substr(cellValue.lenght - 2);
      selectedCell.setValue(time);
      selectedCell.setNumberFormat("hh:mm");
     }
    }
  }

报错信息 #

第7行var sheet = ss.getActiveSheet();中变量ss为null

修复与优化方案 #

核心问题分析 #

1. ss为null的原因：简单触发函数onEdit不能直接调用SpreadsheetApp.getActiveSpreadsheet()，必须通过事件对象e获取上下文，否则在部分场景下会返回null
2. 语法错误：原代码中cellValue.lenght拼写错误，应为cellValue.length
3. 功能缺失：未处理3位数字的输入转换

修正后的完整脚本 #

var COLUMN_TO_CHECK = 3; // 需监听的目标列（例：第3列为上班打卡列）
var SHEET_NAME = "Sheet1";

function onEdit(e) {
  // 校验事件对象完整性，避免异常
  if (!e || !e.range || !e.source) return;
  
  var sheet = e.range.getSheet();
  // 仅处理目标工作表
  if (sheet.getName() !== SHEET_NAME) return;
  
  var targetCell = e.range;
  // 仅处理目标列
  if (targetCell.getColumn() !== COLUMN_TO_CHECK) return;
  
  var inputValue = targetCell.getValue().toString().trim();
  // 仅处理3/4位纯数字输入
  if (!/^\d{3,4}$/.test(inputValue)) return;
  
  var timeString;
  if (inputValue.length === 3) {
    // 3位数字：第1位是小时，后2位是分钟（如930 → 09:30）
    timeString = `0${inputValue.slice(0,1)}:${inputValue.slice(1)}`;
  } else {
    // 4位数字：前2位是小时，后2位是分钟（如1845 → 18:45）
    timeString = `${inputValue.slice(0,2)}:${inputValue.slice(2)}`;
  }
  
  // 设置单元格为时间格式并赋值
  targetCell.setValue(timeString);
  targetCell.setNumberFormat("hh:mm");
}

额外优化建议 #

• 多列支持：若需同时监听上班、下班等多列，可将COLUMN_TO_CHECK改为数组，例如var COLUMNS_TO_CHECK = [3, 5];，然后判断COLUMNS_TO_CHECK.includes(targetCell.getColumn())
• 总工时时钟格式设置：选中显示总工时的单元格，右键→设置单元格格式→时间→选择h:mm AM/PM格式即可，无需脚本处理

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