Apps Script自动化生成文档：如何仅处理最新表单提交条目

问题分析 #

当前脚本在表单提交时会遍历表格所有行，只要第17列（URL列）为空就生成文档，导致历史数据被重复处理。要实现仅处理新提交的条目，核心是利用表单提交的**事件对象e**精准定位新提交的行，而非遍历全表。

修改方案 #

1. 区分触发场景：脚本同时支持两种触发方式——表单自动提交（仅处理新行）和手动菜单补填（处理历史未生成的行）
2. 利用事件对象e：表单提交时，e会包含新提交行的索引和数据，直接提取该行处理即可
3. 保留手动补填逻辑：手动触发时仍保留原遍历逻辑，用于补填历史缺失的文档

修改后的完整代码 #

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  const menu = ui.createMenu('AutoFill');
  menu.addItem('Fill missing data', 'autoFillIntakeForm');
  menu.addToUi();
}

function autoFillIntakeForm(e) {
  const templateFile = DriveApp.getFileById("xxx");
  const templateResponseFolder = DriveApp.getFolderById("yyy");
  const spreadsheetId = "zzz";
  const ss = SpreadsheetApp.openById(spreadsheetId);
  const sheet = ss.getSheetByName("Form Responses 5");
  
  let targetRows = [];
  
  // 场景1：表单提交触发，仅处理新提交的行
  if (e && e.range) {
    const rowIndex = e.range.getRow();
    const rowData = sheet.getRange(rowIndex, 1, 1, sheet.getLastColumn()).getDisplayValues()[0];
    // 检查该行列17是否为空，避免重复处理
    if (!rowData[16]) { // 数组索引从0开始，第17列对应索引16
      targetRows.push({ index: rowIndex - 1, data: rowData });
    }
  } 
  // 场景2：手动菜单触发，处理所有未生成URL的行
  else {
    const rows = sheet.getDataRange().getDisplayValues();
    targetRows = rows.map((row, index) => ({ index, data: row }))
                     .filter(item => item.index !== 0 && !item.data[16]);
  }

  // 处理目标行
  targetRows.forEach(item => {
    const row = item.data;
    const rowNum = item.index + 1;
    
    const copy = templateFile.makeCopy(`${row[14]}, ${row[1]}, ${row[2]} form`, templateResponseFolder);
    const doc = DocumentApp.openById(copy.getId());
    const body = doc.getBody();

    body.replaceText('{{name}}', row[1]);
    body.replaceText('{{shortdescription}}', row[2]);
    body.replaceText('{{approxstoragetime}}', row[3]);
    body.replaceText('{{physicallocation}}', row[4]);
    body.replaceText('{{controlled}}', row[5]);
    body.replaceText('{{storeditemtype}}', row[6]);
    body.replaceText('{{contents}}', row[7]);
    body.replaceText('{{project}}', row[8]);
    body.replaceText('{{quantity}}', row[9]);
    body.replaceText('{{docprotocol}}', row[11]);
    body.replaceText('{{fixture#}}', row[12]);
    body.replaceText('{{serial#}}', row[13]);
    body.replaceText('{{storedate}}', row[14]);
    
    doc.saveAndClose();
    const url = doc.getUrl();
    sheet.getRange(rowNum, 17).setValue(url);
  });
}

关键配置步骤 #

• 给autoFillIntakeForm函数添加表单提交触发器：
  1. 打开脚本编辑器，点击左侧「触发器」图标
  2. 点击「添加触发器」，选择函数autoFillIntakeForm，事件源选「电子表格」，事件类型选「当表单提交时」
  3. 保存触发器，授权必要权限

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