Google Apps Script实现表单提交填充Doc仅获取最新行问题求助

解决方案 #

你绑定的是表单提交触发的事件，触发时事件对象e已经包含当前提交行的位置信息，无需遍历全表数据，直接定位当前提交行提取计算字段即可，修改后的完整代码如下：

function autoFillGoogleDocFromForm(e){
  // 取表单提交的原生字段
  var PersonName = e.values[1];
  var Month = e.values [2];
  var ShowName = e.values [3];
  var Sale = e.values [4];
  var BuyType = e.values [5];
  var Budget = e.values [6];
  var Products = e.values [7];
  var TermMonths = e.values [8];
  var Title = e.values [9];
  var Assets = e.values [10];
  var NumberofShows = e.values [11];
  var NumberofSales = e.values [12];
  var ListSize = e.values [13];
  var Agency = e.values [14];
  var DataCadence = e.values [15];
  var Other = e.values [16];
  var AdditionalNotes = e.values [17];

  // 以下是替换原来遍历部分的代码
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  // 拿到当前提交行的行号
  var currentRow = e.range.getRow();
  // 直接取当前行第19列（对应你之前的row[18]，数组从0开始计数）到第29列的计算字段
  var calculateFields = sheet.getRange(currentRow, 19, 1, 10).getDisplayValues()[0];
  var ContractID = calculateFields[0];
  var Term = calculateFields[1];
  var StartDate = calculateFields[2];
  var EndDate = calculateFields[3];
  var CustomerCost = calculateFields[4];
  var CostShow = calculateFields[5];
  var Cost = calculateFields[6];
  var TotalEngagements = calculateFields[7];
  var CostSplit = calculateFields[8];
  var TotalEmails = calculateFields[9];
  var CostData = calculateFields[10];
  // 遍历部分替换结束

  var templateFile = DriveApp.getFileById("9fu1jGgpaQ");
  var templateResponseFolder = DriveApp.getFolderById("15h3GnG");

  // 注意：你原代码里的BrName、Manufacturer等变量没有定义，需要替换成你实际定义的变量名
  var copy = templateFile.makeCopy('Name' + PersonName, templateResponseFolder);
  var doc = DocumentApp.openById(copy.getId());
  var body = doc.getBody();

  // 这里的占位符替换变量也要对应改成你实际定义的变量名，比如你前面定义的是StartDate就直接用StartDate
  body.replaceText("{{Name of Manufacturer}}", PersonName);
  body.replaceText("{{Name of Brand}}", ShowName);
  body.replaceText("{{Name of Products}}", Products);
  body.replaceText("{{Start Date}}", StartDate);
  body.replaceText("{{End Date}}", EndDate);
  body.replaceText("{{Title of Sponsorship}}", Title);
  body.replaceText("{{Name Generic}}", Assets);
  body.replaceText("{{Number of Videos}}", NumberofShows);
  body.replaceText("{{Cost Video Int}}", CustomerCost);
  body.replaceText("{{Total Video Eng}}", TotalEngagements);
  body.replaceText("{{Video Eng}}", CostSplit);
  body.replaceText("{{Video Eng 2}}", CostSplit);
  body.replaceText("{{Target List Size}}", ListSize);
  body.replaceText("{{Cost Video Eng}}", Cost);
  body.replaceText("{{Total Email Eng}}", TotalEmails);
  body.replaceText("{{Cost Email Eng}}", CostShow);
  body.replaceText("{{Target Size}}", ListSize);
  body.replaceText("{{Data Cadence}}", DataCadence);
  body.replaceText("{{Cost Data}}", CostData);

  doc.saveAndClose();
}

关键修改说明 #

• 删掉了原来全表取数+遍历所有行的逻辑，避免重复为历史提交记录生成文档
• 通过e.range.getRow()直接获取当前触发表单提交的行号，精准提取该行的计算字段，不需要遍历全表
• 注意原代码存在变量名不匹配的问题，你需要根据自己实际的字段对应关系，调整replaceText部分的变量，确保占位符替换的内容正确

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