基于Google Sheets数据动态填充Google Doc多页每份4个表单的实现问题

核心思路 #

• 提前存储原始模板的完整内容结构，每次新增页面时直接复用该结构生成新的占位符组
• 每填充完4条数据后，判断是否还有剩余未处理数据，如有则插入分页符并追加新的模板内容
• 最后处理不足4条数据的剩余占位符，避免原始占位符文本残留

修改后的完整代码 #

function onOpen() {
    const ui = SpreadsheetApp.getUi();
    const menu = ui.createMenu('Auto Fill');
    menu.addItem('Create Delivery Slips', 'createNewDeliverySlips');
    menu.addToUi();
}

function createNewDeliverySlips() {
    const googleDocTemplate = DriveApp.getFileById('<替换为模板文件ID>');
    const googleDocFolder = DriveApp.getFolderById('<替换为目标文件夹ID>');
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
    const rows = sheet.getDataRange().getValues();

    // 新增：获取原始模板的完整内容结构，用于后续新页面复用
    const originalTemplateDoc = DocumentApp.openById(googleDocTemplate.getId());
    const templateBody = originalTemplateDoc.getBody();
    const templateContent = templateBody.copy();
    originalTemplateDoc.saveAndClose();

    const copy = googleDocTemplate.makeCopy(`Delivery Slips`, googleDocFolder);
    const doc = DocumentApp.openById( copy.getId() );
    const body = doc.getBody();

    rows.forEach( function( row, index ) {
        if (index === 0) return;

        if (index % 4 === 1){
            body.replaceText('{{First Name1}}', row[1]);
            body.replaceText('{{Last Name1}}', row[2]);
            body.replaceText('{{Address1}}', row[3]);
            body.replaceText('{{City1}}', row[4]);
            body.replaceText('{{State1}}', row[5]);
            body.replaceText('{{Zip1}}', row[6]);
        }

        if (index % 4 === 2){
            body.replaceText('{{First Name2}}', row[1]);
            body.replaceText('{{Last Name2}}', row[2]);
            body.replaceText('{{Address2}}', row[3]);
            body.replaceText('{{City2}}', row[4]);
            body.replaceText('{{State2}}', row[5]);
            body.replaceText('{{Zip2}}', row[6]);
        }

        if (index % 4 === 3){
            body.replaceText('{{First Name3}}', row[1]);
            body.replaceText('{{Last Name3}}', row[2]);
            body.replaceText('{{Address3}}', row[3]);
            body.replaceText('{{City3}}', row[4]);
            body.replaceText('{{State3}}', row[5]);
            body.replaceText('{{Zip3}}', row[6]);
        }

        if (index % 4 === 0){
            body.replaceText('{{First Name4}}', row[1]);
            body.replaceText('{{Last Name4}}', row[2]);
            body.replaceText('{{Address4}}', row[3]);
            body.replaceText('{{City4}}', row[4]);
            body.replaceText('{{State4}}', row[5]);
            body.replaceText('{{Zip4}}', row[6]);

            // 新增：判断还有未处理数据则插入新页面和新模板
            if (index < rows.length - 1) {
                // 插入分页符
                body.appendPageBreak();
                // 追加新的模板内容，生成新的占位符组
                for (let i = 0; i < templateContent.getNumChildren(); i++) {
                    const child = templateContent.getChild(i);
                    const childType = child.getType();
                    switch(childType) {
                        case DocumentApp.ElementType.PARAGRAPH:
                            body.appendParagraph(child.asParagraph().copy());
                            break;
                        case DocumentApp.ElementType.TABLE:
                            body.appendTable(child.asTable().copy());
                            break;
                        case DocumentApp.ElementType.LIST_ITEM:
                            body.appendListItem(child.asListItem().copy());
                            break;
                        // 模板有其他元素类型可继续补充case
                    }
                }
            }
        }    
    });

    // 新增：处理最后一页不足4条数据的占位符清空
    const remainingCount = (rows.length - 1) % 4;
    if (remainingCount !== 0) {
        for (let i = remainingCount + 1; i <= 4; i++) {
            body.replaceText(`{{First Name${i}}}`, '');
            body.replaceText(`{{Last Name${i}}}`, '');
            body.replaceText(`{{Address${i}}}`, '');
            body.replaceText(`{{City${i}}}`, '');
            body.replaceText(`{{State${i}}}`, '');
            body.replaceText(`{{Zip${i}}}`, '');
        }
    }

    doc.saveAndClose();
}

注意事项 #

• 如果模板包含图片、绘图等特殊元素，可对应补充switch分支的元素类型处理逻辑
• 原始模板不要添加额外分页符，避免生成的文档分页混乱
• 请提前测试模板内容追加逻辑，确保和原模板样式完全一致

内容的提问来源于stack exchange，提问作者TPas