我有一個為資料設定的 65 行的 Google 表格。在每一行中，一些單元格使用下拉串列，而其他單元格根據下拉選項的選擇使用 if 公式。最后一個單元格 (CQ)，通過在單元格中使用 if 陳述句，填充如下；
A) 如果行中所有其他計算單元格為空，則回傳“” B) 如果行中所有其他計算單元格不符合特定條件，則回傳“DATA INCOMPLETE” C) 如果所有計算單元格均符合特定條件，則回傳“COMPLETED” .
我的作業表目前有 10 行資料，但它試圖生成 65 個 PDF 檔案。在函式NMPRBulkPDF 中，它應該在到達空白行時停止，但這并沒有發生。我相信這是因為它認為一行不是空白的，因為：
A) 它認為下拉框有資料或 B) 它認為 if 公式是實際資料，即使回傳為空白 ("")
我不確定是哪個。
我希望能夠做的是使用行中的最后一個單元格 (CQ) 來確定該行是否應為其創建 PDF 檔案。如果條目是“”或“已完成”，我想跳過該行并繼續下一行。或者換句話說，只在最后一個單元格（CQ）中列印帶有“DATA INCOMPLETE”的那些
除了這個問題，我的這個作業很好，所以任何幫助將不勝感激。請耐心等待，因為這是我第一次接觸 Google Script，我完全是自學的。
這是我的腳本
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();
data.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);
});
}
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);
}
uj5u.com熱心網友回復：
我相信你的目標如下。
- 您想檢查“CQ”列。當“CQ”列的值為 時
DATA INCOMPLETE，您想在 的回圈中跳過data.forEach(row => {,,,})。
在這種情況下，如何進行以下修改？我認為在你的情況下，有幾種方法。
從：
const data = currentSheet.getRange(7, 1,currentSheet.getLastRow()-6,96).getDisplayValues();
到：
const data = currentSheet.getRange(7, 1,currentSheet.getLastRow()-6,96).getDisplayValues().filter(r => r[94] != "DATA INCOMPLETE");
或者
從：
data.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);
});
到
data.forEach(row => {
if (row[94] != "DATA INCOMPLETE") {
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);
}
});
- 通過此修改，在列“CQ”處
data有沒有行DATA INCOMPLETE。
參考：
- 篩選（）
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/326440.html