Gmail应用程序脚本：基于Google Drive URL的附件

Question

我正在使用我在此处找到的邮件合并脚本，但是我很难找到适合附加文件的类（第14和15行）。

function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;  // First row of data to process
  var numRows = 1;   // Number of rows to process
  // Fetch the range of cells A2:B2
  var dataRange = sheet.getRange(startRow, 1, numRows, 1)
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (i in data) {
    var row = data[i];
    var emailAddress = row[3];  // First column
    var message = row[10];       // Second column
    var subject = row[6];
    var attachment = Attachment.setUrl(row[11]);
    MailApp.sendEmail(emailAddress, subject, message, {attachments: attachment});
  }
}

我已经尝试了几种附件功能的变体，但是每次都遇到了错误。行对应于Google表中的列，而行[11]是Google Drive中图像的URL。

Answer 1

这是您脚本的一个工作示例：您有2个错误：

1. 范围定义只有一个列，
2. 附件的参数

---

function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;  // First row of data to process
  var numRows = 1;   // Number of rows to process
  // Fetch the range of cells A2:B12 since you use row[11], the data must at least be 12 cells wide
  var dataRange = sheet.getRange(startRow, 1, numRows, 12);
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (i in data) {
    var row = data[i];
    var emailAddress = row[3];  // Fourth column
    var message = row[10];       // elevenths column
    var subject = row[6];
    var attachment = [UrlFetchApp.fetch(row[11]).getBlob()];
    MailApp.sendEmail(emailAddress, subject, message, {attachments: attachment});
  }
}

也应该是blobs的数组，即url必须有效，并且文件必须公开共享：示例：示例：

https://drive.google.com/uc?export=view&id=0B3qSFd3iikE3TUFFLTc5MDE0MzkwLWQxYWItNDkwNy05ZjVkLWIyZDhiZDM4MjdmMg

Answer 2

与Serge Insas的答案类似，这是我必须要做的才能绕过401错误时使用urlfetchapp.fetch

var fileUrl = row[11] // Based on OP's case

var attachment = [
   UrlFetchApp.fetch(fileUrl, {
      headers: { Authorization: 'Bearer ' + ScriptApp.getOAuthToken() },
   }).getBlob(),
]

MailApp.sendEmail(emailAddress, subject, message, {attachments: attachment});