如何修正Apps Script代码，合并指定文件夹内谷歌表单响应至主表？

原代码核心问题 #

• 表头获取错误：activeSheet.getLastColumn()-1 导致少取一列表头，后续列匹配逻辑错乱
• 固定清空范围：A2:AA2000 无法适配实际数据量，易残留空行或清空不彻底
• 未过滤空白行：表单响应表的getLastRow()会包含底部空行，合并后出现大量无效空行
• 表头匹配容错差：子表找不到对应表头时返回-1，触发数组负索引访问，导致数据错乱
• 无空数据判断：无有效数据时执行写入会抛出错误

修正后的完整代码 #

// 请先填写你的文件夹ID和主表名称
const FOLDER_ID = "你的目标文件夹ID";
const SHEET_NAME = "你的主表名称";

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('自定义菜单')
      .addItem('刷新数据', 'mainFunc')
      .addToUi();
}

function mainFunc(){
  var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var activeSheet = activeSpreadsheet.getSheetByName(SHEET_NAME);
  
  // 完整获取主表所有表头（移除原代码的-1）
  var header = activeSheet.getRange(1, 1, 1, activeSheet.getLastColumn()).getValues()[0];
  const headerColCount = header.length;

  var folder = DriveApp.getFolderById(FOLDER_ID);
  var files = folder.getFiles();
  var combinedData= [];

  while(files.hasNext()){
    var file = files.next();
    if(file.getMimeType() === "application/vnd.google-apps.spreadsheet"){
      var data = getDataFromSource(file.getId(), header);
      // 过滤子表中全空的行
      data = data.filter(row => row.some(cell => cell !== ""));
      // 给每行添加来源文件名标识
      data = data.map(row => row.concat([file.getName()]));
      combinedData = combinedData.concat(data);
    }
  }

  // 动态清空主表数据行（从第2行到现有最后一行）
  const lastDataRow = activeSheet.getLastRow();
  if(lastDataRow >= 2){
    activeSheet.getRange(2, 1, lastDataRow - 1, headerColCount + 1).clearContent();
  }

  // 判断是否有有效数据，避免空数组写入报错
  if(combinedData.length > 0){
    activeSheet.getRange(2, 1, combinedData.length, combinedData[0].length).setValues(combinedData);
  } else {
    SpreadsheetApp.getUi().alert("未找到可合并的有效数据");
  }
}

function getDataFromSource(ssId, header){
  var spreadsheet = SpreadsheetApp.openById(ssId);
  var firstSheet = spreadsheet.getSheets()[0];
  var data = firstSheet.getRange(1, 1, firstSheet.getLastRow(), firstSheet.getLastColumn()).getValues();
  
  var pos = [];
  for(var i=0; i<header.length; i++){
    var colIndex = data[0].indexOf(header[i]);
    // 匹配不到表头时用null替代-1，避免负索引访问错误
    pos.push(colIndex !== -1 ? colIndex : null);
  }

  // 移除子表自身表头
  data.shift();
  var processedData = shiftColumns(data, pos);
  return processedData;
}

function shiftColumns(rows, pos) {
  return rows.map(cols => {
    return pos.map(i => {
      // 处理null和空值情况，避免数组越界
      if(i !== null && cols[i]){
        return cols[i];
      } else {
        return "";
      }
    });
  });
}

关键修改说明 #

• 表头完整获取：移除原代码中getLastColumn()后的-1，确保获取主表所有列的表头，保证列匹配逻辑准确
• 动态清空数据：根据主表实际数据行数清空内容，避免固定范围带来的残留空行或过度清空问题
• 空白行过滤：添加过滤逻辑，剔除子表中全空的行，彻底解决合并后大量空行的问题
• 表头匹配容错：匹配失败时用null替代-1，避免数组负索引访问导致的数据错乱
• 空数据保护：添加空数组判断，无有效数据时弹出提示，避免写入报错
• 全局变量明确：将FOLDER_ID和SHEET_NAME定义为全局常量，确保参数传递正确

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