Apps Script插入工作表报错‘Exception: Service error: Spreadsheets’求助

问题概况 #

执行以下代码行时触发错误：

var sheetCreated = SpreadsheetApp.getActiveSpreadsheet().insertSheet(newSheetName);

错误提示：Exception: Service error: Spreadsheets
源表格包含3个名称唯一的工作表，完整代码如下：

function load(){
  var sheetToCopyName = 'toto'
  var spreadSheetToCopyId = '1zDqTaBmoedJPMyopchy2MqVG0JuDveVYUlFQ3_wLO_Y';
  var destinationSharedDriveFolderId = '14ZJ2pK2lWYhXXa0u7PQEmgmuXx1xC0Vr'
  
  var sourceSpreadSheet = SpreadsheetApp.openById(spreadSheetToCopyId);

  severalSheetsToXlsx(sourceSpreadSheet, destinationSharedDriveFolderId);
}

function severalSheetsToXlsx(sourceSpreadSheet, destinationSharedDriveFolderId)
{
    // 创建用于中转的目标表格
    var destinationSpreadSheet = SpreadsheetApp.create('destinationSpreadSheetForCopy',50,5)
    var destinationSpreadSheetId = destinationSpreadSheet.getId()

    // 复制源表格的工作表（带值）
    var sourceSheets = sourceSpreadSheet.getSheets();
    for (let i = 0; i < sourceSheets.length; i++){
      var sheetToCopy = createSheetCopied(sourceSheets[i])
      var sheetCopied =  sheetToCopy.copyTo(destinationSpreadSheet)
      sourceSpreadSheet.deleteSheet(sheetToCopy);
    }
  
    // 导出为Excel文件
    exportExcelFile(destinationSharedDriveFolderId, destinationSpreadSheetId)
}

function createSheetCopied(sheetToCopy){
    // 获取要复制的数据范围
    var rangeToCopy = sheetToCopy.getDataRange()
    var valuesToCopy = rangeToCopy.getValues()

    // 创建新工作表并写入值
    var newSheetName = sheetToCopy.getName().concat('_new')
    var sheetCreated = SpreadsheetApp.getActiveSpreadsheet().insertSheet(newSheetName);
    var pastedRange = sheetCreated.getRange(1, 1, valuesToCopy.length, valuesToCopy[0].length);
    pastedRange.setValues(valuesToCopy);
    return sheetCreated
}

function exportExcelFile(destinationSharedDriveFolderId, spreadSheetId){
    var requestData = {"method": "GET", "headers":{"Authorization":"Bearer "+ScriptApp.getOAuthToken()}};
    params= spreadSheetId+"/export?format=xlsx"
    var url = "https://docs.google.com/spreadsheets/d/"+ params
    console.log(url)
    var result = UrlFetchApp.fetch(url, requestData);  
    var excelBlob = result.getBlob();
    console.log('EXCEL :')
    var folder = DriveApp.getFolderById(destinationSharedDriveFolderId);
    var resource = {
      title: "NewFileCreated" + ".xlsx",
      mimeType: "MimeType.MICROSOFT_EXCEL",
      parents: [{ id: folder.getId() }]
        } 
    excelBlob.setName('NewFileCreated' + ".xlsx")
    var file = folder.createFile(excelBlob);
    console.log("Fichier créé: " + file.getUrl()); 
}

错误原因分析 #

1. getActiveSpreadsheet()使用错误
   SpreadsheetApp.getActiveSpreadsheet()仅能获取用户当前正在操作的活跃表格，但你的代码是通过openById后台打开源表格，执行时没有活跃的交互表格，因此触发服务调用错误。
2. 逻辑冗余且存在风险
   当前流程在源表格中创建临时工作表，复制后再删除，不仅多此一举，还会修改源表格的结构，可能引发权限冲突或数据误操作。

修复方案 #

直接修改逻辑，在目标中转表格中创建新工作表并写入值，跳过源表格的临时表操作：

修改后的完整代码 #

function load(){
  var spreadSheetToCopyId = '1zDqTaBmoedJPMyopchy2MqVG0JuDveVYUlFQ3_wLO_Y';
  var destinationSharedDriveFolderId = '14ZJ2pK2lWYhXXa0u7PQEmgmuXx1xC0Vr'
  
  var sourceSpreadSheet = SpreadsheetApp.openById(spreadSheetToCopyId);
  severalSheetsToXlsx(sourceSpreadSheet, destinationSharedDriveFolderId);
}

function severalSheetsToXlsx(sourceSpreadSheet, destinationSharedDriveFolderId)
{
    // 创建中转目标表格
    var destinationSpreadSheet = SpreadsheetApp.create('destinationSpreadSheetForCopy',50,5);

    // 直接在目标表格中创建工作表并写入值
    var sourceSheets = sourceSpreadSheet.getSheets();
    for (let i = 0; i < sourceSheets.length; i++){
      createSheetCopied(sourceSheets[i], destinationSpreadSheet);
    }
  
    // 导出为Excel
    exportExcelFile(destinationSharedDriveFolderId, destinationSpreadSheet.getId());
    // 可选：删除中转表格，避免冗余文件
    DriveApp.getFileById(destinationSpreadSheet.getId()).setTrashed(true);
}

function createSheetCopied(sheetToCopy, targetSpreadsheet){
    // 获取数据范围和值
    var rangeToCopy = sheetToCopy.getDataRange();
    var valuesToCopy = rangeToCopy.getValues();

    // 在目标表格中创建新工作表
    var newSheetName = sheetToCopy.getName().concat('_new');
    var sheetCreated = targetSpreadsheet.insertSheet(newSheetName);
    
    // 写入数据
    var pastedRange = sheetCreated.getRange(1, 1, valuesToCopy.length, valuesToCopy[0].length);
    pastedRange.setValues(valuesToCopy);
    return sheetCreated;
}

function exportExcelFile(destinationSharedDriveFolderId, spreadSheetId){
    var requestData = {"method": "GET", "headers":{"Authorization":"Bearer "+ScriptApp.getOAuthToken()}};
    var url = `https://docs.google.com/spreadsheets/d/${spreadSheetId}/export?format=xlsx`;
    var result = UrlFetchApp.fetch(url, requestData);  
    var excelBlob = result.getBlob().setName('NewFileCreated.xlsx');
    
    var folder = DriveApp.getFolderById(destinationSharedDriveFolderId);
    var file = folder.createFile(excelBlob);
    console.log("文件已创建: " + file.getUrl()); 
}

关键修改点 #

• 给createSheetCopied函数添加targetSpreadsheet参数，直接传入中转目标表格，避免使用getActiveSpreadsheet()
• 移除源表格中创建临时表并删除的逻辑，直接在目标表格中完成数据写入
• 优化URL拼接为模板字符串，更简洁
• 新增可选逻辑：导出后删除中转表格，减少冗余文件

内容的提问来源于stack exchange，提问作者cedric le valegant