Google Sheets无法识别ISO 8601时间的自动化转换方案咨询

方案一：用ARRAYFORMULA实现自动扩展（无需脚本） #

假设源ISO日期数据在A列（从A2开始，A1是表头），在B2单元格输入以下公式，设置B列格式为yyyy-mm-dd hh:mm:ss后，新增行的A列数据会自动转换：

=ARRAYFORMULA(IF(A2:A="",,DATEVALUE(REGEXEXTRACT(A2:A,"(\d{4}-\d{2}-\d{2})")) + TIMEVALUE(REGEXEXTRACT(A2:A,"T(\d{2}:\d{2}:\d{2})"))))

公式说明： #

• REGEXEXTRACT(A2:A,"(\d{4}-\d{2}-\d{2})")：提取ISO字符串中的日期部分（如2025-03-17）
• REGEXEXTRACT(A2:A,"T(\d{2}:\d{2}:\d{2})")：提取ISO字符串中的时间部分（如18:21:43）
• DATEVALUE和TIMEVALUE分别将提取的文本转为日期/时间数值，相加后得到Google Sheets可识别的日期时间格式
• ARRAYFORMULA会自动对A列所有行生效，新增行无需手动复制公式

方案二：用Apps Script实现全自动化（适合复杂场景） #

如果需要完全无需手动操作的自动化流程（比如源数据是自动导入的），可以用Google Apps Script的编辑触发事件：

1. 打开目标工作表，点击「扩展程序」→「Apps Script」
2. 删除默认代码，粘贴以下脚本：

function onEdit(e) {
  const sheet = e.source.getActiveSheet();
  const editedRange = e.range;
  // 自定义源列和目标列：1=A列，2=B列，可按需修改
  const sourceColumn = 1;
  const targetColumn = 2;

  // 仅处理源列的非表头行修改
  if (editedRange.getColumn() === sourceColumn && editedRange.getRow() > 1) {
    const isoString = editedRange.getValue();
    // 验证ISO格式匹配
    if (typeof isoString === 'string' && isoString.match(/^\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}\.\d{3}\+\d{2}:\d{2}$/)) {
      // 拆分并清洗日期时间
      const [datePart, timeSection] = isoString.split('T');
      const cleanTime = timeSection.split('.')[0];
      const formattedDateTime = new Date(`${datePart} ${cleanTime}`);
      // 写入目标列并设置格式
      sheet.getRange(editedRange.getRow(), targetColumn)
           .setValue(formattedDateTime)
           .setNumberFormat('yyyy-mm-dd hh:mm:ss');
    }
  }
}

3. 保存脚本（命名如ISODateConverter），授权后即可生效

脚本说明： #

• onEdit(e)：当工作表有编辑操作时自动触发
• 仅监控源列（A列）的非表头行，新增或修改源数据时自动处理
• 自动验证ISO格式，清洗掉T、毫秒和时区部分，转为Google Sheets可识别的日期时间后写入目标列
• 无需手动维护，新增行数据会自动转换

注意事项 #

• 两种方案都不会修改源数据，转换结果独立存储在目标列
• 可根据实际需求调整列号、日期时间格式（比如把yyyy-mm-dd hh:mm:ss改成其他格式）
• 脚本首次运行需要授权，按照提示完成权限验证即可

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