如何在Google Sheet中按间隔和频率循环日期转换数据格式？

需求说明 #

需要将包含起始日期、间隔、频率、结束条件的日程表数据，批量转换为按规则循环展开的日期列表，每个循环日期关联原任务信息。

数据结构与目标效果 #

原始数据结构 #

包含以下核心字段：

• 任务名称
• 起始日期：循环的开始日期
• 间隔：循环的时间间隔数值
• 频率：间隔的时间单位（日/周/月）
• 结束类型：终止循环的规则（按日期/按次数）
• 结束值：对应结束类型的具体值（截止日期/循环次数）

目标输出效果 #

展开为每行一个日期的列表，每行包含任务名称和对应的循环日期，直到满足结束条件。

Google Sheets 实现方法 #

方法1：自定义Apps脚本（灵活适配复杂规则） #

使用Google Apps Script编写批量生成逻辑，步骤如下：

1. 打开目标Google Sheet，点击顶部菜单「扩展程序」→「Apps脚本」
2. 清空默认代码，粘贴以下脚本：

function generateRecurringDates() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  // 读取A2:F列的原始数据（过滤空行）
  const rawData = sheet.getRange("A2:F").getValues().filter(row => row[0]);
  const result = [];

  rawData.forEach(row => {
    const [taskName, startDate, interval, freq, endType, endVal] = row;
    let currentDate = new Date(startDate);
    let loopCount = 0;
    let shouldStop = false;

    while (!shouldStop) {
      // 添加当前日期到结果
      result.push([taskName, new Date(currentDate)]);

      // 计算下一个循环日期
      switch(freq.toLowerCase()) {
        case "日":
          currentDate.setDate(currentDate.getDate() + interval);
          break;
        case "周":
          currentDate.setDate(currentDate.getDate() + interval * 7);
          break;
        case "月":
          currentDate.setMonth(currentDate.getMonth() + interval);
          break;
      }

      // 判断是否终止循环
      if (endType === "按次数") {
        loopCount++;
        if (loopCount >= endVal) shouldStop = true;
      } else if (endType === "按日期") {
        if (currentDate > new Date(endVal)) shouldStop = true;
      }
    }
  });

  // 将结果写入H2开始的区域（可根据需求修改列号）
  if (result.length) {
    sheet.getRange(2, 8, result.length, 2).setValues(result);
  }
}

3. 点击脚本编辑器的「保存」按钮，命名项目（比如「RecurringDatesGenerator」）
4. 点击「运行」按钮，首次运行需要完成Google账号授权（按照提示操作即可）
5. 返回表格，H2及以下区域将自动生成展开后的循环日期列表

方法2：数组公式（适合简单规则场景） #

如果仅需处理「按次数」结束的日/周循环，可使用SEQUENCE结合DATEADD的数组公式：

=ARRAYFORMULA(
  FLATTEN(
    BYROW(A2:F, LAMBDA(row,
      IF(INDEX(row,1)="",,
        IF(INDEX(row,5)="按次数",
          HSTACK(
            INDEX(row,1)&"",
            DATEADD(INDEX(row,2), SEQUENCE(INDEX(row,6))-1, INDEX(row,4))
          ),
          ""
        )
      )
    ))
  )
)

将公式粘贴到H2单元格，即可自动生成对应数据。注：此公式仅适配部分场景，复杂规则推荐使用脚本方法。

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