Google-sheets – Trying to send email from Google Sheets with image attachment

google sheetsgoogle-apps-script

I'm trying to send an email from within Google Sheets and then format it via some joins to send to an address for my Blogger account. I need to attach the image file apparently as the image isn't being added to the email when sent in non-attachment formatting, but somehow I can't seem to get the attachment from the cell.

So for example I have each cell with emailaddress|subject |summary|image but the image isn't being appended, and for the life of me the attachment isn't working, that is when the email is sent no image to Blogger or even attached to the email.

Here is the script I'm using 

// This constant is written in column C for rows for which an email
// has been sent successfully.
var EMAIL_SENT = "EMAIL_SENT";

function starwarsEmail2blog() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;  // First row of data to process
  var numRows = 20;   // Number of rows to process
  var blobs = [];
  // Fetch the range of cells A2:D2
  var dataRange = sheet.getRange(startRow, 1, numRows, 20)
  // Fetch values for each row in the Range.
  var data = dataRange.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 attachments = row[3];     // Fourth column
    var emailSent = row[4];     // Fifth column
    if (emailSent != EMAIL_SENT) {  // Prevents sending duplicates
      // var subject = "Sending emails from a Spreadsheet";
      GmailApp.sendEmail(emailAddress, subject, message, attachments);
      sheet.getRange(startRow + i, 5).setValue(EMAIL_SENT);
      // Make sure the cell is updated right away in case the script 
      // is interrupted
      SpreadsheetApp.flush();
    }
  }
}

Best Answer

As Rubén pointed out, the format of sendEmail method is different: the last parameter is an object of options such as {attachments: ...}.

Also, images inserted in a spreadsheet via "insert image" are not associated with any cell in a sheet, they are just floating over the sheet. This makes them unsuitable for your purpose of matching images to recipients listed in the sheet.

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();
Related Topic