如何在Google Sheets中自动新增标签、隐藏旧标签、修改公式并重命名

你之前遇到的日期命名错误，是因为Utilities.formatDate方法的格式符使用错误：大写DD代表一年中的第几天，小写dd才代表当月的日期，这就是10月14日被错误格式化为10/287的核心原因。

下面是满足你所有需求的完整脚本：

function createDailySheet() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  // 筛选所有名称符合MM/dd日期格式的工作表
  const dateSheets = ss.getSheets().filter(sheet => {
    const name = sheet.getName();
    return /^\d{1,2}\/\d{1,2}$/.test(name);
  });

  // 按日期从小到大对工作表排序
  dateSheets.sort((a, b) => {
    const dateA = new Date(`${new Date().getFullYear()}/${a.getName()}`);
    const dateB = new Date(`${new Date().getFullYear()}/${b.getName()}`);
    return dateA - dateB;
  });

  // 1. 复制最新工作表生成当日新表
  const latestSheet = dateSheets[dateSheets.length - 1];
  const latestDate = new Date(`${new Date().getFullYear()}/${latestSheet.getName()}`);
  const newDate = new Date(latestDate);
  newDate.setDate(latestDate.getDate() + 1);
  const newSheetName = Utilities.formatDate(newDate, Session.getScriptTimeZone(), "MM/dd");
  const newSheet = latestSheet.copyTo(ss);
  newSheet.setName(newSheetName);
  // 将新表移动到标签栏最右侧
  ss.setActiveSheet(newSheet);
  ss.moveActiveSheet(ss.getSheets().length);

  // 2. 自动隐藏最早的可见工作表
  const earliestSheet = dateSheets[0];
  if (!earliestSheet.isSheetHidden()) {
    earliestSheet.hideSheet();
  }

  // 3. 自动替换新表公式中的前一天引用
  const targetPrevName = latestSheet.getName();
  const oldPrevName = dateSheets[dateSheets.length - 2].getName();
  const allRange = newSheet.getDataRange();
  const formulas = allRange.getFormulas();
  for (let row = 0; row < formulas.length; row++) {
    for (let col = 0; col < formulas[row].length; col++) {
      if (formulas[row][col]) {
        formulas[row][col] = formulas[row][col].replaceAll(`'${oldPrevName}'!`, `'${targetPrevName}'!`);
      }
    }
  }
  allRange.setFormulas(formulas);
}

使用说明 #

• 首次运行前请确认现有工作表名称都为月/日格式，无不符合格式的无关工作表
• 可手动触发脚本运行，也可在Apps Script后台添加定时触发器，设置每日固定时间自动运行

注意事项 #

• 跨年度使用时需要对应调整年份匹配逻辑，当前默认使用当前自然年匹配工作表日期
• 如果你的公式引用不是'日期'!单元格的标准格式，可自行调整替换规则适配你的公式写法

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