如何使用Apps Script将按月份分组的日期调整为按时间顺序排序

问题特征 #

你的原始日期字符串规则为逗号分隔的[月份英文全称, 年份, 月份英文全称, 年份...]结构，当前按月份分组排列（所有1月在前、再所有2月，以此类推），需按时间先后顺序重排，支持缺失月份的场景。
原始数据示例：

JANUARY, 2016, JANUARY, 2017, JANUARY, 2018, JANUARY, 2019, JANUARY, 2020, FEBRUARY, 2016, FEBRUARY, 2017, FEBRUARY, 2018, FEBRUARY, 2019, FEBRUARY, 2020, FEBRUARY, 2021, MARCH, 2016, MARCH, 2017, MARCH, 2018, MARCH, 2019, APRIL, 2016, APRIL, 2017, APRIL, 2018, APRIL, 2019, MAY, 2016, MAY, 2017, MAY, 2018, MAY, 2019, JUNE, 2016, JUNE, 2017, JUNE, 2018, JUNE, 2019, JULY, 2016, JULY, 2017, JULY, 2018, JULY, 2019, AUGUST, 2016, AUGUST, 2017, AUGUST, 2018, AUGUST, 2019, SEPTEMBER, 2016, SEPTEMBER, 2017, SEPTEMBER, 2018, SEPTEMBER, 2019, OCTOBER, 2016, OCTOBER, 2017, OCTOBER, 2018, OCTOBER, 2019, NOVEMBER, 2015, NOVEMBER, 2016, NOVEMBER, 2017, NOVEMBER, 2018, NOVEMBER, 2020, DECEMBER, 2015, DECEMBER, 2016, DECEMBER, 2017, DECEMBER, 2018, DECEMBER, 2020

核心算法步骤 #

• 步骤1：拆分原始字符串为数组，按顺序每两个元素组成一组，每组对应一个「月份+年份」的日期单元
• 步骤2：创建月份映射表，把英文月份全称转换为对应的数字：{"JANUARY":1, "FEBRUARY":2, "MARCH":3, "APRIL":4, "MAY":5, "JUNE":6, "JULY":7, "AUGUST":8, "SEPTEMBER":9, "OCTOBER":10, "NOVEMBER":11, "DECEMBER":12}
• 步骤3：为每个日期单元生成排序权重值：年份 * 100 + 月份数字，例如2015年11月的权重为201511，直接通过数值大小判断时间先后
• 步骤4：按权重值从小到大对所有日期单元排序
• 步骤5：把排序后的日期单元重新拼接为需要的字符串格式即可

可直接使用的Google Apps Script代码（适配Google Sheet场景） #

打开表格后点击「扩展程序」-「Apps Script」，粘贴以下代码运行即可直接处理A列数据：

function sortDateString() {
  // 月份映射表
  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 sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  // 读取A列第一行的原始数据
  const rawStr = sheet.getRange("A1").getValue().toString().trim();
  const rawArr = rawStr.split(",").map(item => item.trim());
  // 分组为日期单元
  const dateUnits = [];
  for (let i = 0; i < rawArr.length; i += 2) {
    const month = rawArr[i];
    const year = parseInt(rawArr[i+1]);
    dateUnits.push({
      monthName: month,
      year: year,
      sortKey: year * 100 + monthMap[month.toUpperCase()]
    })
  }
  // 按sortKey升序排序
  dateUnits.sort((a,b) => a.sortKey - b.sortKey);
  // 拼接为结果字符串
  const result = dateUnits.map(unit => `${unit.monthName}, ${unit.year}`).join(", ");
  // 输出到B列第一行，可自行修改输出位置
  sheet.getRange("B1").setValue(result);
}

输出效果 #

处理后你的示例数据开头会按NOVEMBER, 2015, DECEMBER, 2015, JANUARY, 2016...的顺序排列，完全符合需求，自动适配缺失月份的场景，不需要提前预设时间区间。

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