google-sheetsgoogle-apps-scriptgoogle-sheets-apigooglesheets4

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


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: Screenshot of sheets template

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 body

Screenshot of email received: 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.


Solution

  • 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.

    enter image description here

    Note: