Hello I'm trying to attach my chart from another sheet to the PDF attachment when sending an email. I still don't know how to use the image source from HTML when getting image from another sheet.

I use inlineImages:{image: img} and <img src='cid:image'> when attaching images to the email body, but it's not working when used with PDF.

function pdfSend() {
  const id = <sheet id>;
  const ss = SpreadsheetApp.openById(id);
  //const ss = SpreadsheetApp.getActive();
  const sheet = ss.getSheetByName('Employee Report');
  const data = sheet.getDataRange().getValues().slice(1);

  const gs = ss.getSheetByName('Graph');
  var chart = gs.getCharts();
  var img = chart[0].getAs("image/png");

  data.forEach((row, i)=> {
    let email = row[1];

    if (email != "" ) {
      let html = `<h1>Hello ${row[0]}</h1>`;
      html  = `<div>Is your email correct? ${row[1]}</div>`;
      html  = "<br><p align='center'><imgsrc='???'>/</p> <br><br>";
      const blob = Utilities.newBlob(html,MimeType.HTML);
      blob.setName(`${row[0]}.pdf`);
      const subject = 'Example PDF';
      MailApp.sendEmail({
        to: email,
        subject: subject,
        htmlBody: html,
        attachments:[blob.getAs(MimeType.PDF)]
      });

      sheet.getRange(i 2,4).setValue('Sent');
    }
  });
}

CodePudding user response：

I believe your goal is as follows.

• You want to include var img = chart[0].getAs("image/png") into the PDF file of the attachment file for email.

In this case, how about the following modification?

Modified script:

function pdfSend() {
  const id = "<sheet id>";
  const ss = SpreadsheetApp.openById(id);
  //const ss = SpreadsheetApp.getActive();
  const sheet = ss.getSheetByName('Employee Report');
  const data = sheet.getDataRange().getValues().slice(1);

  const gs = ss.getSheetByName('Graph');
  var chart = gs.getCharts();

  var img = `data:image/png;base64,${Utilities.base64Encode(chart[0].getAs("image/png").getBytes())}`; // Modified

  data.forEach((row, i) => {
    let email = row[1];

    if (email != "") {
      let html = `<h1>Hello ${row[0]}</h1>`;
      html  = `<div>Is your email correct? ${row[1]}</div>`;

      html  = `<br><p align='center'><img src='${img}'>/</p> <br><br>`; // Modified

      const blob = Utilities.newBlob(html, MimeType.HTML);
      blob.setName(`${row[0]}.pdf`);
      const subject = 'Example PDF';
      MailApp.sendEmail({
        to: email,
        subject: subject,
        htmlBody: html,
        attachments: [blob.getAs(MimeType.PDF)]
      });

      sheet.getRange(i   2, 4).setValue('Sent');
    }
  });
}

• In this modification, the chart is included in img tag as the data URL.

Reference:

• base64Encode(data)