Auto-Populate Google Docs with Spreadsheet Data

Other Questions Google docs
1

My script creates docs from a template but leaves placeholder text unchanged. Below is an alternative code snippet:

function initializeMenu() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('DocBuilder').addItem('Generate Document', 'generateDocument').addToUi();
}

function generateDocument() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Responses');
  const templateFile = DriveApp.getFileById('TEMPLATE_ID');
  const targetFolder = DriveApp.getFolderById('FOLDER_ID');
  const records = sheet.getDataRange().getValues();
  
  for (let i = 1; i < records.length; i++) {
    if (records[i][6]) continue;
    let newFile = templateFile.makeCopy(records[i][0] + ' Record', targetFolder);
    let doc = DocumentApp.openById(newFile.getId());
    let body = doc.getBody();
    body.replaceText('{{Name}}', records[i][0]);
    body.replaceText('{{Pronouns}}', records[i][1]);
    body.replaceText('{{Email}}', records[i][2]);
    body.replaceText('{{SchoolEmail}}', records[i][3]);
    body.replaceText('{{Phone}}', records[i][4]);
    body.replaceText('{{Notes}}', records[i][5]);
    doc.saveAndClose();
    sheet.getRange(i + 1, 7).setValue(doc.getUrl());
  }
}
2

hey i tink you might have extra whitespaces in the template, causing replacement fails. tried regex matching and it helpd in my case. give that a shot if placeholders wont swap correctly, might solve the issuw.

3

I ran into a similar problem when trying to automatically populate docs from a spreadsheet. In my testing, I found that ensuring complete accuracy in the placeholder patterns was key. I had issues where even a slight deviation in punctuation or trailing spaces in the template would cause the replacements to fail. I solved this by carefully reviewing my template and script, also adding logging to check the values at runtime. This helped confirm that the fields were mapped correctly, and tweaking the template formatting made a significant difference in ensuring the replacement worked as expected.

4

In my experience, making sure the text within the placeholders exactly matches between the template and the script was vital. I once faced issues where invisible formatting characters in the template caused partial matches and errors during text replacement. I solved this by reformatting the placeholders to standard text and running the script in a test environment with extensive logging. Seeing where the values diverged helped me streamline the matching process, ensuring that the placeholders were replaced correctly in every generated document.