Populate Google Docs Template with Spreadsheet Data and Named Images from Drive

Other Questions Google docs
1

I built a registration system using Apps Script that collects user information and digital signatures. When someone fills out the form, their data goes to a Google Sheet and their signature image gets saved to a Drive folder. The signature file gets named using a unique submission ID that matches the row ID in the spreadsheet.

I want to create a system that takes data from the spreadsheet and combines it with the matching signature image to fill out a Google Docs template. After everything is populated, the document should be converted to PDF format.

Here’s my current code that handles the data population and PDF conversion:

function generateBulkDocuments() {
  const templateFile = DriveApp.getFileById("template-doc-id-here");
  const workingFolder = DriveApp.getFolderById("temp-folder-id");
  const outputFolder = DriveApp.getFolderById("final-pdf-folder-id");
  const dataSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("FormData");
  
  const records = dataSheet.getRange(2, 1, dataSheet.getLastRow()-1, 15).getDisplayValues();
  
  let statusLog = [];
  records.forEach(record => {
    try {
      generateDocument(record[2], record[3], record[7], record[8], record[4], record[9], record[12], record[10], record[11], record[5], record[6], record[13], record[1], record[0], record[2], templateFile, workingFolder, outputFolder);
      statusLog.push(["Success"]);
    } catch(error) {
      statusLog.push(["Failed"]);
    }
  });
  
  dataSheet.getRange(2, 16, dataSheet.getLastRow()-1, 1).setValues(statusLog);
}

function generateDocument(firstName, lastName, birthPlace, birthDate, gender, address, postal, city, state, email, phone, social, uniqueId, timestamp, fileName, templateFile, workingFolder, outputFolder) {
  const workingCopy = templateFile.makeCopy(workingFolder);
  const docInstance = DocumentApp.openById(workingCopy.getId());
  const docBody = docInstance.getBody();
  
  docBody.replaceText("{firstName}", firstName);
  docBody.replaceText("{lastName}", lastName);
  docBody.replaceText("{birthPlace}", birthPlace);
  docBody.replaceText("{birthDate}", birthDate);
  docBody.replaceText("{gender}", gender);
  docBody.replaceText("{address}", address);
  docBody.replaceText("{postal}", postal);
  docBody.replaceText("{city}", city);
  docBody.replaceText("{state}", state);
  docBody.replaceText("{email}", email);
  docBody.replaceText("{phone}", phone);
  docBody.replaceText("{social}", social);
  docBody.replaceText("{timestamp}", timestamp);
  docBody.replaceText("{uniqueId}", uniqueId);
  
  docInstance.saveAndClose();
  const pdfBlob = workingCopy.getAs(MimeType.PDF);
  outputFolder.createFile(pdfBlob).setName(fileName);
  workingCopy.setTrashed(true);
}

The problem is I need to also grab the signature image file and insert it into the document. The script should find an image file that has the same name as the unique ID and replace a placeholder text like {signatureImage} with the actual image.

I tried adding some code to handle the image insertion but now the PDF conversion part stops working. The document gets filled with data and the image appears correctly, but it won’t create the PDF file anymore.

How can I make this work properly so both the image insertion and PDF generation happen successfully?

2

sounds like a timing issue - the doc isn’t fully processed before PDF conversion. add a small delay after inserting the image with Utilities.sleep(1000), then call saveAndClose(). also make sure you’re calling docInstance.saveAndClose() again after image insertion, not just after text replacements.

3

This sounds like an image insertion issue. When you add images to a Google Doc, it messes with the document structure and can break PDF conversion.

I ran into the same thing. Don’t try replacing placeholder text directly with images - it doesn’t work well. Instead, use findText() to locate your placeholder, delete it, then use insertImage() at that exact spot. Also double-check you’re grabbing the image blob right from Drive: DriveApp.getFilesByName(uniqueId).next().getBlob().

Order matters here: replace all your text first, then do images, save the doc, give it a second to process, then convert to PDF. Don’t try accessing the file while Google’s still working on it in the background.

4

I’ve hit this exact problem before. The issue is usually how you’re searching for the image file. Since your signature gets named with the unique ID, you need to modify your generateDocument function to handle images.

Here’s what worked for me: After your text replacements but before saveAndClose(), search for the image using DriveApp.getFilesByName(uniqueId + '.png') (or whatever extension you’re using). Find the placeholder with docBody.findText('{signatureImage}'), get the element, and replace it using insertInlineImage().

The key fix was flushing document changes with docInstance.saveAndClose() after inserting the image, then reopening briefly before PDF conversion. Also double-check that your image files actually exist in Drive with the exact naming you expect - missing files make the whole process fail silently.