Google Apps Script：getFolderById在表格中权限获取失败求助

我有一个用于生成发票的Google表格，每张发票对应一个工作表，通过复选框触发脚本将当前工作表保存为PDF到同账号的指定Google Drive文件夹。

现有代码 #

function saveInvoiceIntoGDrivePDF() {
    const currentSpreadSheet = SpreadsheetApp.getActiveSpreadsheet();
    const paramSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Paramètres généraux");
    const currentSheet = currentSpreadSheet.getActiveSheet();
    const invoicesStorageFolderOnGDriveId = paramSheet.getRange('B30').getValue();
    DriveApp.getFolderById(invoicesStorageFolderOnGDriveId);   
    const authorizationToken = ScriptApp.getOAuthToken();
    SpreadsheetApp.getUi().alert("Got Auth Token!");
    const exportLink = "https://docs.google.com/spreadsheets/d/"+currentSpreadSheet.getId()+"/export?format=pdf&size=a4&portrait=true&scale=4&top_margin=1.00&bottom_margin=1.00&left_margin=0.50&right_margin=0.50&gridlines=false&printnotes=false&pageorder=2&horizontal_alignment=CENTER&vertical_alignment=TOP&printtitle=false&sheetnames=false&fzr=false&fzc=false&attachment=false&r1=0&r2=40&c1=1&c2=6&gid="+currentSheet.getSheetId();
    const invoicingYear = SpreadsheetApp.getActiveSheet().getRange('J5').getValue();
    const invoicingMonth = SpreadsheetApp.getActiveSheet().getRange('N6').getValue();
    const customerName = SpreadsheetApp.getActiveSheet().getRange('J9').getValue();
    const indexFacture = SpreadsheetApp.getActiveSheet().getRange('J7').getValue();
    const invoicePDFFileName = "Invoice_"+invoicingYear+"_"+invoicingMonth+"_"+customerName+"_"+indexFacture+".pdf";
    const exportParams = {     
        method:"GET",     
        headers:{Authorization:"Bearer "+authorizationToken},     
        muteHttpExceptions:true   
    };
    var invoicePDFContent = UrlFetchApp.fetch(exportLink, exportParams).getBlob().setName(invoicePDFFileName);
    invoicesStorageFolderOnGDrive.createFile(invoicePDFContent);
}

appsscript.json权限设置 #

{
    "timeZone": "Europe/Paris",
    "dependencies": {
    },
    "exceptionLogging": "STACKDRIVER",
    "runtimeVersion": "V8",
    "oauthScopes": [
        "https://www.googleapis.com/auth/script.external_request",
        "https://www.googleapis.com/auth/drive",
        "https://www.googleapis.com/auth/spreadsheets.readonly",
        "https://www.googleapis.com/auth/spreadsheets"
    ]
}

核心问题 #

该函数通过每张工作表“管理区域”的复选框触发时，无法获取Drive权限；但在Apps Script编辑器中运行完全正常。需要解决权限适配问题，让普通用户能通过简单操作完成PDF保存。

1. 修复变量赋值错误 #

代码中DriveApp.getFolderById(invoicesStorageFolderOnGDriveId);未将文件夹对象赋值给变量，导致后续createFile调用失败，修改为：

const invoicesStorageFolderOnGDrive = DriveApp.getFolderById(invoicesStorageFolderOnGDriveId);

2. 替换触发方式 #

复选框触发属于简单触发（如onEdit），这类触发受权限限制，无法访问需要授权的服务（如DriveApp、带授权的UrlFetch请求）；而编辑器运行处于授权环境，所以能正常执行。推荐两种替代方案：

方案A：自定义菜单触发

添加菜单代码，让用户通过表格顶部的自定义菜单触发脚本，菜单触发属于授权环境：

function onOpen() {
    const ui = SpreadsheetApp.getUi();
    ui.createMenu('发票工具')
      .addItem('保存当前发票为PDF', 'saveInvoiceIntoGDrivePDF')
      .addToUi();
}

方案B：可安装编辑触发

1. 打开Apps Script编辑器，点击左侧「触发」图标
2. 点击「添加触发」，选择函数saveInvoiceIntoGDrivePDF，事件源选「从电子表格」，事件类型选「编辑时」
3. 保存时按照提示完成权限授权

3. 优化权限范围 #

移除冗余的spreadsheets.readonly权限（已有spreadsheets权限覆盖），避免权限冲突：

"oauthScopes": [
    "https://www.googleapis.com/auth/script.external_request",
    "https://www.googleapis.com/auth/drive",
    "https://www.googleapis.com/auth/spreadsheets"
]

4. 代码优化（可选） #

• 复用currentSheet变量，避免重复调用getActiveSheet()
• 用模板字符串替代字符串拼接，提升代码可读性
• 替换调试弹窗为日志记录，添加成功提示

修改后的完整代码 #

function saveInvoiceIntoGDrivePDF() {
    const currentSpreadSheet = SpreadsheetApp.getActiveSpreadsheet();
    const paramSheet = currentSpreadSheet.getSheetByName("Paramètres généraux");
    const currentSheet = currentSpreadSheet.getActiveSheet();
    const invoicesStorageFolderOnGDriveId = paramSheet.getRange('B30').getValue();
    const invoicesStorageFolderOnGDrive = DriveApp.getFolderById(invoicesStorageFolderOnGDriveId);   
    const authorizationToken = ScriptApp.getOAuthToken();
    console.log("Auth token acquired successfully");
    
    const exportLink = `https://docs.google.com/spreadsheets/d/${currentSpreadSheet.getId()}/export?format=pdf&size=a4&portrait=true&scale=4&top_margin=1.00&bottom_margin=1.00&left_margin=0.50&right_margin=0.50&gridlines=false&printnotes=false&pageorder=2&horizontal_alignment=CENTER&vertical_alignment=TOP&printtitle=false&sheetnames=false&fzr=false&fzc=false&attachment=false&r1=0&r2=40&c1=1&c2=6&gid=${currentSheet.getSheetId()}`;
    
    const invoicingYear = currentSheet.getRange('J5').getValue();
    const invoicingMonth = currentSheet.getRange('N6').getValue();
    const customerName = currentSheet.getRange('J9').getValue();
    const indexFacture = currentSheet.getRange('J7').getValue();
    const invoicePDFFileName = `Invoice_${invoicingYear}_${invoicingMonth}_${customerName}_${indexFacture}.pdf`;
    
    const exportParams = {     
        method:"GET",     
        headers:{Authorization:`Bearer ${authorizationToken}`},     
        muteHttpExceptions:true   
    };
    
    const invoicePDFContent = UrlFetchApp.fetch(exportLink, exportParams).getBlob().setName(invoicePDFFileName);
    invoicesStorageFolderOnGDrive.createFile(invoicePDFContent);
    SpreadsheetApp.getUi().alert("PDF已成功保存到Drive文件夹！");
}

function onOpen() {
    const ui = SpreadsheetApp.getUi();
    ui.createMenu('发票工具')
      .addItem('保存当前发票为PDF', 'saveInvoiceIntoGDrivePDF')
      .addToUi();
}

操作步骤 #

1. 替换原脚本代码，更新appsscript.json权限配置
2. 运行一次onOpen函数，完成首次权限授权
3. 刷新Google表格，通过顶部「发票工具」菜单触发PDF保存；若用可安装触发，直接编辑复选框即可执行

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