Automatically Populate Google Docs 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 info from a spreadsheet but in a more flexible way than what I’ve seen before.

I found some code that replaces specific tokens in a doc with spreadsheet data, but it’s all hardcoded. Like this:

body.replaceText('{{First Name}}', row[0]);
body.replaceText('{{Last Name}}', row[1]);
body.replaceText('{{Position}}', row[2]);

What I’m after is something that can handle any token in the spreadsheet without me having to list them all out in the code. I’ve seen a similar approach for Gmail templates, but I’m not sure how to adapt it for Google Docs.

Has anyone done something like this before? I’d love to see an example of how to make it work with Docs. Maybe there’s a way to loop through all the columns in the spreadsheet and replace matching tokens in the doc?

Thanks for any help or ideas you can share!

2

I’ve tackled a similar challenge before, and here’s a more flexible approach you might find useful:

function populateDoc() {
  var doc = DocumentApp.getActiveDocument();
  var body = doc.getBody();
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var headers = data[0];
  var values = data[1];  // Assuming you're using the second row for values

  headers.forEach((header, index) => {
    var token = '{{' + header + '}}';
    body.replaceText(token, values[index] || '');
  });
}

This script dynamically handles any token in your spreadsheet without hardcoding. It assumes your headers are in the first row and values in the second. You can adjust the values variable if needed. Remember to add error handling for production use. Hope this helps streamline your workflow!

3

I’ve used a similar approach in my work, and here’s a script that might be helpful:

function dynamicPopulate() {
  var doc = DocumentApp.getActiveDocument();
  var body = doc.getBody();
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var headers = data[0];
  var values = data[1];

  for (var i = 0; i < headers.length; i++) {
    var token = '{{' + headers[i] + '}}';
    body.replaceText(token, values[i] || '');
  }
}

This script dynamically handles any token in your spreadsheet. It assumes headers are in the first row and values in the second. You can adjust the values variable if needed.

One tip: consider adding error handling for production use. Also, you might want to add a feature to handle multiple rows of data if that’s something you need.

Let me know if you need any clarification or have questions about implementing this!

4

hey jack, i’ve done something similar before! here’s a quick script that might help:

function fillDoc() {
  var doc = DocumentApp.getActiveDocument();
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var headers = data[0];
  
  headers.forEach((header, i) => {
    doc.getBody().replaceText(`{{${header}}}`, data[1][i]);
  });
}

this loops thru all columns and replaces matching tokens. hope it helps!