CODEWITHSUNDEEP
google-apps-scriptgmailgoogle-sheets
sclevy
sclevy

Reputation: 11

Gmail App Scripts: Attachments based on Google Drive URL

I am using a mail merger script I found here, but I'm having trouble finding the right class to attach the file (rows 14 and 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});
  }
}

I've tried a handful of variations for the attachment function, but I've gotten an error each time. The rows correspond to columns in a Google Sheet, and row[11] is the URL for an image in google drive.

Upvotes: 1

Views: 3444

Answers (2)

0xh8h
0xh8h

Reputation: 3509

Similar to Serge insas's answer, this is what I had to do in order to bypass the 401 error when using 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});

Upvotes: 0

Serge insas
Serge insas

Reputation: 46802

here is a working example of your script : you had 2 errors :

  1. the range definition had only one column
  2. the argument of attachment should be an array of blobs
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});
  }
}

note also that url must be valid and that the file must be publicly shared : example :

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

Upvotes: 1

Related Questions