如何使用AppScript将非标准文本日期转换为标准日期格式？

问题背景 #

我有多个Google表格，日期列存储的是'16th day of February 2022'这类非标准文本格式，Google Sheets自带工具和常规方法都无法自动识别。我已经能用Python实现解析转换，但希望用AppScript来提升效率，不过目前还在学习相关语法，实现有困难。

Python参考实现 #

之前用Python的实现思路如下：

import pandas as pd
df = pd.read_excel('filename.xlsx')
months = {'january': 1, 'february': 2, 'march':3, ..., 'december':12}
for index, row in df.iterrows():
    arbitrary_date = row['Date'].split()
    for i in arbitrary_date:
        if i.lower() in months:
            day = arbitrary_date[0][:2]  # 提取日期数字部分
            month = str(months[i.lower()])
            year = arbitrary_date[-1]
            # 后续可通过内置库转换为标准日期格式

AppScript批量转换方案 #

下面是针对Google Sheets的AppScript实现，可直接在表格中运行完成批量转换：

完整代码 #

function convertNonStandardDates() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const dateColumn = "A"; // 替换为你的日期列字母（如B、C）
  const startRow = 2; // 从第2行开始（假设第1行是表头）
  
  // 获取目标列的所有日期文本
  const range = sheet.getRange(`${dateColumn}${startRow}:${dateColumn}${sheet.getLastRow()}`);
  const dateTexts = range.getValues().flat();
  
  // 月份名称与数字映射
  const monthMap = {
    'january': 1,
    'february': 2,
    'march': 3,
    'april': 4,
    'may': 5,
    'june': 6,
    'july': 7,
    'august': 8,
    'september': 9,
    'october': 10,
    'november': 11,
    'december': 12
  };
  
  // 批量转换日期文本
  const convertedDates = dateTexts.map(text => {
    if (!text) return [null]; // 空单元格返回null
    
    // 拆分日期文本为数组
    const parts = text.split(' ');
    // 提取日期：去掉后缀（th/st/nd/rd）并转为数字
    const day = parseInt(parts[0].replace(/[a-zA-Z]+$/, ''), 10);
    // 提取月份：转小写后匹配映射表
    const monthName = parts[3].toLowerCase();
    const month = monthMap[monthName];
    // 提取年份
    const year = parseInt(parts[4], 10);
    
    // 创建标准日期对象（注意AppScript的Date月份从0开始，需减1）
    const date = new Date(year, month - 1, day);
    
    // 验证日期有效性，无效则返回null
    return date instanceof Date && !isNaN(date) ? [date] : [null];
  });
  
  // 将转换后的日期写回原列
  range.setValues(convertedDates);
  // 可选：设置单元格为标准日期格式
  range.setNumberFormat("yyyy-MM-dd");
}

使用步骤 #

1. 打开目标Google Sheet，点击扩展程序 > Apps 脚本
2. 粘贴上述代码，根据你的表格修改dateColumn（日期列字母）和startRow（数据起始行）
3. 点击运行按钮，首次运行需授权脚本访问表格权限
4. 运行完成后，指定列的非标准日期文本会自动转为可识别的标准日期格式

代码关键逻辑说明 #

• 文本拆分：通过split(' ')将日期文本拆分为数组，精准提取日、月、年部分
• 日期清洗：用正则表达式replace(/[a-zA-Z]+$/, '')去除日期后缀（如th、st），转为数字格式
• 月份匹配：通过预定义的monthMap将英文月份名称转为数字
• 日期对象创建：AppScript的Date构造函数要求月份从0开始计数，因此需将匹配到的月份数字减1
• 批量写入：采用数组批量写入的方式，比逐行操作效率更高

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