InlineImages error, how to find what is wrong? sending a picture of the spreadsheet

Question

I am trying to combine two codes that I found here: Google Sheet Range mail pdf Convert a gdoc into image

I want to achieve something that will send an email with the inline picture of specific range from a spreadsheet.

The code that I combined from two above is working very well and is sending an email but only if the image is sent as an attachments. I think that image is a blob so it should be fine to send it as it is, but somehow I am getting an error.

I would like to have the picture within the body of the email and pdf as an attachments. Do you know how it can be solved?

Error I am getting:

Exception: Invalid argument: inlineImages

/**
 * This is a method for converting all pages in a PDF file to PNG images.
 * PNG images are returned as BlobSource[].
 * IMPORTANT: This method uses Drive API. Please enable Drive API at Advanced Google services.
 * 
 * @param {Blob} blob Blob of PDF file.
 * @return {BlobSource[]} PNG blobs.
 */
async function convertPDFToPNG_(blob) {
  // Convert PDF to PNG images.
  const cdnjs = "https://cdn.jsdelivr.net/npm/pdf-lib/dist/pdf-lib.min.js";
  eval(UrlFetchApp.fetch(cdnjs).getContentText()); // Load pdf-lib
  const setTimeout = function (f, t) { // Overwrite setTimeout with Google Apps Script.
    Utilities.sleep(t);
    return f();
  }
  const data = new Uint8Array(blob.getBytes());
  const pdfData = await PDFLib.PDFDocument.load(data);
  const pageLength = pdfData.getPageCount();
  console.log(`Total pages: ${pageLength}`);
  const obj = { imageBlobs: [], fileIds: [] };
  for (let i = 0; i < pageLength; i++) {
    console.log(`Processing page: ${i + 1}`);
    const pdfDoc = await PDFLib.PDFDocument.create();
    const [page] = await pdfDoc.copyPages(pdfData, [i]);
    pdfDoc.addPage(page);
    const bytes = await pdfDoc.save();
    const blob = Utilities.newBlob([...new Int8Array(bytes)], MimeType.PDF, `sample${i + 1}.pdf`);
    const id = DriveApp.createFile(blob).getId();
    Utilities.sleep(4000); // This is used for preparing the thumbnail of the created file.
    const link = Drive.Files.get(id, { fields: "thumbnailLink" }).thumbnailLink;
    if (!link) {
      throw new Error("In this case, please increase the value of 3000 in Utilities.sleep(3000), and test it again.");
    }
    const imageBlob = UrlFetchApp.fetch(link.replace(/\=s\d*/, "=s1000")).getBlob().setName(`page${i + 1}.png`);
    obj.imageBlobs.push(imageBlob);
    obj.fileIds.push(id);
  }
  obj.fileIds.forEach(id => DriveApp.getFileById(id).setTrashed(true));
  return obj.imageBlobs;
}

// Please run this function.
async function myFunction() {

// Retrieve Spreadsheet and Sheet objects.
  const ss = SpreadsheetApp.openById("IDIDIDIDIDIDIDIDID");
  const sheet = ss.getSheetByName("ForEmail-SHEET-NAME");

  // Retrieve PDF blob.
  const url = `https://docs.google.com/spreadsheets/d/${ss.getId()}/export?format=pdf&range=B2:R52&gid=${sheet.getSheetId()}&size=6&portrait=false&`;
  const pdfBlob = UrlFetchApp.fetch(url, { headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() } }).getBlob();

  const SOURCE_TEMPLATE = pdfBlob;
  const TARGET_FOLDER = "FOLDER-ID";

  // Use a method for converting all pages in a PDF file to PNG images.
 const blob = pdfBlob
  const imageBlobs = await convertPDFToPNG_(blob);

  // As a sample, create PNG images as PNG files.
  const folder = DriveApp.getFolderById(TARGET_FOLDER);
  imageBlobs.forEach(b => folder.createFile(b));




MailApp.sendEmail({
    to: "[email protected]",
    cc: "" || null,
    bcc: "" || null,
    subject: "TEST",
    body: "TEST",
    inlineImages: imageBlobs,
    attachments: [pdfBlob],    
  });


  
}

Chiropractor

Answer 1

Modification points:

• If you want to send imageBlobs as inlineImages, the value of inlineImagesis required to be an object. Also, the property ofhtmlBody` is required to be used for showing the images.

When these points are reflected in your script, it becomes as follows.

Modified script:

In this case, your myFunction is modified.

async function myFunction() {

  // Retrieve Spreadsheet and Sheet objects.
  const ss = SpreadsheetApp.openById("IDIDIDIDIDIDIDIDID");
  const sheet = ss.getSheetByName("ForEmail-SHEET-NAME");

  // Retrieve PDF blob.
  const url = `https://docs.google.com/spreadsheets/d/${ss.getId()}/export?format=pdf&range=B2:R52&gid=${sheet.getSheetId()}&size=6&portrait=false&`;
  const pdfBlob = UrlFetchApp.fetch(url, { headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() } }).getBlob();

  const SOURCE_TEMPLATE = pdfBlob;
  const TARGET_FOLDER = "FOLDER-ID";

  // Use a method for converting all pages in a PDF file to PNG images.
  const blob = pdfBlob
  const imageBlobs = await convertPDFToPNG_(blob);

  // As a sample, create PNG images as PNG files.
  const folder = DriveApp.getFolderById(TARGET_FOLDER);
  imageBlobs.forEach(b => folder.createFile(b));

  // I modified the below script.
  const body = "TEST";
  const { inlineImages, htmlBody } = imageBlobs.reduce((o, blob, i) => {
    const k = `page${i + 1}`;
    o.inlineImages[k] = blob;
    o.htmlBody += `<img src="cid:${k}">`;
    return o;
  }, { inlineImages: {}, htmlBody: `<p>${body}</p>` });
  MailApp.sendEmail({
    to: "[email protected]",
    cc: "" || null,
    bcc: "" || null,
    subject: "TEST",
    body,
    htmlBody,
    inlineImages,
    attachments: [pdfBlob],
  });
}

