Search code examples
google-apps-scriptgoogle-sheetsgmail

Google App Script: send an email reference an image

For example, I am using Google App Script to send emails, and the email addresses, email contents are from google sheet (sheet1),

enter image description here

Just an code example (if the footer image in Google drive)

function sendEmail() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  
  var cell = ss.getActiveCell();
  var row = cell.getRow();
  var column = cell.getColumn();
  var lr = ss.getLastRow();

  for (var i = 2;i<=lr;i++){
  var assignmentName = sheet.getRange(i, 2).getValue();
  var emailAddress = sheet.getRange(i, 1).getValue();
  
  var image1 = DriveApp.getFileById("fileID").getBlob();
  var message = "Hi Mr/Mrs,<br /><br />Bellow is your info... <br><img src='cid:Footerimage'> <br>";

  
  GmailApp.sendEmail(emailAddress,"A Test Email", message, 
   {
     htmlBody: message,
     inlineImages:
      {
        Footerimage: image1
      }
    }
   );
}

my question is how can I reference this footer image in my google app script if the footer image is from a cell that in the same google sheet(sheet2).

enter image description here

Solution

  • I believe your goal is as follows.

    • You want to retrieve the image data from the image embedded into a cell. And, you want to send an email using the retrieved image as the footer (in this case, you want to use the image as the inline image).

    Issue and workaround:

    Unfortunately, in the current stage, there is no method for directly retrieving the image data from the image embedded into a cell. So in this case, as a workaround, I would like to propose a sample script using the method I have answered here.

    About your showing script, when getValue is used in a loop, the process cost becomes high. And when you use one image, var image1 = DriveApp.getFileById("fileID").getBlob(); is not required to be included in the loop.

    Usage:

    In this workaround, in order to retrieve the image data from the image embedded into a cell, a Google Apps Script library is used.

    1. Install Google Apps Script library.

    Please install the Google Apps Script library. About the method for installing the library, you can see it at here.

    2. Sample script.

    function myFunction() {
  // Retrieve image data from the image embeded into a cell.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const res = DocsServiceApp.openBySpreadsheetId(ss.getId()).getSheetByName("Sheet2").getImages();
  const obj = res.find(({ range }) => range.a1Notation == "A1");
  if (!obj) throw new Error("Image was not found.");
  const imageBlob = obj.image.blob;

  // Retrieve email addresses and send emails.
  const sheet = ss.getSheetByName("Sheet1");
  const values = sheet.getRange("A2:B" + sheet.getLastRow()).getValues();
  values.forEach(([emailAddress, assignmentName]) => {
    // In your showing script, "assignmentName" is not used. 
    const sampleBody = "A Test Email";
    const message = "Hi Mr/Mrs,<br /><br />Bellow is your info... <br><img src='cid:Footerimage'> <br>";
    GmailApp.sendEmail(emailAddress, "A Test Email", message, {
      htmlBody: message,
      inlineImages: { Footerimage: imageBlob }
    });
  });
}

    • When this script is run, the image data is retrieved from the cell "A1" of "Sheet2" and the emails are sent using the email addresses retrieved from the column "A" of "Sheet1". At that time, the image data is appended to the email as the footer using the inline-image.

    • From your showing script, message is sent as both the text body and the HTML body. Please be careful this.

    Note:

    • In this sample script, it supposes that the email addresses are put to the column "A" of "Sheet1" and the image data is put to a cell "A1" of "Sheet2". When you changed this situation, the script might not be able to be used. Please be careful about this.

    References: