I have a Google spreadsheet that contains customer wine information. I want to transfer all this data into a document template for printing purposes. My current script only transfers the first row successfully, but the remaining rows don’t appear in the final document.
I think there’s an issue with how I’m handling the loop through the data array. This is my first time working with Apps Script so I might be missing something basic.
Here’s my current code:
function onOpen() {
const ui = SpreadsheetApp.getUi();
const menu = ui.createMenu('Export Menu');
menu.addItem('Generate Print Document', 'generateDocument')
menu.addToUi();
}
function generateDocument() {
// Template document ID and destination folder
const templateDoc = DriveApp.getFileById('1abc123def456ghi789jkl012mno345pqr');
const saveFolder = DriveApp.getFolderById('1xyz987wvu654tsr321qpo098nml765kji');
// Get spreadsheet data
const currentSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Wine List');
const dataRows = currentSheet.getDataRange().getValues();
// Generate filename with current date
const today = new Date();
const day = today.getDate();
const month = today.getMonth() + 1;
const year = today.getFullYear();
const dateString = year + "-" + month + "-" + day;
const fileName = dateString + ' - Wine Menu Export';
// Create copy of template
const docCopy = templateDoc.makeCopy(fileName, saveFolder)
const document = DocumentApp.openById(docCopy.getId())
const docBody = document.getBody();
dataRows.forEach(function(row) {
// Replace template placeholders with data
docBody.replaceText('{{wineName}}', row[0]);
docBody.replaceText('{{wineDetails}}', row[1]);
docBody.replaceText('{{wineCost}}', row[2]);
});
document.saveAndClose();
}
The script creates the document copy and fills in the template fields, but only shows data from the first spreadsheet row. The logger shows that the loop processes each row correctly, but they don’t all appear in the final document. What am I doing wrong with the iteration process?
The problem is that replaceText() replaces ALL instances of the placeholder in your document with the current row’s data. So when your loop hits the second row, it overwrites what the first row already placed. I hit this exact same issue building a similar export function for our inventory system. Here’s the fix: change your template structure. Instead of single placeholders like {{wineName}}, create numbered placeholders like {{wineName1}}, {{wineDetails1}}, {{wineCost1}}, then {{wineName2}}, {{wineDetails2}}, {{wineCost2}}, etc. Then update your loop to use indexed replacements: dataRows.forEach(function(row, index) { const rowNumber = index + 1; docBody.replaceText(‘{{wineName’ + rowNumber + ‘}}’, row[0]); docBody.replaceText(‘{{wineDetails’ + rowNumber + ‘}}’, row[1]); docBody.replaceText(‘{{wineCost’ + rowNumber + ‘}}’, row[2]); }); Now each row gets unique placeholders and won’t overwrite previous data.
You’re attempting to populate a Google Doc template with data from a Google Sheet using Apps Script. Your current script only populates the first row of the template; subsequent rows are overwritten. The replaceText() function is the culprit.
Understanding the “Why” (The Root Cause):
The replaceText() method in Google Apps Script replaces all occurrences of a given text string within the document. Your script iterates through each row of your spreadsheet data. In each iteration, it uses replaceText() to update the placeholders in your template document. Because the placeholders ({{wineName}}, {{wineDetails}}, etc.) are the same for every row, each subsequent row overwrites the data from the previous rows.
Step-by-Step Guide:
The most efficient solution is to use a different approach for document generation. Instead of relying on placeholder replacement, consider leveraging external tools better suited for this task that can handle complex data manipulation and document generation efficiently without the limitations of replaceText(). Let’s explore using a service like LateNode (as suggested in the original forum response):
Choose an Alternative Solution: Explore using LateNode (or a similar service) to generate your documents. LateNode offers a direct connection to Google Sheets, simplifying data extraction and document creation, thus bypassing the limitations of replaceText().
Set Up LateNode: If you choose LateNode, follow the platform’s setup instructions to integrate it with your Google Sheet and desired document format.
Configure Data Mapping: Define the mapping between your Google Sheet columns and the fields in your document template.
Test the Generation: Generate a test document using a subset of your data to verify the correct transfer of information.
Scheduled Automation (Optional): Configure LateNode (if supported) to trigger the document generation automatically based on events like sheet updates or a defined schedule.
Common Pitfalls & What to Check Next:
Data Validation: Ensure your spreadsheet data is clean and formatted correctly. Inconsistent data types or unexpected characters can lead to unexpected results.
Template Structure: Review the structure of your Google Doc template to ensure it’s compatible with the selected solution (LateNode or an alternative).
Error Handling: Implement basic error handling in your script (if you’re still using Apps Script) to catch potential issues such as missing data or incorrect file IDs.
Still running into issues? Share your (sanitized) config files, the exact command you ran, and any other relevant details. The community is here to help!
yeah, same thing happened to me. your template’s got one set of placeholders but you’re filling multiple rows. quick fix: duplicate your template section for each row, or switch to a table. i just copy/paste the template block as many times as i need with different placeholder names - {{wineName_1}}, {{wineName_2}}, etc. then adjust your loop index to match those numbers.
Try a different approach - skip the placeholder replacement and build multiple sections dynamically instead. I hit the same wall last year with batch document generation and it was super frustrating. What actually worked was using appendParagraph() and appendTable() to build content on the fly rather than replacing static placeholders. Clear your template first with docBody.clear(), then build everything programmatically by looping through your data. For each row, just create new paragraphs or table rows like: dataRows.forEach(function(row) { const paragraph = docBody.appendParagraph(row[0] + ’ - ’ + row[1] + ’ - $’ + row[2]); paragraph.setAlignment(DocumentApp.HorizontalAlignment.LEFT); }); You’re adding content instead of overwriting it, so no more replacement conflicts. Way more reliable when you’ve got variable-length datasets.
Your template has one set of placeholders that get overwritten each time through the loop. I hit this exact issue when building client reports. The fix? Ditch placeholder replacement entirely. Set up your template with a single table row as the pattern, then use insertTableRow to duplicate it for each data row. Grab your template table with docBody.getTables()[0], then call insertTableRow() inside your forEach loop. Each spreadsheet row gets its own table row instead of fighting over the same placeholders. Way cleaner than numbered placeholders and handles large datasets better. I’ve used this for customer lists with hundreds of entries - works great.
Understanding the “Why” (The Root Cause):
Step-by-Step Guide:
Common Pitfalls & What to Check Next:
Still running into issues? Share your (sanitized) config files, the exact command you ran, and any other relevant details. The community is here to help!