Google Sheets脚本优化需求：合并脚本、格式复制、行列清理、标签排序

问题背景 #

我有一个Google Sheet，所有数据都存放在名为"All"的首个主工作表中，目前拆分了三个独立脚本：

• 脚本1：运行后根据A列内容创建新工作表标签，并在新标签中插入QUERY公式筛选对应数据
• 脚本2：运行后冻结指定工作表的首行
• 脚本3：运行后调整所有工作表的行高为50像素

需求目标 #

将三个脚本合并为一个可统一运行的脚本，同时实现：

1. 将主表"All"的格式复制到所有新创建的工作表中（仅复制格式，不复制数据值）
2. 删除新工作表中的多余列，并仅保留3个额外行
3. （非必需）让新创建的标签自动按字母顺序排列，省去手动重复排序操作

合并后的完整脚本 #

function manageSheets() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const mainSheet = ss.getSheetByName("All");
  const lastRow = mainSheet.getLastRow();
  const mainRange = mainSheet.getDataRange();
  const tabNames = new Set();

  // 收集所有唯一的A列值并去重
  for (let i = 2; i <= lastRow; i++) {
    const tabName = mainSheet.getRange(i, 1).getValue().trim();
    if (tabName) tabNames.add(tabName);
  }

  // 将标签名转为数组并按字母排序
  const sortedTabNames = Array.from(tabNames).sort();

  // 创建新工作表并配置
  sortedTabNames.forEach(tabName => {
    let sheet = ss.getSheetByName(tabName);
    if (!sheet) {
      // 创建新工作表
      sheet = ss.insertSheet(tabName);
      // 复制主表格式到新表
      mainRange.copyTo(sheet.getRange(1, 1), SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false);
      // 插入QUERY公式
      sheet.getRange("A1").setFormula(`QUERY(All!A1:R,"select * where upper(A) = '${tabName.toUpperCase()}'",1)`);
      // 冻结首行
      sheet.setFrozenRows(1);
      
      // 删除多余列（与主表列数对齐）
      const maxColumns = mainSheet.getLastColumn();
      if (sheet.getLastColumn() > maxColumns) {
        sheet.deleteColumns(maxColumns + 1, sheet.getLastColumn() - maxColumns);
      }

      // 仅保留3个额外行（数据行+3空行）
      const formulaRowCount = sheet.getRange("A:A").getValues().filter(row => row[0] !== "").length;
      const totalRowsNeeded = formulaRowCount + 3;
      if (sheet.getMaxRows() > totalRowsNeeded) {
        sheet.deleteRows(totalRowsNeeded + 1, sheet.getMaxRows() - totalRowsNeeded);
      } else if (sheet.getMaxRows() < totalRowsNeeded) {
        sheet.insertRowsAfter(sheet.getMaxRows(), totalRowsNeeded - sheet.getMaxRows());
      }
    }
  });

  // 调整所有工作表行高为50像素
  const requests = ss.getSheets().map(sheet => ({
    updateDimensionProperties: {
      properties: { pixelSize: 50 },
      range: { sheetId: sheet.getSheetId(), dimension: "ROWS" },
      fields: "pixelSize"
    }
  }));
  Sheets.Spreadsheets.batchUpdate({ requests }, ss.getId());

  // 按字母顺序排列所有工作表标签（主表"All"固定在首位）
  const allSheets = ss.getSheets();
  const mainSheetIndex = allSheets.findIndex(sheet => sheet.getName() === "All");
  if (mainSheetIndex !== -1) {
    const mainSheetObj = allSheets.splice(mainSheetIndex, 1)[0];
    allSheets.sort((a, b) => a.getName().localeCompare(b.getName()));
    allSheets.unshift(mainSheetObj);
    allSheets.forEach((sheet, index) => {
      ss.setActiveSheet(sheet);
      ss.moveActiveSheet(index + 1);
    });
  }
}

功能实现说明 #

1. 格式复制 #

使用copyTo方法并指定PASTE_FORMAT参数，仅将主表"All"的单元格格式、列宽等样式复制到新工作表，不会复制任何数据内容。

2. 清理多余列和行 #

• 删除多余列：以主表的列数为基准，新表若超出该列数则直接删除超出部分，确保列结构与主表一致。
• 保留3个额外行：先统计QUERY公式返回的有效数据行数，再将新表总行数调整为「数据行+3空行」，多余行直接删除，行数不足则补充插入。

3. 标签自动排序 #

• 先提取A列的唯一值并去重，转为数组后按字母顺序排序，再按排序后的顺序创建工作表。
• 最后统一调整所有标签顺序，主表"All"固定在首位，其余标签按字母升序排列。

4. 原有功能整合 #

• 冻结首行：在新工作表创建完成后直接调用setFrozenRows(1)完成配置。
• 批量调整行高：保留原脚本的batchUpdate批量操作逻辑，一次性完成所有工作表的行高设置，比逐表调整效率更高。

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