Google Sheets批量生成PDF时基于指定单元格值筛选生成行的脚本优化需求

Hey Kevin, I totally get why this is frustrating—those dropdown lists and empty-returning IF formulas are tricking the script into thinking rows have data when they don't. Let's adjust the script to strictly follow your requirement: only generate PDFs for rows where the CQ cell is "DATA INCOMPLETE".

The Root Issue #

Your original script uses currentSheet.getLastRow() which counts rows with dropdown lists or formulas (even those returning "") as non-empty. That's why it's processing all 65 rows instead of just the 10 with valid data.

The Solution #

We'll add a filter step to your createNMPRBulkPDFs() function to only keep rows where the CQ column (last column in your data range) equals "DATA INCOMPLETE". Here's how to do it:

1. First, confirm the index of the CQ column in your data array: since you're pulling 96 columns (A to CQ), the array index for CQ is 95 (JavaScript arrays start at 0).
2. Add a filter() call right before the forEach() loop to narrow down the rows we need to process.

Modified Code #

Here's the updated createNMPRBulkPDFs() function with the fix (I've highlighted the changes with comments):

function createNMPRBulkPDFs(){
  const docFile = DriveApp.getFileById("12CFZKbgV5wCW8CmnePWPz1njCo8UNtba_7LmP7cQG_0");
  const tempFolder = DriveApp.getFolderById("1AGFO-RKnGX9srrG04uK7_nVI2DjC3SPc");
  const pdfFolder = DriveApp.getFolderById("1ApolEORfrDS-QukjpSkK4TwDkyIQd9k8");
  const currentSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("PROGRESS");
  const data = currentSheet.getRange(7, 1,currentSheet.getLastRow()-6,96).getDisplayValues();

  // -------------------- NEW FILTER STEP --------------------
  // Keep only rows where CQ column (index 95) is "DATA INCOMPLETE"
  const filteredData = data.filter(row => row[95] === "DATA INCOMPLETE");
  // -------------------- END NEW FILTER STEP --------------------

  // Use filteredData instead of original data for processing
  filteredData.forEach(row => {
    createNMPR(row[1],row[2],row[3],row[4],row[38],row[39],row[40],row[41],row[43],row[44],row[42],row[46],row[47],row[45],row[49],row[50],row[48],row[52],row[53],row[51],row[55],row[56],row[54],row[58],row[59],row[57],row[60],row[61],row[62],row[63],row[64],row[65],row[66],row[67],row[68],row[69],row[70],row[71],row[72],row[73],row[74],row[75],row[76],row[77],row[78],row[79],row[80],row[81],row[82],row[83],row[84],row[85],row[86],row[87],row[88],row[89],row[90],row[91],row[92],row[93],row[1] + " " + row[2],docFile,tempFolder,pdfFolder);
  });
}

