Google Apps Script 自动拆分Sheets时间戳至三类时间日期对应列

支持对指定工作表（如Sheet1、Sheet2）中Timestamp列的标准日期时间格式内容，自动拆分出Time (GMT)、Time (Am/Pm)、Date三类数据写入对应独立列；Timestamp为空的行，目标列不会返回任何错误提示值。
#

配置说明 #

脚本开头预留了可自定义的配置项，你可以根据自己表格的实际结构修改：

• 可自定义需要生效的工作表名称列表
• 可自定义Timestamp列、三个拆分结果列的所在位置
• 可自定义数据起始行（默认跳过表头行从第2行开始处理）

提示：如果需要调整三类输出值的显示格式，直接修改代码中对应输出段的字符串拼接逻辑即可，比如日期要改成YYYY-MM-DD格式，只需修改日期部分的拼接规则。

完整脚本代码 #

// 自定义配置区 - 按实际表格情况修改
const CONFIG = {
  targetSheetNames: ["Sheet1", "Sheet2"],
  timestampCol: 1, // Timestamp列列号，A列=1、B列=2，以此类推
  gmtTimeCol: 2, // Time (GMT) 写入列
  ampmTimeCol: 3, // Time (Am/Pm) 写入列
  dateCol: 4, // Date 写入列
  dataStartRow: 2 // 数据起始行，默认第1行为表头
}

function splitTimestamp() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  CONFIG.targetSheetNames.forEach(sheetName => {
    const sheet = ss.getSheetByName(sheetName);
    if (!sheet) return;
    const lastRow = sheet.getLastRow();
    if (lastRow < CONFIG.dataStartRow) return;

    // 批量读取Timestamp列数据，减少API调用提升效率
    const tsRange = sheet.getRange(CONFIG.dataStartRow, CONFIG.timestampCol, lastRow - CONFIG.dataStartRow + 1, 1);
    const tsValues = tsRange.getValues();

    const gmtOutput = [];
    const ampmOutput = [];
    const dateOutput = [];

    tsValues.forEach(row => {
      const tsVal = row[0];
      // 空值直接写入空内容，避免返回错误
      if (!tsVal) {
        gmtOutput.push([""]);
        ampmOutput.push([""]);
        dateOutput.push([""]);
        return;
      }

      const dateObj = new Date(tsVal);
      // 处理GMT24小时制时间
      const utcH = String(dateObj.getUTCHours()).padStart(2, '0');
      const utcM = String(dateObj.getUTCMinutes()).padStart(2, '0');
      gmtOutput.push([`${utcH}:${utcM}`]);

      // 处理12小时制AM/PM时间
      let localH = dateObj.getHours();
      const period = localH >= 12 ? 'PM' : 'AM';
      localH = localH % 12 || 12;
      const localM = String(dateObj.getMinutes()).padStart(2, '0');
      ampmOutput.push([`${localH}:${localM} ${period}`]);

      // 处理日期
      const y = dateObj.getFullYear();
      const m = String(dateObj.getMonth() + 1).padStart(2, '0');
      const d = String(dateObj.getDate()).padStart(2, '0');
      dateOutput.push([`${m}/${d}/${y}`]);
    });

    // 批量写入结果
    sheet.getRange(CONFIG.dataStartRow, CONFIG.gmtTimeCol, gmtOutput.length, 1).setValues(gmtOutput);
    sheet.getRange(CONFIG.dataStartRow, CONFIG.ampmTimeCol, ampmOutput.length, 1).setValues(ampmOutput);
    sheet.getRange(CONFIG.dataStartRow, CONFIG.dateCol, dateOutput.length, 1).setValues(dateOutput);
  });
}

// 可选：创建编辑触发器，输入/修改时间戳时自动拆分
function buildAutoTrigger() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  // 避免重复创建触发器
  const existingTriggers = ScriptApp.getProjectTriggers();
  const hasTrigger = existingTriggers.some(t => t.getHandlerFunction() === "splitTimestamp" && t.getTriggerSourceId() === ss.getId());
  if (!hasTrigger) {
    ScriptApp.newTrigger("splitTimestamp")
      .forSpreadsheet(ss)
      .onEdit()
      .create();
  }
}

部署步骤 #

1. 打开目标Google Sheets表格，点击顶部菜单栏「扩展程序」→「Apps Script」打开脚本编辑器
2. 删除编辑器中默认的空白代码，粘贴上面的完整脚本
3. 修改开头CONFIG配置区的参数，匹配你自己表格的工作表名称、列位置
4. 编辑器顶部函数选择splitTimestamp，点击运行按钮，按弹窗提示完成Google账号授权，首次运行即可完成初始化，空行不会生成任何错误值
5. （可选）如果需要修改时间戳后自动更新拆分结果，选择buildAutoTrigger函数点击运行一次，后续编辑表格时会自动触发拆分逻辑

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