求助：为现有Google Apps Script添加Google Drive PDF邮件附件

我有一段可正常运行的Google Apps Script代码，想要给邮件添加1-2个来自Google Drive的PDF作为附件，但尝试实现时出现问题，调整后仍未解决。已精简脚本，并按照建议将文件夹改为指定文件（命名为myFile2），以下是我的代码：

function Email11() {

var ssID = SpreadsheetApp.getActiveSpreadsheet().getId;

  var sheetName = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("S/A - Darrin");

  var fileName = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("S/A - Darrin").getRange("A19").getDisplayValue();


  var folder = DriveApp.getFoldersByName("Sales Agreements").next();

  var Blob = sheetName.getParent().getBlob().getAs('application/pdf');

  folder.createFile(Blob).setName(fileName);

  var ssID = SpreadsheetApp.getActiveSpreadsheet().getId();

  var sheetName = SpreadsheetApp.getActiveSpreadsheet().getName();

  //var email = Session.getUser().getEmail();

  var emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("S/A - Darrin").getRange("F6");

  var emailAddress = emailRange.getValue();

  var stockNumber = ss.getRange("B7").getValue();
  var styledStockNumber = "<span style='color:red; font-weight:bold;'>" + stockNumber + "</span>";

  var subject = "Darrin " + stockNumber;

    var htmlBody =
    styledStockNumber + " (Your Reference)<br><br>" +
    "Please click the below link (Open the form) and complete the declaration form.<br><br>" +
    "You will need the reference above (in red) to compete the form (4 numbers a space and the post code), " + "you can copy and paste it as it needs to be an identical match.<br><br>" +
    "<a href='https://fill.boloforms.com/signature/9fd2152e-e490-4351-a4a8-56ab8287595f?p=view'>Open the form</a><br><br>" +
        "Please consider the environment before printing this e-mail.";

var requestData = { "method": "GET", "headers": { "Authorization": "Bearer " + ScriptApp.getOAuthToken() } };
    var shID = getSheetID("S/A - Darrin") //Get Sheet ID of sheet name "Master"
    var url = "https://docs.google.com/spreadsheets/d/" + ssID + "/export?format=pdf&id=" + ssID + "&gid=" + shID;

    var result = UrlFetchApp.fetch(url, requestData);
    var contents = result.getContent();

    var bcc = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("S/A - Darrin").getRange("F5").getDisplayValues().flat().join(","); 
    var filename = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("S/A - Darrin").getRange("A19").getDisplayValue();

    var myFile2 = DriveApp.getFileById("1T8REu_XSwfCV-dZz5ftV0KV_zTDe67oB");
    
    MailApp.sendEmail({
  to: emailAddress,
  subject: subject,
  htmlBody: htmlBody,
  attachments: [result.getBlob().setName(`${filename}.pdf`),myFile2],
  bcc: bcc
});
}

你的代码存在几个关键问题，导致附件添加失败或脚本报错：

• 未定义的变量与方法调用错误：

  1. 第一行var ssID = SpreadsheetApp.getActiveSpreadsheet().getId;缺少括号，正确写法是getId()，否则无法获取表格ID。
  2. var stockNumber = ss.getRange("B7").getValue();中的ss未定义，需要先获取当前表格对象。
  3. getSheetID函数未实现，需要补充该函数来获取指定工作表的gid。

• 附件格式错误：
  MailApp.sendEmail的attachments参数需要接收Blob对象，而myFile2是Drive File对象，需调用getBlob()转换。

• 变量覆盖问题：
  你重新定义了sheetName为表格名称（字符串），覆盖了之前获取的Sheet对象，后续若使用该变量会出错。

以下是修复后的完整代码：

function Email11() {
  // 获取当前表格对象，避免重复调用
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ssID = ss.getId();
  var targetSheet = ss.getSheetByName("S/A - Darrin");

  // 获取文件名
  var fileName = targetSheet.getRange("A19").getDisplayValue();

  // 将表格保存为PDF到指定文件夹
  var folder = DriveApp.getFoldersByName("Sales Agreements").next();
  var sheetBlob = ss.getBlob().getAs('application/pdf');
  folder.createFile(sheetBlob).setName(fileName);

  // 获取收件人邮箱
  var emailAddress = targetSheet.getRange("F6").getValue();

  // 获取库存编号并格式化
  var stockNumber = targetSheet.getRange("B7").getValue();
  var styledStockNumber = "<span style='color:red; font-weight:bold;'>" + stockNumber + "</span>";

  // 邮件主题与内容
  var subject = "Darrin " + stockNumber;
  var htmlBody =
    styledStockNumber + " (Your Reference)<br><br>" +
    "请点击下方链接打开并填写声明表格。<br><br>" +
    "你需要使用上方红色的参考编号（4位数字加空格和邮编），可以直接复制粘贴，确保完全匹配。<br><br>" +
    "打开表格<br><br>" +
    "请考虑环保，如需打印请谨慎。";

  // 获取工作表gid并导出PDF
  var shID = getSheetID("S/A - Darrin", ss);
  var url = "https://docs.google.com/spreadsheets/d/" + ssID + "/export?format=pdf&id=" + ssID + "&gid=" + shID;
  var requestData = { "method": "GET", "headers": { "Authorization": "Bearer " + ScriptApp.getOAuthToken() } };
  var result = UrlFetchApp.fetch(url, requestData);

  // 获取BCC邮箱
  var bcc = targetSheet.getRange("F5").getDisplayValues().flat().join(",");
  var filename = targetSheet.getRange("A19").getDisplayValue();

  // 获取Drive中的PDF文件并转为Blob
  var myFile2 = DriveApp.getFileById("1T8REu_XSwfCV-dZz5ftV0KV_zTDe67oB").getBlob();

  // 发送邮件
  MailApp.sendEmail({
    to: emailAddress,
    subject: subject,
    htmlBody: htmlBody,
    attachments: [result.getBlob().setName(`${filename}.pdf`), myFile2],
    bcc: bcc
  });
}

// 补充获取工作表gid的函数
function getSheetID(sheetName, ss) {
  var sheets = ss.getSheets();
  for (var i = 0; i < sheets.length; i++) {
    if (sheets[i].getName() === sheetName) {
      return sheets[i].getSheetId();
    }
  }
  return null;
}

1. 统一获取表格对象ss，减少重复API调用，提升效率。
2. 修复getId()的调用错误，补充getSheetID函数实现。
3. 将myFile2转换为Blob对象，符合邮件附件要求。
4. 修正未定义变量ss的问题，使用targetSheet操作指定工作表。
5. 优化变量命名，避免变量覆盖问题。

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