Google-apps-script – Send email with an attachment located in Google Drive

attachmentgoogle-apps-scriptgoogle-drive

I want to send a PDF attachment located on Google Drive from a Sheet.

Code I'm using is:

function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;  // First row of data to process
  var numRows = 8;   // Number of rows to process
  // Fetch the range of cells A2:B3
  var dataRange = sheet.getRange(startRow, 1, numRows, 2)
  // Fetch values for each row in the Range.
  var file = DriveApp.getFilesByName('ScriptCarPass.pdf')
  var data = dataRange.getValues();
  for (i in data) {

    var row = data[i];
    var emailAddress = row[0];  // First column
    var message = row[1];       // Second column
    var subject = "Sending emails from a Spreadsheet";
    MailApp.sendEmail(emailAddress, subject, message, {attachments: file});
  }
}

Everything works fine except the attachment – i.e. if I run without the attachment it works fine.

I keep getting error Invalid argument: inlineImages

I've also tried with:

 attachments: [file.getAs(MimeType.PDF)]

but that gives error that .getAs isn't recognised.

Best Answer

DriveApp.getFilesByName() returns a file iterator. You therefore need to slightly modify the sendMail() arguments.

MailApp.sendEmail(emailAddress, subject, message, 
   {attachments: file.next().getBlob()}
);

Images via a URL

A way to associate images with cells is to use =image(url) formula, which requires the image to be accessible somewhere. I suggest the following approach: put the URL itself in the column for attachment, say D, and =image(D2) command elsewhere, just to have a preview of the image. Then you can obtain the image as a blob with

var image = UrlFetchApp.fetch(row[3]).getBlob()

and send it via

MailApp.sendEmail(emailAddress, subject, message, {attachments: [image]});

The full script is as follows:

function emailImage(){
  var EMAIL_SENT = "EMAIL_SENT";
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;  // First row of data to process
  var numRows = 20;   // Number of rows to process
  var data = sheet.getRange(startRow, 1, numRows, 20).getValues();
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var emailAddress = row[0];  // First column
    var subject = row[1];       // Second column
    var message = row[2];       // Third column
    var image = UrlFetchApp.fetch(row[3]).getBlob();   // Fourth column
    var emailSent = row[4];     // Fifth column
    if (emailSent != EMAIL_SENT) {  // Prevents sending duplicates
      MailApp.sendEmail(emailAddress, subject, message, {attachments: [image]});
      sheet.getRange(startRow + i, 5).setValue(EMAIL_SENT);
      // Make sure the cell is updated right away in case the script is interrupted
      SpreadsheetApp.flush();
    }
  }
}

Images on Google Drive

If the images are stored in Google Drive, they can be accessed by file Id (the long alphanumerical part of their URL). Put the Ids in column D and replace the line with var image = by

 var image = DriveApp.getFileById(row[3]).getBlob();

Gmail – Possible to access email attachment from google drive

Short answer

Yes it's possible, but it's required to extend Google Drive to be able to do so.

Explanaition

Users could authorize apps created by themselves and third party apps to access their Gmail mailbox, like a desktop/mobile email client among others, so it's possible that if the user "lose access to his gmail account" on the web, he still be able to access the messages and the attached files to them.

Google Drive is not a third party app, so if the user "lose access to his gmail account" he could not access Google Drive with that account. Besides this, Google Drive doesn't has a built-in feature to access Gmail messages attachments but it could be extended through add-ons and Google Apps Scripts.

References

Apps connected to your account - Accounts Help
Extend Google Docs, Sheets, and Forms with Apps Script - Docs editors Help