如何提前3-4周创建以周六为周结束日的每周工作表

需求与问题 #

• 核心需求：每周日上午触发脚本，生成提前4周的工作表，工作表标签为该周的周六（周结束日）；以上周创建的工作表为模板复制内容，要求每次仅生成一个表，避免一次性生成全年工作表。
• 当前问题：现有代码要么日期计算错误，要么批量生成全年表；当前运行时抛出ReferenceError: formatDateString is not defined错误。

错误原因 #

1. 缺失日期格式化函数：代码中调用formatDateString但未实现该函数，直接导致引用错误。
2. 日期逻辑不符合需求：原代码计算的周结束日为周日，而非需求的周六；且未处理“提前4周”的生成逻辑。
3. 模板选择错误：原代码固定取第一个工作表作为模板，未按需求以上周创建的表为模板。

修正后的完整代码 #

function createWeeklySheet() {
  // 定义日期格式化函数：转换为YYYY-MM-DD格式（可根据团队习惯调整）
  function formatDateString(date) {
    const year = date.getFullYear();
    const month = String(date.getMonth() + 1).padStart(2, '0');
    const day = String(date.getDate()).padStart(2, '0');
    return `${year}-${month}-${day}`;
  }

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const today = new Date();

  // 计算目标周结束日：周日触发时，提前4周的周六
  const targetSaturday = new Date(today);
  targetSaturday.setDate(today.getDate() - 4 * 7 + 5); // 周日减28天到4周前的周日，加5天得到对应周六

  const targetSheetName = formatDateString(targetSaturday);

  // 检查目标表是否已存在，避免重复创建
  const existingSheetNames = ss.getSheets().map(sheet => sheet.getName());
  if (existingSheetNames.includes(targetSheetName)) {
    return;
  }

  // 筛选并排序历史周工作表，取最新的作为模板
  const dateSheets = ss.getSheets()
    .filter(sheet => /^\d{4}-\d{2}-\d{2}$/.test(sheet.getName()))
    .sort((a, b) => new Date(b.getName()) - new Date(a.getName()));

  let templateSheet;
  if (dateSheets.length === 0) {
    // 无历史表时使用第一个工作表作为初始模板
    SpreadsheetApp.getUi().alert('未找到历史周工作表，将使用第一个工作表作为模板');
    templateSheet = ss.getSheets()[0];
  } else {
    templateSheet = dateSheets[0];
  }

  // 复制模板并重命名为目标日期
  const newSheet = templateSheet.duplicate();
  newSheet.setName(targetSheetName);
}

关键说明 #

• 日期格式化：统一使用YYYY-MM-DD格式命名工作表，既符合阅读习惯，也便于按日期排序筛选历史表。
• 精准日期计算：针对周日触发的场景，准确计算出提前4周的周六作为周结束日。
• 智能模板选择：优先使用最新的历史周工作表作为模板，确保内容延续性；无历史表时给出提示并使用初始模板。
• 重复校验：提前检查目标表是否存在，避免重复生成。

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