Apps Script: Automating text replacement in Google Docs using Google Sheets data

Other Questions Google docs
1

I’ve been working on a project to create job offer letters using Google Docs and Sheets. Right now I have merge fields in my Doc template like {{FirstName}} and {{StartDate}}. These fields and their values are stored in a Google Sheet.

My current script replaces these fields one by one but I want to make it more flexible. I’m trying to figure out how to loop through all the merge fields in my Sheet and automatically replace them in the Doc.

Here’s what I’ve got so far:

function updateOfferLetter() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('MergeData');
  const template = DriveApp.getFileById('templateId');
  const copy = template.makeCopy('New Offer Letter');
  const doc = DocumentApp.openById(copy.getId());
  
  const mergeFields = sheet.getRange('A1:B10').getValues();
  
  for (let field of mergeFields) {
    if (field[0] && field[1]) {
      doc.getBody().replaceText('{{' + field[0] + '}}', field[1]);
    }
  }
  
  doc.saveAndClose();
}

This code isn’t working quite right. Can anyone help me figure out how to properly loop through my Sheet data and update the Doc? Thanks!

2

hey tom, looks like ur on the right track! one thing to try is using the replaceAllText method instead of replaceText. it might be faster for multiple replacements. also, double-check ur sheet range - ‘A1:B10’ might miss some data if u have more rows. good luck with ur project!

3

Your approach is sound, but there are a few tweaks that could improve your script’s functionality. First, consider using getDataRange() instead of a fixed range to ensure you’re capturing all your data. You might also want to add error handling to deal with cases where merge fields aren’t found in the document.

Here’s a slightly modified version of your script:

function updateOfferLetter() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('MergeData');
  const template = DriveApp.getFileById('templateId');
  const copy = template.makeCopy('New Offer Letter');
  const doc = DocumentApp.openById(copy.getId());
  
  const mergeFields = sheet.getDataRange().getValues();
  
  mergeFields.forEach(field => {
    if (field[0] && field[1]) {
      doc.getBody().replaceText('{{' + field[0] + '}}', field[1].toString());
    }
  });
  
  doc.saveAndClose();
}

This should loop through all your merge fields effectively. Remember to test thoroughly with different data sets to ensure it’s working as expected.

4

I’ve used a similar approach for generating personalized certificates. One thing that really helped was implementing a try-catch block for each replacement. Sometimes, if a placeholder isn’t in the doc, it can throw off the whole script.

Here’s a snippet that might be useful:

mergeFields.forEach(field => {
  if (field[0] && field[1]) {
    try {
      doc.getBody().replaceText('{{' + field[0] + '}}', field[1].toString());
    } catch (e) {
      Logger.log('Error replacing ' + field[0] + ': ' + e.toString());
    }
  }
});

This way, if one replacement fails, it logs the error but continues with the rest. It’s been a lifesaver when dealing with large batches of documents. Also, consider adding a progress bar or some kind of status update if you’re processing a lot of docs. It helps to know how far along the script is, especially for larger jobs.