Google Apps Script复制指定行至新表格报错：TypeError排查求助

问题定位 #

你遇到的TypeError: Cannot read properties of undefined (reading 'forEach')是因为**sheetsToCopy数组里第一个元素{ name: "README"}没有columns属性**，当循环到这个对象时，sheet.columns是undefined，自然不能调用forEach方法。

另外还有个潜在问题：如果Drive里找不到名为source_spreadsheet的文件，sourceSpreadsheet会保持初始的undefined状态，后续调用sourceSpreadsheet.getSheetByName()也会触发新的错误。

修复方案 #

1. 补全缺失的columns配置：给README工作表添加需要复制的列，或者单独处理无columns的场景（比如复制整个工作表）。
2. 增加源文件存在性校验：如果没找到源文件，直接终止脚本并给出提示，避免后续报错。
3. 新增源工作表存在性校验：如果源文件里找不到指定名称的工作表，弹出提示并跳过，不中断整个脚本执行。

修改后的完整代码 #

function copySpecificData() {
  var sourceSpreadsheet;
  
  const files = DriveApp.getFilesByName('source_spreadsheet');
  // 校验源文件是否存在
  if (!files.hasNext()) {
    SpreadsheetApp.getUi().alert("未找到名为source_spreadsheet的文件");
    return; // 终止脚本
  }
  
  const fileId = files.next().getId();
  sourceSpreadsheet = SpreadsheetApp.openById(fileId);

  const destinationSpreadsheet = SpreadsheetApp.create("dest_spreadsheet");

  const sheetsToCopy = [
    // 给README添加需要复制的列，或者删除columns让代码自动复制整表
    { name: "README", columns: ["A", "B", "C"] }, 
    { name: "TEST", columns: ["A", "B", "E", "F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T"] },
    { name: "EXAMPLE", columns: ["A", "B", "E", "F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T"] },
    { name: "TEXT", columns: ["A", "B", "E", "F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T"] }
  ];

  sheetsToCopy.forEach(function(sheet) {
    const sourceSheet = sourceSpreadsheet.getSheetByName(sheet.name);
    if (!sourceSheet) {
      SpreadsheetApp.getUi().alert(`源文件中未找到工作表：${sheet.name}`);
      return; // 跳过当前工作表，继续处理下一个
    }
    
    const destinationSheet = destinationSpreadsheet.insertSheet();
    destinationSheet.setName(sheet.name);

    // 先判断columns是否存在且有效，避免再次报错
    if (sheet.columns && sheet.columns.length > 0) {
      sheet.columns.forEach(function(column, index) {
        const range = sourceSheet.getRange(column + ":" + column);
        range.copyTo(destinationSheet.getRange(1, index + 1), { contentsOnly: true });
      });
    } else {
      // 如果没有配置columns，自动复制整个工作表的内容
      sourceSheet.getDataRange().copyTo(destinationSheet.getRange(1,1), { contentsOnly: true });
    }
  });

  SpreadsheetApp.getUi().alert("新表格已创建：" + destinationSpreadsheet.getUrl());
}

额外说明 #

• 如果README需要复制整个工作表，直接删除它的columns配置即可，代码里的else分支会自动处理整表复制逻辑。
• 新增的校验逻辑能提前拦截大部分异常情况，避免脚本无预警中断。

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