Populate Google Docs Dynamically Using Spreadsheet Data

Other Questions Google docs
1

Hey everyone,

I’m trying to figure out how to make my Google Docs smarter. I want them to automatically fill in with info from a spreadsheet, but in a more flexible way than what I’ve seen before.

Here’s what I’m thinking:

Instead of hard-coding specific tokens in the script, I’d love if it could just replace any token it finds in the doc with the matching column from my spreadsheet. Kind of like a mail merge for docs.

I’ve seen something similar for Gmail templates, but I’m not sure how to adapt it for Google Docs. Has anyone done this before? Any tips or code snippets would be a huge help!

Here’s a basic example of what I’m hoping to achieve:

function updateDoc(doc, data) {
  let content = doc.getBody().getText();
  Object.keys(data).forEach(key => {
    let token = `{{${key}}}`;
    content = content.replace(new RegExp(token, 'g'), data[key]);
  });
  doc.getBody().setText(content);
}

This is just a starting point, though. I’m sure there’s a better way to do it. Any ideas?

2

hey swimmin shark, that’s a cool idea! i’ve done somethin similar before. instead of regex, try using doc.getBody().findText() to locate tokens. then use getElement().asText().setText() to replace em. it’s faster and works better with formatting. good luck with ur project!

3

I’ve implemented a similar system in my work. One approach that proved effective was using Google Apps Script’s built-in TextFinder class. It’s more efficient than regex for large documents. Here’s a basic outline:

function updateDoc(doc, data) {
  var body = doc.getBody();
  for (var key in data) {
    var token = '{{' + key + '}}';
    var finder = body.createTextFinder(token);
    finder.replaceAllWith(data[key]);
  }
}

This method preserves formatting and handles multiple occurrences of the same token. You might also consider adding error handling for missing data and implementing a way to process nested data structures for more complex use cases.

4

I’ve actually tackled a similar challenge in my work. One thing that really helped was using named ranges in the spreadsheet. You can assign names to specific columns or ranges, then reference those names in your script. It makes the whole process more robust and easier to maintain.

For the actual replacement, I found that using a combination of body.replaceText() and paragraph.replaceText() worked well. It allowed me to handle both single-line and multi-line replacements smoothly.

Here’s a rough outline of what I did:

function updateDoc(doc, sheet) {
var body = doc.getBody();
var namedRanges = sheet.getNamedRanges();

namedRanges.forEach(function(range) {
var name = range.getName();
var value = range.getRange().getValue();
body.replaceText(‘{{’ + name + ‘}}’, value);
});
}

This approach gives you a lot of flexibility and keeps your spreadsheet data nicely organized. Hope this helps!