Google-sheets – How to insert an image from Google Drive into email body with script

google sheetsgoogle-apps-scriptgoogle-drive

Could anyone help me?

I've created a Google Form that uploads a file (image) to a private drive folder, the form also sends responses to a Google Sheet including the image's URL. The code below gets the file id and sends the image as an attachment but it doesn't do it in the body of the email. The folder must be private.

function sendImage(){
  var sheet = SpreadsheetApp.getActive().getActiveSheet();
  var fileUrl = sheet.getRange('C2').getValue();
  var index = fileUrl.indexOf('=') + 1;
  var fileId = fileUrl.substring(index);
  var img = DriveApp.getFileById(fileId).getBlob();

 MailApp.sendEmail({
    name: "my Name",
    to: 'myEmail@email.com',
    subject: "Test 1",
    body:  img,
    attachments: img,
     });
}

Best Answer

How about this modification? You can send an email including images using inlineImages of Advanced parameters at MailApp.sendEmail(). You have already been able to retrieve the image blob. In this modified script, the blob is used.

Modified script:

function sendImage(){
  var sheet = SpreadsheetApp.getActive().getActiveSheet();
  var fileUrl = sheet.getRange('C2').getValue();
  var index = fileUrl.indexOf('=') + 1;
  var fileId = fileUrl.substring(index);
  var img = DriveApp.getFileById(fileId).getBlob();

  MailApp.sendEmail({
    name: "my Name",
    to: 'myEmail@email.com',
    subject: "Test 1",
    htmlBody: "<img src=\"cid:sampleImage\">", // Modified
    inlineImages: {sampleImage: img} // Modified
  });
}

Reference:

inlineImages of Advanced parameters

Related Solutions

Gmail – Automating Predictable Received Gmail Attachments

You can get part of the way there with just Gmail. Add in something like IFTTT and you can get a little further. Note, however, that it's probably not going to be as intelligent as you'd like it and you may occasionally get false matches.

Here's my take on it:

Send an automatic received receipt exclusively to emails that send time-sheet type image or pdf attachments.

You'll need to turn on the "Canned Responses" Gmail Lab. Create a canned response with your response text ("Received").

Then, adjust or create a filter:

to:myEmail@gmail.com has:attachment .pdf

That should pick up any PDF file that's sent to you. (You could get a false match if someone sends some other sort of attachment and puts the string ".pdf" somewhere in their message.)

If you want to match on more file types, you can do something like

.png OR .jpg OR .gif OR .tiff OR .jpeg OR .tiff

For the action, choose "Send a canned response" and, obviously, choose the canned response you created earlier.

Label the gmail with 1 or 2 separate labels like "time-sheets" and "downloaded" (read next part)

In order to label a message via filter with two separate labels, you'll need to have two separate filters. Just copy the criteria for one filter to the other. (If you're ambitious, you can export your filters, edit the resulting XML, then re-import them.)

send the attachment to a specific google drive folder

You can easily do that with an IFTTT recipe. The Gmail channel lets you trigger on a Gmail search and then you can use that to save to Google Drive. Here's a recipe that's similar to what you'd want to do.

The trouble is, this probably isn't going to be sufficient.

You need to check that the attachment is an actual timesheet and that it's signed. You'll need human intervention for that. It'll also be easy to get messed up when someone sends a PDF that's not a timesheet. You might be able to ameliorate that a bit by having your senders put a specific keyword in the subject which you can use in your filter, but then you still have plenty of potential for false-positives (when they use the keyword incorrectly) or false-negatives (when they forget to use it).

You probably need to find some sort of process flow system that can automate your process, but if that's the nature of your question I'm afraid it's outside the ken of this site. (This site is for questions about apps you're already using, not help in finding an app to use.)

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

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