Google Apps Script如何仅获取最新行生成PDF避免重复

你原脚本的问题是每次运行都会读取所有非表头行遍历生成PDF，只需调整数据读取范围为仅获取最后一行即可解决重复生成的问题，修改后的完整代码如下：

function billPdf() {
  const pdfFolder = DriveApp.getFolderById("17Wyo_wHI1p_zNrEQWnay7pbb_3WS8Sivw");
  const tempFolder = DriveApp.getFolderById("1CiEPOh7X82M5jpupYtYqBdnlYIzlSRCjb");
  const tempDoc = DriveApp.getFileById("1IZr8ovAMOV1z2ADfXHjKvvHNOzMSW1T4WwC0XPd7_OeQ");
  const currentSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("TT");
  const lastRow = currentSheet.getLastRow();
  // 仅读取最后一行的73列数据
  const lastRowData = currentSheet.getRange(lastRow, 1, 1, 73).getDisplayValues()[0];
  let status;
  try {
    createPdf(
      lastRowData[0],lastRowData[1],lastRowData[2],lastRowData[3],lastRowData[4],lastRowData[5],
      lastRowData[13],lastRowData[14],lastRowData[15],lastRowData[16],lastRowData[17],
      lastRowData[23],lastRowData[24],lastRowData[25],lastRowData[26],lastRowData[27],
      lastRowData[33],lastRowData[34],lastRowData[35],lastRowData[36],lastRowData[37],
      lastRowData[43],lastRowData[44],lastRowData[45],lastRowData[46],lastRowData[47],
      lastRowData[49],lastRowData[69],lastRowData[11],
      pdfFolder,tempFolder,tempDoc,
      lastRowData[0]
    );
    status = "Done";
  } catch(err) {
    status = "Failed";
  }
  // 仅给最后一行的第71列写入处理状态
  currentSheet.getRange(lastRow, 71).setValue(status);
}

function createPdf(a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z,aa,ab,ac,pdfFolder,tempFolder,tempDoc,pdfname) {
  const newtemplateFile = tempDoc.makeCopy(tempFolder);
  const openDoc = DocumentApp.openById(newtemplateFile.getId());
  const body = openDoc.getBody();
  body.replaceText("{invoice}", a);
  body.replaceText("{date}", b);
  body.replaceText("{type}", c);
  body.replaceText("{contact}", d);
  body.replaceText("{name}", e);
  body.replaceText("{email}", f);
  body.replaceText("{p1}", g);
  body.replaceText("{p2}", h);
  body.replaceText("{p3}", i);
  body.replaceText("{p4}", j);
  body.replaceText("{p5}", k);
  body.replaceText("{q1}", l);
  body.replaceText("{q2}", m);
  body.replaceText("{q3}", n);
  body.replaceText("{q4}", o);
  body.replaceText("{q5}", p);
  body.replaceText("{mr1}", q);
  body.replaceText("{mr2}", r);
  body.replaceText("{mr3}", s);
  body.replaceText("{mr4}", t);
  body.replaceText("{mr5}", u);
  body.replaceText("{to1}", v);
  body.replaceText("{to2}", w);
  body.replaceText("{to3}", x);
  body.replaceText("{to4}", y);
  body.replaceText("{to5}", z);
  body.replaceText("{subt}", aa);
  body.replaceText("{add}", ab);
  body.replaceText("{foe}", ac);
  openDoc.saveAndClose();
  const bolbPDF = newtemplateFile.getAs(MimeType.PDF);
  const pdfFile = pdfFolder.createFile(bolbPDF).setName(pdfname);
  tempFolder.removeFile(newtemplateFile);
  return pdfFile;
}

核心修改点 #

• 调整数据读取逻辑：删除了原脚本读取全部非表头行的逻辑，改为仅获取当前表格最后一行的数据，无需遍历历史行
• 简化处理流程：去掉了遍历所有行的forEach循环，直接处理单条最后行数据
• 修正语法错误：修复了原代码中errors.push["Done"]的写法错误，push是方法需使用括号调用
• 调整状态写入规则：原脚本会批量覆盖所有行的处理状态，改为仅写入最后一行对应列的状态，避免修改历史行数据

注意事项 #

如果你的脚本是绑定谷歌表单提交触发，可直接通过事件对象获取刚提交的行数据，能避免多人同时提交时取错最后一行的问题，当前方案适配单用户、低提交频率的使用场景。

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