• By this modification, the image blobs imageBlobs are sent as inlineImages.

Reference:

• sendEmail(message)

Thracophrygian

Thanks for your solution! It works and put an image into a body of the email, but I have one issue. Text of the body does not show up "body: "TEST"". ⇒　I am not getting TEST in email – Chiropractor

@Chiropractor Thank you for replying. From your reply, I updated my answer. Please confirm it. – Thracophrygian

Answer 2

SUGGESTED SOLUTION

You can achieve what you'd like by adding the following to your script:

  const htmlBody = `
    <html>
      <body>
        <h1>TEST</h1>
        <img src="cid:inlineImage" />
      </body>
    </html>
  `;

  const inlineImage = imageBlobs[0].setName("inlineImage");

  MailApp.sendEmail({
    to: "[email protected]",
    cc: "" || null,
    bcc: "" || null,
    subject: "TEST",
    body: "TEST",
    htmlBody: htmlBody,
    inlineImages: { inlineImage: inlineImage },
    attachments: [pdfBlob]
  });

The full code would become:

/**
 * This is a method for converting all pages in a PDF file to PNG images.
 * PNG images are returned as BlobSource[].
 * IMPORTANT: This method uses Drive API. Please enable Drive API at Advanced Google services.
 * 
 * @param {Blob} blob Blob of PDF file.
 * @return {BlobSource[]} PNG blobs.
 */
async function convertPDFToPNG_(blob) {
  const cdnjs = "https://cdn.jsdelivr.net/npm/pdf-lib/dist/pdf-lib.min.js";
  eval(UrlFetchApp.fetch(cdnjs).getContentText());
  const setTimeout = function (f, t) {
    Utilities.sleep(t);
    return f();
  }
  const data = new Uint8Array(blob.getBytes());
  const pdfData = await PDFLib.PDFDocument.load(data);
  const pageLength = pdfData.getPageCount();
  console.log(`Total pages: ${pageLength}`);
  const obj = { imageBlobs: [], fileIds: [] };
  for (let i = 0; i < pageLength; i++) {
    console.log(`Processing page: ${i + 1}`);
    const pdfDoc = await PDFLib.PDFDocument.create();
    const [page] = await pdfDoc.copyPages(pdfData, [i]);
    pdfDoc.addPage(page);
    const bytes = await pdfDoc.save();
    const blob = Utilities.newBlob([...new Int8Array(bytes)], MimeType.PDF, `sample${i + 1}.pdf`);
    const id = DriveApp.createFile(blob).getId();
    Utilities.sleep(4000);
    const link = Drive.Files.get(id, { fields: "thumbnailLink" }).thumbnailLink;
    if (!link) {
      throw new Error("In this case, please increase the value of 3000 in Utilities.sleep(3000), and test it again.");
    }
    const imageBlob = UrlFetchApp.fetch(link.replace(/\=s\d*/, "=s1000")).getBlob().setName(`page${i + 1}.png`);
    obj.imageBlobs.push(imageBlob);
    obj.fileIds.push(id);
  }
  obj.fileIds.forEach(id => DriveApp.getFileById(id).setTrashed(true));
  return obj.imageBlobs;
}

async function myFunction() {
  const ss = SpreadsheetApp.openById("IDIDIDIDIDIDIDIDID");
  const sheet = ss.getSheetByName("ForEmail-SHEET-NAME");

  const url = `https://docs.google.com/spreadsheets/d/${ss.getId()}/export?format=pdf&range=B2:R52&gid=${sheet.getSheetId()}&size=6&portrait=false&`;
  const pdfBlob = UrlFetchApp.fetch(url, { headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() } }).getBlob();

  const TARGET_FOLDER = "FOLDER-ID";

  const blob = pdfBlob;
  const imageBlobs = await convertPDFToPNG_(blob);

  const folder = DriveApp.getFolderById(TARGET_FOLDER);
  imageBlobs.forEach(b => folder.createFile(b));

  const htmlBody = `
    <html>
      <body>
        <h1>TEST</h1>
        <img src="cid:inlineImage" />
      </body>
    </html>
  `;

  const inlineImage = imageBlobs[0].setName("inlineImage");

  MailApp.sendEmail({
    to: "[email protected]",
    cc: "" || null,
    bcc: "" || null,
    subject: "TEST",
    body: "TEST",
    htmlBody: htmlBody,
    inlineImages: { inlineImage: inlineImage },
    attachments: [pdfBlob]
  });
}

This uses the htmlBody advanced parameter of sendEmail(recipient, subject, body, options) to achieve what you'd like, together with const inlineImage = imageBlobs[0].setName("inlineImage") to use the first image blob as the inline image. I've also removed const SOURCE_TEMPLATE = pdfBlob; from the script since it wasn't in use.

OUTPUT

Dexamethasone