如何用单个脚本处理多工作表并将各表数据按列归档？

需求说明 #

现有脚本仅支持单个工作表的数据归档，且归档数据按行存储。需改造为支持批量处理约10个工作表，每次归档时为每个目标工作表在Archive表中新增一列存储数据，无需多次运行脚本。

改造后代码 #

// 配置：目标工作表列表及对应要归档的区域
const targetSheets = [
  { name: "E2 - Electrical", range: "E2:E81" },
  // 在这里添加其他工作表，格式同上：{ name: "工作表名称", range: "要归档的区域" }
  // 示例：{ name: "M3 - Mechanical", range: "F2:F70" }
];
const archiveSheetName = "Archive";

function batchAppendColumnsToArchive() {
  const ss = SpreadsheetApp.getActive();
  let archiveSheet = ss.getSheetByName(archiveSheetName);
  
  // 如果Archive表不存在则创建，首行留空用于后续日期表头
  if (!archiveSheet) {
    archiveSheet = ss.insertSheet(archiveSheetName);
  }

  const currentDate = new Date();
  
  // 遍历每个目标工作表，处理并归档数据
  targetSheets.forEach(sheetConfig => {
    const targetSheet = ss.getSheetByName(sheetConfig.name);
    if (!targetSheet) {
      console.log(`工作表 ${sheetConfig.name} 不存在，跳过处理`);
      return;
    }

    // 获取目标区域数据，过滤空行
    const values = targetSheet.getRange(sheetConfig.range).getValues()
      .filter(row => row[0] !== null && row[0] !== undefined && row[0] !== '');
    
    // 准备要写入的列：首行是日期+工作表名，后续是数据
    const columnData = [[`${currentDate.toLocaleString()} - ${sheetConfig.name}`]]
      .concat(values);

    // 找到Archive表的下一个空列
    const nextEmptyCol = archiveSheet.getLastColumn() + 1;
    // 写入数据到对应列
    archiveSheet.getRange(1, nextEmptyCol, columnData.length, 1).setValues(columnData);
  });
}

关键说明 #

• 配置灵活：在targetSheets数组中添加所有需要归档的工作表及其对应区域，格式统一，后续新增工作表只需添加新的配置项
• 按列存储：每个工作表的归档数据作为一列存入Archive表，列首标注归档日期和工作表名称，便于区分
• 空行过滤：自动过滤目标区域中的空行，避免归档无效数据
• 容错处理：如果配置的工作表不存在，会跳过该表并在控制台打印提示，不影响其他表的处理
• 高效写入：使用setValues批量写入数据，比原脚本的appendRow更高效，适合多表场景

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