Google Sheets - Image from URL is not received on email using Apps Script

Question

I will explain what I'm trying to do.

I need to send a unique QR code per each row to the respective email IDs.

Below is the screenshot of the Google Sheet format:

Below is the App Script code (This is the same code I fetched from Google Developer).

obj.forEach(function(row, rowIdx){
if (row[EMAIL_SENT_COL] == ''){
  try {
    const msgObj = fillInTemplateFromObject_(emailTemplate.message, row);
    GmailApp.sendEmail(row[RECIPIENT_COL], msgObj.subject, msgObj.text, {
      htmlBody: msgObj.html,
      attachments: emailTemplate.attachments,
      inlineImages: emailTemplate.inlineImages
    });
    out.push([new Date()]);
  } catch(e) {
    out.push([e.message]);
  }
} else {
  out.push([row[EMAIL_SENT_COL]]);
}

});

function getGmailTemplateFromDrafts_(subject_line){
try {
  // get drafts
  const drafts = GmailApp.getDrafts();
  // filter the drafts that match subject line
  const draft = drafts.filter(subjectFilter_(subject_line))[0];
  // get the message object
  const msg = draft.getMessage();
  // Handles inline images and attachments so they can be included in the merge
  // Based on https://stackoverflow.com/a/65813881/1027723
  // Gets all attachments and inline image attachments
  const allInlineImages = draft.getMessage().getAttachments({includeInlineImages: true,includeAttachments:true});
  const attachments = draft.getMessage().getAttachments({includeInlineImages: true});
  const htmlBody = msg.getBody(); 

  // Creates an inline image object with the image name as key 
  // (can't rely on image index as array based on insert order)
  const img_obj = allInlineImages.reduce((obj, i) => (obj[i.getName()] = i, obj) ,{});

  //Regexp searches for all img string positions with cid
  const imgexp = RegExp('<img.*?src="cid:(.*?)".*?alt="(.*?)"[^\>]+>', 'g');
  const matches = [...htmlBody.matchAll(imgexp)];

  //Initiates the allInlineImages object
  const inlineImagesObj = {};
  // built an inlineImagesObj from inline image matches
  matches.forEach(match => inlineImagesObj[match[1]] = img_obj[match[2]]);

  return {message: {subject: subject_line, text: msg.getPlainBody(), html:htmlBody}, 
          attachments: attachments, inlineImages: inlineImagesObj };
} catch(e) {
  throw new Error("Oops - can't find Gmail draft");
}

Screenshot of email body:

Screenshot of email received:

QR code is missing in the email. I feel there is some issue with the image data fetched. I am very new to App Script, in fact, this is the first thing I am trying out and I might be missing on few of the basics.

Let me know if any additional info is needed. Thanks in advance.

Answer 1

From your showing Spreadsheet image, "Screenshot of email body:" and the script of "Below is the App Script code (This is the same code I fetched from Google Developer).", as a simple modification, how about the following modification? In this modification, the function sendEmails of https://developers.google.com/apps-script/samples/automations/mail-merge#code-source is modified.

From:

const obj = data.map(r => (heads.reduce((o, k, i) => (o[k] = r[i] || '', o), {})));

To:

const obj = data.map(r => {
  const temp = heads.reduce((o, k, i) => (o[k] = r[i] || '', o), {});
  temp["QR code"] = `<img src="${temp["URL"]}">`;
  return temp;
});

Testing:

When this modified script is tested, the following result is obtained.

Note:

• In this modification, from your Spreadsheet image, the value of "QR code" is created using the value of "URL" as an HTML tag. So, when you change the header titles, this modification might not be able to be used. Please be careful about this.