// Your existing createNMPR function stays exactly the same
function createNMPR(firstname,lastname,gender,age,phone,address,spousename,spouseage,childone,childoneage,childonesex,childtwo,childtwoage,childtwosex,childthree,childthreeage,childthreesex,childfour,childfourage,childfoursex,childfive,childfiveage,childfivesex,childsix,childsixage,childsixsex,remarks,wcmassigned,wwcmassigned,wma,bds,dbsf,mbsa,mcpp,mwb,fhvone,dbp,doc,cfmp,bi,apo,tpr,fhvtwo,tpbone,fhe,eqv,rsv,ymywv,pv,srp,ce,tbtwo,pbi,rpb,mp,tpc,bti,sti,te,ts,pdfName,docFile,tempFolder,pdfFolder) {
  const tempFile = docFile.makeCopy(tempFolder);
  const tempDocFile = DocumentApp.openById(tempFile.getId());
  const body = tempDocFile.getBody();
  body.replaceText('{{First Name}}', firstname);
  body.replaceText('{{Last Name}}', lastname);
  body.replaceText('{{Gender}}', gender);
  body.replaceText('{{Age}}', age);
  body.replaceText('{{Phone Number REPORT}}', phone);
  body.replaceText('{{Address REPORT}}', address);
  body.replaceText('{{Spouse Name REPORT}}', spousename);
  body.replaceText('{{Spouse Age REPORT}}', spouseage);
  body.replaceText('{{Child 1 Age REPORT}}', childoneage);
  body.replaceText('{{Child 1 Sex REPORT}}', childonesex);
  body.replaceText('{{Child 1 REPORT}}', childone);
  body.replaceText('{{Child 2 Age REPORT}}', childtwoage);
  body.replaceText('{{Child 2 Sex REPORT}}', childtwosex);
  body.replaceText('{{Child 2 REPORT}}', childtwo);
  body.replaceText('{{Child 3 Age REPORT}}', childthreeage);
  body.replaceText('{{Child 3 Sex REPORT}}', childthreesex);
  body.replaceText('{{Child 3 REPORT}}', childthree);
  body.replaceText('{{Child 4 Age REPORT}}', childfourage);
  body.replaceText('{{Child 4 Sex REPORT}}', childfoursex);
  body.replaceText('{{Child 4 REPORT}}', childfour);
  body.replaceText('{{Child 5 Age REPORT}}', childfiveage);
  body.replaceText('{{Child 5 Sex REPORT}}', childfivesex);
  body.replaceText('{{Child 5 REPORT}}', childfive);
  body.replaceText('{{Child 6 Age REPORT}}', childsixage);
  body.replaceText('{{Child 6 Sex REPORT}}', childsixsex);
  body.replaceText('{{Child 6 REPORT}}', childsix);
  body.replaceText('{{Remarks REPORT}}', remarks);
  body.replaceText('{{Ward Council Member Assigned REPORT}}', wcmassigned);
  body.replaceText('{{Which Ward Council Member Assigned REPORT}}', wwcmassigned);
  body.replaceText('{{Ward Missionary Assigned REPORT}}', wma);
  body.replaceText('{{Baptism Date Set REPORT}}', bds);
  body.replaceText('{{Date Baptism Scheduled For REPORT}}', dbsf);
  body.replaceText('{{Ministering Brother/Sister Assigned REPORT}}', mbsa);
  body.replaceText('{{My Covenant Path Provided REPORT}}', mcpp);
  body.replaceText('{{Meeting with Bishopric REPORT}}', mwb);
  body.replaceText('{{Family History Visit 1 REPORT}}', fhvone);
  body.replaceText('{{Date Baptism Performed REPORT}}', dbp);
  body.replaceText('{{Date of Confirmation REPORT}}', doc);
  body.replaceText('{{Come Follow Me Provided REPORT}}', cfmp);
  body.replaceText('{{Bishop Interview REPORT}}', bi);
  body.replaceText('{{Aaronic Priesthood Ordination REPORT}}', apo);
  body.replaceText('{{Temple Partial Recommend REPORT}}', tpr);
  body.replaceText('{{Family History Visit 2 REPORT}}', fhvtwo);
  body.replaceText('{{Temple Proxy Baptisms 1 REPORT}}', tpbone);
  body.replaceText('{{Family Home Evening REPORT}}', fhe);
  body.replaceText('{{Elder Quorum Visit REPORT}}', eqv);
  body.replaceText('{{Relief Society Visit REPORT}}', rsv);
  body.replaceText('{{YM/YW Visit REPORT}}', ymywv);
  body.replaceText('{{Primary Visit REPORT}}', pv);
  body.replaceText('{{Self-Reliance Program REPORT}}', srp);
  body.replaceText('{{Calling Extended REPORT}}', ce);
  body.replaceText('{{Temple Baptisms 2nd time REPORT}}', tbtwo);
  body.replaceText('{{Patriarchal Blessing Interview REPORT}}', pbi);
  body.replaceText('{{Received Patriarchal Blessing REPORT}}', rpb);
  body.replaceText('{{Melchizedek Priesthood REPORT}}', mp);
  body.replaceText('{{Temple Preparation Class REPORT}}', tpc);
  body.replaceText('{{Bishop Temple Interview REPORT}}', bti);
  body.replaceText('{{Stake Temple Interview REPORT}}', sti);
  body.replaceText('{{Temple Endowment REPORT}}', te);
  body.replaceText('{{Temple Sealing REPORT}}', ts);
  tempDocFile.saveAndClose();
  const pdfContentBlob = tempFile.getAs(MimeType.PDF);
  pdfFolder.createFile(pdfContentBlob).setName(pdfName);
  tempFolder.removeFile(tempFile);
}

Quick Checks to Ensure It Works #

• Double-check that row[95] is indeed the CQ column: if you ever add/remove columns, you'll need to adjust this index.
• If you want to test before running the full batch, you can add a console.log(filteredData.length) right after the filter step to see how many rows will be processed (should match the number of "DATA INCOMPLETE" rows).

That's it! The rest of your script works fine, so we just added that one filter step to target only the rows you need.

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