How to embed Google Drive images into PDFs generated from Google Sheets data?

Other Questions Google docs
1

I’m working on a project where I need to create PDFs from Google Sheets data. Each row in my spreadsheet is transformed into a PDF using a Google Docs template. One of the columns holds URLs to images stored on Google Drive. I want these images to be visible in the PDFs, but so far I can only display the URL as plain text. I tried using a method I found online, yet it didn’t work. I also attempted to show the image directly in the spreadsheet before transferring it to the PDF, but it only showed a placeholder text ‘Cellimage’. Can anyone advise on how to properly embed these images in the PDFs? I’m really stuck and appreciate any help.

Here’s a simplified code snippet of what I’m working with:

function createPDF(row) {
  let template = DriveApp.getFileById('template_id');
  let newDoc = template.makeCopy();
  let body = newDoc.getBody();

  // Replace placeholders with data
  body.replaceText('<<Name>>', row['Name']);
  body.replaceText('<<Date>>', row['Date']);

  // Attempt to insert image
  let imageUrl = row['ImageUrl'];
  let image = UrlFetchApp.fetch(imageUrl).getBlob();
  body.appendImage(image);

  // Create PDF
  let pdfFile = DriveApp.createFile(newDoc.getAs('application/pdf'));
  newDoc.setTrashed(true);
}

Any ideas on how to get the images to display properly?

2

hey ZoeStar42, i had a similar issue. try using the Advanced Drive Service instead. enable it in Apps Script, then use Drive.Files.get() to fetch the file metadata. you can then use the webContentLink property to get a direct URL for the image. should work better than UrlFetchApp. good luck!

3

I’ve dealt with this exact problem before, and I found a workaround that might help you out. Instead of using the URL directly, try converting the image to base64 first. Here’s what worked for me:

Fetch the image using UrlFetchApp, convert the blob to base64, and then insert the base64 string into your document as an inline image.

For example:

function createPDF(row) {
  // ... your existing code ...

  let imageUrl = row['ImageUrl'];
  let imageBlob = UrlFetchApp.fetch(imageUrl).getBlob();
  let base64String = Utilities.base64Encode(imageBlob.getBytes());
  let inlineImage = 'data:image/png;base64,' + base64String;

  body.appendParagraph('').appendInlineImage(inlineImage);

  // ... rest of your code ...
}

This method bypasses permission issues and should work regardless of where the image is hosted. Just ensure your script has permission to access external URLs. Hope this helps!

4

I encountered a similar challenge in my work. One solution that worked for me was utilizing Google Apps Script’s built-in DriveApp class. Instead of UrlFetchApp, try this approach:

let imageFile = DriveApp.getFileById(row['ImageId']);
let imageBlob = imageFile.getBlob();
body.appendImage(imageBlob);

This method assumes you have the file ID of the image in your spreadsheet, not just the URL. It directly accesses the image file in Drive, which should circumvent any permission issues. Remember to grant necessary permissions to your script. If you’re still facing issues, double-check that your template document allows image insertions and that your script has sufficient authorization to access both the spreadsheet and Drive files.