如何将Google Sheet指定工作表表格插入到Doc模板替换标签位置

原代码核心问题 #

• inserttable1函数仅定义未调用，表格插入逻辑从未执行
• 调用PDF转换时，文档已被saveAndClose()关闭，无法再通过doc.getAs()获取Blob
• forEach循环的index变量与表格插入函数内的index重名，导致索引混乱
• 未处理{{Table 1}}占位符找不到的情况，可能触发空指针错误

修正后的完整代码 #

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  const menu = ui.createMenu('AutoFill Docs');
  menu.addItem('Create New Docs', 'createNewGoogleDocs');
  menu.addToUi();
}

function createNewGoogleDocs() {
  const googleDocTemplate = DriveApp.getFileById('1ljyhJ11v90WQJTIV9nrjC0uq80HYK_U1aY7-dgMtNUk');
  const destinationFolder = DriveApp.getFolderById('1dsrpxUihbYQQkdKx3uD5v0IDvv_CSUOS');
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('HĐMBHH');
  const rows = sheet.getDataRange().getValues();

  rows.forEach(function(row, rowIndex){
    if (rowIndex === 0) return;
    if (row[16]) return;

    const copy = googleDocTemplate.makeCopy(`LG.001 ${row[15]}`, destinationFolder);
    const doc = DocumentApp.openById(copy.getId());
    const body = doc.getBody();

    // 替换文本占位符
    body.replaceText("{{b Company name}}", row[2]);
    body.replaceText("{{Party B Address}}", row[3]);
    body.replaceText("{{Party B Tel}}", row[4]);
    body.replaceText("{{b Rep}}", row[5]);
    body.replaceText("{{B Position}}", row[6]);
    body.replaceText("{{B Tax Code}}", row[7]);
    body.replaceText("{{dot1}}", row[8]);
    body.replaceText("{{chu1}}", row[9]);
    body.replaceText("{{word1}}", row[10]);
    body.replaceText("{{dot2}}", row[11]);
    body.replaceText("{{chu2}}", row[12]);
    body.replaceText("{{word2}}", row[13]);
    body.replaceText("{{bh}}", row[14]);
    body.replaceText("{{Contract ID}}", row[0]);

    // 执行表格插入逻辑
    insertTableIntoDoc(body);

    // 调整顺序：先获取PDF Blob再关闭文档
    const pdfBlob = doc.getAs('application/pdf');
    doc.saveAndClose();
    const pdf = destinationFolder.createFile(pdfBlob);
    const url = pdf.getUrl();

    // 更新工作表中的PDF链接
    sheet.getRange(rowIndex + 1, 17).setValue(url);
  });
}

// 提取表格插入逻辑为独立函数
function insertTableIntoDoc(body) {
  const sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("LG001 - Table 1");
  const numRows = sheet1.getLastRow();
  const numColumns = sheet1.getLastColumn();
  if (numRows === 0 || numColumns === 0) return; // 空表格直接返回

  const data = sheet1.getRange(1, 1, numRows, numColumns).getValues();
  const placeHolder = body.findText("{{Table 1}}");
  
  if (!placeHolder) {
    console.log("未找到{{Table 1}}占位符");
    return;
  }

  const paragraph = placeHolder.getElement().getParent();
  const tableIndex = body.getChildIndex(paragraph);
  body.insertTable(tableIndex + 1, data);
  body.removeChild(paragraph);
}

关键修改说明 #

1. 触发表格插入逻辑：将原inserttable1重命名为insertTableIntoDoc并主动调用，确保表格插入逻辑执行
2. 修复PDF生成错误：先获取文档的PDF Blob，再关闭文档，避免关闭后无法操作的问题
3. 解决变量冲突：将循环索引改为rowIndex，避免与表格插入逻辑中的索引变量混淆
4. 增加容错处理：检查表格是否为空、占位符是否存在，避免运行时异常
5. 优化代码结构：将表格插入逻辑提取为独立函数，提升代码可读性和可维护性

内容的提问来源于stack exchange，提问作者Trng L Thanh