谷歌表格中将小数转hh:mm格式并兼容多输入格式的实现方法

实现原理 #

借助谷歌表格的Apps Script（对标Excel的VBA）编写onEdit触发器，监听单元格编辑事件，自动把输入的小时数值（如1.5、36.25）或时间字符串（如1:30）转换为[h]:mm格式的显示效果，同时保留时间数值的可计算性。

具体步骤 #

1. 打开目标谷歌表格，点击顶部菜单栏的扩展 > Apps 脚本，进入脚本编辑器。
2. 删除编辑器内的默认代码，粘贴以下自定义脚本：

function onEdit(e) {
  const range = e.range;
  let inputValue = e.value;
  
  // 仅处理单个单元格的编辑操作
  if (range.rowStart !== range.rowEnd || range.columnStart !== range.columnEnd) return;
  
  // 转换字符串形式的数字为数值类型
  let numValue;
  if (typeof inputValue === 'string') {
    numValue = Number(inputValue);
    if (!isNaN(numValue)) {
      inputValue = numValue;
    }
  }
  
  // 处理数字输入（包含字符串转来的数字）
  if (typeof inputValue === 'number') {
    // 将输入的小时数转换为谷歌表格的时间数值（以天为单位）
    const timeInDays = inputValue / 24;
    range.setValue(timeInDays);
    // 设置显示格式为[h]:mm，支持超过24小时的显示
    range.setNumberFormat('[h]:mm');
  } 
  // 处理h:mm格式的字符串输入
  else if (typeof inputValue === 'string') {
    const timePattern = /^(\d+):(\d{2})$/;
    const match = inputValue.match(timePattern);
    if (match) {
      const hours = parseInt(match[1], 10);
      const minutes = parseInt(match[2], 10);
      const timeInDays = (hours + minutes / 60) / 24;
      range.setValue(timeInDays);
      range.setNumberFormat('[h]:mm');
    }
  }
}

3. 点击脚本编辑器顶部的保存按钮，给脚本命名（比如TimeInputConverter）。
4. 返回谷歌表格测试输入：
   • 输入1.5，单元格自动显示1:30
   • 输入1:30，单元格保持显示1:30
   • 输入36.25，显示36:15
   • 输入64，显示64:00

注意事项 #

• 脚本仅对单个单元格的手动编辑生效，批量粘贴内容不会触发转换（如需支持批量，可修改脚本逻辑）。
• 转换后的单元格实际值为谷歌表格标准时间数值（以天为单位），支持后续的时间计算（如求和、加减）。

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