Google Sheets如何通过按钮触发脚本批量将文本时长转为时间值

可直接运行的批量转换脚本 #

function setDuration() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  // 可根据实际需求修改处理的单元格范围，当前为C列45行到80行
  const dataRange = sheet.getRange("C45:C80");
  const values = dataRange.getValues();
  const output = [];

  // 遍历每一行数据
  for (let i = 0; i < values.length; i++) {
    const rowValue = values[i][0]; // 单列数据取每行第一个元素
    let duration = rowValue; // 不符合格式的文本保留原值

    if (typeof rowValue === 'string') {
      const hourMatch = rowValue.match(/(\d+) ?h/i);
      const hours = hourMatch ? Number(hourMatch[1]) : 0;
      const minuteMatch = rowValue.match(/(\d+) ?m/i);
      const minutes = minuteMatch ? Number(minuteMatch[1]) : 0;
      const secondMatch = rowValue.match(/(\d+) ?s/i);
      const seconds = secondMatch ? Number(secondMatch[1]) : 0;

      if (hours || minutes || seconds) {
        duration = hours / 24 + minutes / 1440 + seconds / 86400;
      }
    }
    output.push([duration]); // setValues要求二维数组格式，需套一层数组
  }

  // 批量写入结果并统一设置显示格式
  dataRange.setValues(output).setNumberFormat('[h]"h "m"m "s"s"');
}

原脚本无法运行的原因 #

• getRange().getValues()返回的是二维数组，不是单个字符串，直接判断typeof value == 'string'永远不成立
• 没有定义range变量，运行时会报变量未定义错误
• 批量写入数据需要传入和范围维度匹配的二维数组，不能直接传入单个数值

按钮触发配置方法 #

1. 打开谷歌表格，点击顶部菜单栏「插入」→「绘图」
2. 自定义绘制按钮样式，保存后拖到表格合适位置
3. 点击插入的绘图，右上角出现三个点，点击选择「分配脚本」
4. 输入脚本名称setDuration，点击确定即可
   之后点击按钮即可自动转换对应范围内的所有时长格式文本。

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