Google Apps Script将谷歌表格转文档及PDF仅处理最后一行问题咨询

问题原因 #

你的代码结构有误：

• for循环内部仅做了行列数据的变量赋值操作，没有包含任何生成文档、替换模板、导出PDF的业务逻辑
• 循环遍历所有行时会不断覆盖client、agent等变量的值，循环结束后变量中仅保留最后一行的数据
• 生成文档、导出PDF的所有逻辑都写在循环外部，仅会执行一次，因此最终只能得到最后一行对应的处理结果

修复方案 #

把单条数据的完整处理逻辑（从复制模板到生成PDF的所有代码）全部移入for循环内部即可，同时可将重复调用的模板、文件夹对象提取到循环外，减少不必要的API请求：

function createDoc() {
  console.log("first");

  // 提前拉取表格数据
  var headers = Sheets.Spreadsheets.Values.get('1846CxmPdoc2VBW6GxaybCPW1_u2swO1jooIBiF2Yl90', 'A2:AA2');
  var variables = Sheets.Spreadsheets.Values.get('1846CxmPdoc2VBW6GxaybCPW1_u2swO1jooIBiF2Yl90', 'A3:AA14');
  // 提前获取公共资源，避免循环内重复调用API
  var templateId = '1ONhT3n4Pr49BL6xEM_ykO9UVi8xZriA2fVAZjoFi2qI';
  var templateFile = DriveApp.getFileById(templateId);
  var docFolder = DriveApp.getFolderById('1YVhLzwZ9CI5-iTR1SKHF5ykNVqZQvQY9');
  var pdfFolder = DriveApp.getFolderById("1_idXGdZo0l_U1IxuaLDUqrk0HjdfZvsg");

  // 循环遍历所有行数据
  for(var i = 0; i < variables.values.length; i++) {
    // 提取当前行的所有字段
    var client = variables.values[i][0];
    var agent = variables.values[i][1];
    var aaddress = variables.values[i][2];
    var acity = variables.values[i][3];
    var caddress = variables.values[i][4];
    var ccity = variables.values[i][5];
    var suopen = variables.values[i][6];
    var suclose = variables.values[i][7];
    var moopen = variables.values[i][8];
    var moclose = variables.values[i][9];
    var tuopen = variables.values[i][10];
    var tuclose = variables.values[i][11];
    var weopen = variables.values[i][12];
    var weclose = variables.values[i][13];
    var thopen = variables.values[i][14];
    var thclose = variables.values[i][15];
    var fropen = variables.values[i][16];
    var frclose = variables.values[i][17];
    var saopen = variables.values[i][18];
    var saclose = variables.values[i][19];
    var price = variables.values[i][20];
    var appayment = variables.values[i][21];
    var mpayment = variables.values[i][22];
    var junepayment = variables.values[i][23];
    var julypayment = variables.values[i][24];
    var aupayment = variables.values[i][25];
    var sepayment = variables.values[i][26];

    // 复制模板并重命名
    const documentId = templateFile.makeCopy().getId();
    const newDoc = DriveApp.getFileById(documentId);
    newDoc.setName('2022 ' + client + ' Pool Management Proposal');
    newDoc.moveTo(docFolder); 

    // 替换模板占位符
    const openDoc = DocumentApp.openById(documentId);
    const body = openDoc.getBody();
    body.replaceText('##Agent Name##', agent);
    body.replaceText('##Agent Address##', aaddress);
    body.replaceText('##Agent City/Zip##', acity);
    body.replaceText('##Client Name##', client)
    body.replaceText('##Client Address##', caddress);
    body.replaceText('##Client City/Zip##', ccity);
    body.replaceText('##Contract Price##', price);
    body.replaceText('##April Payment##', appayment);
    body.replaceText('##May Payment##', mpayment);
    body.replaceText('##June Payment##', junepayment);
    body.replaceText('##July Payment##', julypayment);
    body.replaceText('##August Payment##', aupayment);
    body.replaceText('##September Payment##', sepayment);
    body.replaceText('##Sunday Open##', suopen);
    body.replaceText('##Sunday Close##', suclose);
    body.replaceText('##Monday Open##', moopen);
    body.replaceText('##Monday Close##', moclose);
    body.replaceText('##Tuesday Open##', tuopen);
    body.replaceText('##Tuesday Close##', tuclose);
    body.replaceText('##Wednesday Open##', weopen);
    body.replaceText('##Wednesday Close##', weclose);
    body.replaceText('##Thursday Open##', thopen);
    body.replaceText('##Thursday Close##', thclose);
    body.replaceText('##Friday Open##', fropen);
    body.replaceText('##Friday Close##', frclose);
    body.replaceText('##Saturday Open##', saopen);
    body.replaceText('##Saturday Close##', saclose);
    openDoc.saveAndClose();

    // 导出PDF
    const theBlob = newDoc.getBlob().getAs('application/pdf');
    const newPDFFile = docFolder.createFile(theBlob);
    newPDFFile.moveTo(pdfFolder);
    const fileName = newDoc.getName().replace(".", ""); 
    newPDFFile.setName(fileName + ".pdf");
  }
}

优化建议 #

• 可增加空行判断逻辑，避免表格空行生成无效文档
• 处理数据量较大时，可在循环末尾增加Utilities.sleep(1000)，避免触发Google服务调用配额限制
• 表格ID、模板ID、文件夹ID建议提取为统一配置项，后续修改更方便

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