如何用Google Apps Script提取所有工作表指定单元格值并汇总到新表

原代码存在的问题 #

• 表格对象不一致：用openById打开了目标表格，但后续操作却用getActiveSpreadsheet（当前活跃表格），导致跨表格操作出错，需统一使用打开的表格实例。
• 内层循环冗余且赋值错误：getRange(sheettest.getLastRow(),12, 1, 4).getValues()返回的是1行4列的二维数组，内层循环遍历vals.length（值为1）毫无意义，且setValues需要传入二维数组，直接传入vals[j]（一维数组）会触发错误。
• 缺少表头：未添加需求中的“工作表名称 | 值1 | 值2 | 值3 | 值4”表头行。
• 未处理同名工作表：若已存在LEAVEBAL工作表，执行insertSheet会报错。

修改后的完整代码 #

var sheetID = "1tGjn4slbC2x7jtMDO7J6m-aWwvHWq2KJdtF4gT9kcEs"; 
var targetSpreadsheet = SpreadsheetApp.openById(sheetID);
var allSheets = targetSpreadsheet.getSheets();

// 处理已存在的LEAVEBAL工作表
var existingSheet = targetSpreadsheet.getSheetByName('LEAVEBAL');
if (existingSheet) {
  targetSpreadsheet.deleteSheet(existingSheet);
}

// 新建汇总工作表并设置表头
var summarySheet = targetSpreadsheet.insertSheet().setName('LEAVEBAL');
summarySheet.getRange(1, 1, 1, 5).setValues([["工作表名称", "值1", "值2", "值3", "值4"]]);

// 遍历所有工作表提取数据
for (var i = 0; i < allSheets.length; i++) {
  var currentSheet = allSheets[i];
  var sheetName = currentSheet.getName();
  // 跳过汇总工作表本身，避免循环处理
  if (sheetName === 'LEAVEBAL') continue;

  // 获取目标单元格区域：最后一行的第12列开始，1行4列
  var targetVals = currentSheet.getRange(currentSheet.getLastRow(), 12, 1, 4).getValues()[0];
  
  // 将工作表名称和对应值写入汇总表
  summarySheet.getRange(i + 2, 1).setValue(sheetName);
  summarySheet.getRange(i + 2, 2, 1, 4).setValues([targetVals]);
}

代码说明 #

1. 统一表格实例：全程使用targetSpreadsheet（通过openById打开的目标表格），避免跨表格操作的混乱。
2. 处理同名工作表：先检查是否存在LEAVEBAL，存在则删除，防止新建时报错。
3. 添加表头：在汇总表第一行写入需求的表头内容。
4. 优化数据写入：直接提取目标区域的一维数组（getValues()[0]），一次性写入汇总表对应行，避免冗余循环，同时保证setValues传入的是二维数组格式。
5. 跳过汇总表：遍历过程中跳过新建的LEAVEBAL工作表，避免重复处理。

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