Google Apps Script导入多表格到单工作表报错排查

问题场景 #

运行自定义Google Apps Script实现多份电子表格数据汇总到单个工作表，原代码如下：

function addMenu()
{
 var menu = SpreadsheetApp.getUi().createMenu('Custom');
 menu.addItem('Copy Data', 'getData');
 menu.addToUi();
}

function onOpen(e)
{
 addMenu(); 
}

function getData() {
  
  get_files = ['SpreadSheet Example 1', 'SpreadSheet Example 2'];
  
  var ssa = SpreadsheetApp.getActiveSpreadsheet();
  var copySheet = ssa.getSheetByName('DATA');
  copySheet.getRange('A2:Z').clear();  
  
  for(z = 0; z < get_files.length; z++)
  {
  
    var files = DriveApp.getFilesByName(get_files[z]);
    while (files.hasNext()) 
    {
      var file = files.next();
      break;
    }
  
    var ss = SpreadsheetApp.open(file);
    SpreadsheetApp.setActiveSpreadsheet(ss);
    var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
    
    for(var i = 0; i < sheets.length; i++)
    {  
      var nameSheet = ss.getSheetByName(sheets[i].getName()); 
      var nameRange = nameSheet.getDataRange();
      var nameValues = nameRange.getValues();
      
      for(var y = 1; y < nameValues.length; y++)
      {
        copySheet.appendRow(nameValues[y]); 
      }     
    }    
  }  
}

报错信息 #

• 文件名添加.xlsx后缀时触发报错：Exception: Service Spreadsheets failed while accessing document with id 1YdIDE2OgJrtHPvCGA5c.
• 文件名去掉后缀时触发报错：Exception: Argument cannot be null: file
• 所有待读取文件均存放在Google Drive同一文件夹内。

报错根因与排查方向 #

1. xlsx后缀报错根因
   SpreadsheetApp.open() 方法仅支持打开Google Sheets原生格式文件，带.xlsx后缀的是上传到Drive的Excel格式文件，不属于原生Sheets格式，接口无法直接读取，就会抛出文档访问失败错误。如果源文件是Excel格式，需要先手动打开后点击「文件-保存为Google Sheets」完成格式转换，再读取转换后的Sheets文件。

2. file参数为空报错根因
   原代码的文件获取逻辑存在漏洞：

• DriveApp.getFilesByName() 默认全局检索当前账号有权限访问的所有Drive文件，如果文件名拼写不匹配、源文件未给运行脚本的账号开放查看权限，就会出现检索结果为空的情况，此时file变量不会被赋值，后续传入SpreadsheetApp.open()时就会触发空参数报错。
• 全局检索容易匹配到其他位置的同名文件，建议直接指定源文件所在文件夹的ID，在文件夹范围内检索文件，避免搜不到/搜错文件。
• 原代码中SpreadsheetApp.setActiveSpreadsheet(ss)为冗余代码，批量读取数据时不需要切换活动表格，该行还可能额外触发权限校验问题，可直接删除。
• 原代码逐行调用appendRow()写入数据效率极低，数据量较大时容易触发脚本执行超时，建议批量读取后一次性写入。

修正后参考代码 #

// 替换为你存放源文件的文件夹ID
const SOURCE_FOLDER_ID = '替换为你的源文件夹ID';
// 填写准确的Google Sheets格式源文件名，不需要加后缀
const SOURCE_FILE_NAMES = ['SpreadSheet Example 1', 'SpreadSheet Example 2'];

function addMenu() {
  const menu = SpreadsheetApp.getUi().createMenu('Custom');
  menu.addItem('Copy Data', 'getData');
  menu.addToUi();
}

function onOpen() {
  addMenu(); 
}

function getData() {
  const targetSs = SpreadsheetApp.getActiveSpreadsheet();
  const copySheet = targetSs.getSheetByName('DATA');
  // 清空原有数据
  copySheet.getRange('A2:Z').clear();
  const sourceFolder = DriveApp.getFolderById(SOURCE_FOLDER_ID);
  const allData = [];

  SOURCE_FILE_NAMES.forEach(fileName => {
    const fileIterator = sourceFolder.getFilesByName(fileName);
    // 增加文件存在判断
    if (!fileIterator.hasNext()) {
      SpreadsheetApp.getUi().alert(`未找到文件：${fileName}，请检查文件名与权限`);
      return;
    }
    const file = fileIterator.next();
    const sourceSs = SpreadsheetApp.open(file);
    const allSheets = sourceSs.getSheets();

    allSheets.forEach(sheet => {
      const dataRange = sheet.getDataRange();
      const values = dataRange.getValues();
      // 跳过表头，从第二行开始取数
      for (let i = 1; i < values.length; i++) {
        allData.push(values[i]);
      }
    })
  })

  // 批量一次性写入所有数据，提升执行效率
  if (allData.length > 0) {
    copySheet.getRange(2, 1, allData.length, allData[0].length).setValues(allData);
  }
  SpreadsheetApp.getUi().alert(`数据汇总完成，共写入${allData.length}行`);
}

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