如何通过Google Apps Script onEdit用复选框触发移动端PDF导出

• 你使用的默认onEdit函数属于简单触发器，Google Apps Script对简单触发器有严格的权限限制，无法调用需要用户授权的服务（你写的ExportAsPDF里用到的UrlFetchApp、DriveApp、MailApp均属于受限服务），因此调用请求会被直接拦截，表现为执行到对应步骤直接跳过。
• 现有触发代码存在语法错误：if(e.value='TRUE')是赋值操作而非等值判断，永远返回真，且eval(ExportAsPDF)的写法完全多余，没有意义。
• 触发器配置逻辑错误：不需要给ExportAsPDF单独配置onEdit触发器，也不需要部署为API可执行文件。

1. 清理现有触发器：打开脚本编辑器左侧「触发器」面板，删除所有已配置的onEdit、ExportAsPDF相关触发器，避免逻辑冲突。
2. 替换代码：将现有触发逻辑的函数名从onEdit改为installableOnEdit（避免和简单触发器冲突），修正语法错误，调整执行顺序：先完成PDF导出再复位复选框。
3. 新建可安装触发器：在触发器面板点击「添加触发器」，按以下配置：
   • 选择要运行的函数：installableOnEdit
   • 选择事件来源：电子表格
   • 选择事件类型：修改时
   • 其余配置保持默认，点击保存，按提示完成账号授权（若出现「Google未验证应用」提示，点击「高级」-「继续前往[你的项目名]」即可完成授权）
4. 替换ExportAsPDF函数内的占位符：将FolderID替换为你实际要保存PDF的Google Drive文件夹ID，将收件人、抄送人邮箱地址替换为实际地址。

function installableOnEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var activeSheet = ss.getActiveSheet();
  var activeCell = e.range;
  // 复选框触发逻辑
  if(activeCell.getColumn() == 2 && activeCell.getRow() == 1 && activeSheet.getName() == 'Report Generator (Automatic)') {
    if(e.value === "TRUE") {
      // 先执行导出
      ExportAsPDF("F1:O", "Report Generator (Automatic)");
      // 导出完成后复位复选框
      activeCell.setValue("FALSE");
    }
    return;
  }
  // 多选下拉逻辑
  var oldValue, newValue;
  if(activeCell.getColumn() == 14 && activeCell.getRow() >= 8) {
    newValue = e.value;
    oldValue = e.oldValue;
    if(!e.value) {
      activeCell.setValue("");
    } else {
      if (!e.oldValue) {
        activeCell.setValue(newValue);
      } else {
        if(oldValue.indexOf(newValue) < 0) {
          activeCell.setValue(oldValue + ',\n' + newValue);
        } else {
          activeCell.setValue(oldValue);
        }
      }
    }
  }
}

// 该函数仅需替换内部的文件夹ID、收件人、抄送人占位符即可使用
function ExportAsPDF(range,shTabName) {
 var blob,exportUrl,name,options,response,sheetTabId,ss,ssID,url_base,range;

    range = range? range: "F1:O";//Set the default to whatever you want

    shTabName = "Report Generator (Automatic)";//Replace the name with the sheet tab name for your situation
    ss = SpreadsheetApp.getActiveSpreadsheet();//This assumes that the Apps Script project is bound to a G-Sheet
    ssID = ss.getId();
    sh = ss.getSheetByName(shTabName);
    sheetTabId = sh.getSheetId();
    url_base = ss.getUrl().replace(/edit$/,'');
    name = sh.getRange("E1").getValue();
    name = name + "- Supporting Report Evidence"

    exportUrl = url_base + 'export?exportFormat=pdf&format=pdf' +
      '&gid=' + sheetTabId + '&id=' + ssID +
      '&range=' + range + 
      '&size=A4' +
      '&portrait=false' +
      '&fitw=true' +
      '&sheetnames=false&printtitle=True&pagenumbers=CENTER' +
      '&gridlines=false' +
      '&fzr=false' +
      '&top_margin=0.15' +
      '&bottom_margin=0.15' +
      '&left_margin=0.15' +
      '&right_margin=0.15' +
      '&horizontal_alignment=CENTER' +
      '&vertical_alignment=MIDDLE'+
      '&fzr=False';

    options = {
      headers: {
        'Authorization': 'Bearer ' +  ScriptApp.getOAuthToken(),
      }
     }

    options.muteHttpExceptions = true;

    response = UrlFetchApp.fetch(exportUrl, options);

    if (response.getResponseCode() !== 200) {
      console.log("Error exporting Sheet to PDF!  Response Code: " + response.getResponseCode());
      return;
    }
  
    blob = response.getBlob();
    blob.setName(name + '.pdf')
    var specified_folder = DriveApp.getFolderById("FolderID");    //替换为实际的文件夹ID
    var savedPDFfile= specified_folder.createFile(blob);
    var recipient='recipient@mail.com'; //替换为实际收件人邮箱
    var subject=SpreadsheetApp.getActiveSpreadsheet().getRangeByName("'Report Generator (Automatic)'!E1").getValue().toString();
    var body="Hello,\n\nPlease find attached the test document.\n\nThank you,\n My name";
    var myemail = Session.getEffectiveUser().getEmail();
    MailApp.sendEmail(recipient,subject,body,{
    name: myemail,
    cc: 'CCmail@mail.com', //替换为实际抄送人邮箱
    attachments: [savedPDFfile.getAs(MimeType.PDF)]})
};

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