Apps Script批量合并Spreadsheet数据：循环失效无写入问题求助

问题背景 #

我有多份结构相同、数据不同的Google Sheets文件，希望通过Apps Script将这些文件中的数据提取后逐行追加到新的Spreadsheet中。但编写的批量处理代码仅处理第一个文件，且没有任何数据写入目标表。

原批量处理代码：

function leerDrive() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  //var data = [];

  var folder = DriveApp.getFolderById("1Ow3KXDsf7eyEmbDrCUzkVFFC_-FNoEwA");
  var contents = folder.getFilesByType(MimeType.GOOGLE_SHEETS)

  var fileID, file;

  try {

    while (contents.hasNext()) {

      file = contents.next();
      fileID = file.getId();

      Logger.log(fileID)
      Logger.log(file)

      var ss = SpreadsheetApp.openById(fileID);
      var hojaCalc = ss.getSheetByName("ETS");
      var calc = hojaCalc.getRange("B14:V72").getValues();
      var conCalc = hojaCalc.getRange("E4").getValue();
      Logger.log(calc)

      for (var fila = 1; fila < calc.length; fila++) {
        var discCalc = calc[fila][0]
        var cuotaMcalc = calc[fila][6]
        var cuotaFcalc = calc[fila][8]
        

        Logger.log(discCalc)
        Logger.log(cuotaMcalc)
        Logger.log(cuotaFcalc)

        var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("base intermedia");
              

      }  

      //return data;
    }

  }
  catch (error) {
    return;

  }
}

单个文件写入代码：

function baseIntermedia() {

  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("base intermedia");
  var sheet = SpreadsheetApp.openById("1rs3OujBExJKHY4b5IVT2oRsDimdzgVEjZNIEDxY8fm8")
  var hojaCalc = sheet.getSheetByName("ETS");
  var conCalc = hojaCalc.getRange("E4").getValue();
  var calc = hojaCalc.getRange("B14:V72").getValues();

  arregloDiscCalc = []
  arreglocuotaMcalc = []
  arreglocuotaFcalc = []

  for(var fila=0;fila<calc.length-1;fila++){
    var discCalc = calc[fila][0]
    var cuotaMcalc = calc[fila][6]
    var cuotaFcalc = calc[fila][8]

    
    Logger.log(discCalc)
    Logger.log(cuotaMcalc)
    Logger.log(cuotaFcalc)

    arregloDiscCalc.push([discCalc])
    arreglocuotaMcalc.push([cuotaMcalc])
    arreglocuotaFcalc.push([cuotaFcalc])

  }
  
 Logger.log(arregloDiscCalc) 
 ss.getRange(2,1,calc.length-1).setValue(conCalc)
 ss.getRange(2,2,calc.length-1).setValues(arregloDiscCalc)
 ss.getRange(2,3,calc.length-1).setValues(arreglocuotaMcalc)
 ss.getRange(2,4,calc.length-1).setValues(arreglocuotaFcalc)



}

错误原因分析 #

1. 变量重名覆盖：原批量代码中，开头定义的ss是目标表的Spreadsheet对象，但循环内又用var ss = SpreadsheetApp.openById(fileID);覆盖了这个变量，导致后续无法正确引用目标表。
2. 缺失写入逻辑：循环内仅获取了目标表的引用，但没有执行任何数据写入操作。
3. 未处理追加逻辑：单个文件写入是从第2行开始覆盖，批量处理需要计算目标表的最后一行，实现数据追加而非覆盖。
4. 异常处理无效：catch块直接return，无法打印错误信息，难以定位问题。

修正后的完整代码 #

function leerDrive() {
  // 提前获取目标表引用，避免变量覆盖
  var targetSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var targetSheet = targetSpreadsheet.getSheetByName("base intermedia");
  if (!targetSheet) {
    Logger.log("目标表'base intermedia'不存在");
    return;
  }

  var folder = DriveApp.getFolderById("1Ow3KXDsf7eyEmbDrCUzkVFFC_-FNoEwA");
  var files = folder.getFilesByType(MimeType.GOOGLE_SHEETS);

  try {
    while (files.hasNext()) {
      var file = files.next();
      var fileID = file.getId();
      Logger.log("正在处理文件: " + file.getName() + " (" + fileID + ")");

      var sourceSpreadsheet = SpreadsheetApp.openById(fileID);
      var sourceSheet = sourceSpreadsheet.getSheetByName("ETS");
      if (!sourceSheet) {
        Logger.log("文件" + file.getName() + "中不存在'ETS'工作表，跳过");
        continue;
      }

      // 提取源数据
      var calc = sourceSheet.getRange("B14:V72").getValues();
      var conCalc = sourceSheet.getRange("E4").getValue();
      // 过滤空行（如果需要）
      var validRows = calc.filter(row => row[0] !== "");

      // 准备要写入的数据数组
      var writeData = [];
      for (var fila = 0; fila < validRows.length; fila++) {
        var discCalc = validRows[fila][0];
        var cuotaMcalc = validRows[fila][6];
        var cuotaFcalc = validRows[fila][8];
        // 每行数据对应目标表的4列
        writeData.push([conCalc, discCalc, cuotaMcalc, cuotaFcalc]);
      }

      if (writeData.length === 0) {
        Logger.log("文件" + file.getName() + "无有效数据，跳过");
        continue;
      }

      // 计算目标表的最后一行，实现追加
      var lastRow = targetSheet.getLastRow();
      var startRow = lastRow === 0 ? 2 : lastRow + 1; // 如果表为空，从第2行开始（假设第1行是表头）

      // 一次性写入数据，提升效率
      targetSheet.getRange(startRow, 1, writeData.length, writeData[0].length).setValues(writeData);
      Logger.log("文件" + file.getName() + "数据写入完成，共写入" + writeData.length + "行");
    }
    Logger.log("所有文件处理完成");
  } catch (error) {
    Logger.log("处理过程中出现错误: " + error.message);
    throw error; // 抛出错误便于调试
  }
}

代码关键说明 #

• 变量隔离：将目标表和源表的引用变量分开（targetSpreadsheet/sourceSpreadsheet），避免变量覆盖。
• 空行过滤：使用filter过滤源数据中的空行，避免写入无效数据。
• 批量写入：将单个文件的所有有效数据整理成二维数组后一次性写入，减少SpreadsheetApp API调用次数，提升执行效率。
• 追加逻辑：通过getLastRow()获取目标表最后一行，计算写入的起始行，实现数据逐文件追加。
• 错误处理优化：打印错误信息并抛出，便于在脚本编辑器的日志中定位问题。

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