google-apps-scriptgoogle-sheetsgmail

Unable to send an email with a reference image with GAS

For example, I was using Google App Script to set up an program of sending emails based on the data from a Google sheet, and the example data here includes the email addresses (column A, Emails), email contents that will be using the data from column B and C,

enter image description here

Below is the example (if the footer image in Google drive) with issue:

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 the image as the email footer in each email I will send out using Google App Script. The target image was saved in the same google sheet but different tab(e.g.sheet2).

enter image description here

Solution

  • I believe your goal is as follows.

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

    Note:

    References: