Skip row if a specific cell is display value blank when running BulkPDFs Google Sheets-CodePudding

I have a Google sheets with 65 lines set up for data. In every row some of the cells use dropdown list and others use an if formulas based on the selections of the dropdown choices. The final cell (CQ), through the use of an if statement in the cell, populates as follows;

A) Returns "" if all other calculated cells in the row are blank B) Returns "DATA INCOMPLETE" if all the other calculated cells in the row do not meet specific criteria C) Returns "COMPLETED" if all calculated cells meet the specific criteria.

My sheet currently has data in 10 lines however it is trying to generate 65 PDF files. In functionNMPRBulkPDF it is suppose to stop when it reaches a blank row however that is not happening. I believe this is occurring because it thinks a row is not blank because either:

A) it sees the dropdown box as having data or B) it thinks the if formula is actual data even if the return is blank ("")

I am not sure which.

What I would like to be able to do is to use last cell in the row (CQ) to determine if the row should have a PDF file created for it. If the entry is "" or "COMPLETED" I would like to skip the row and go on to the next one. Or in other words only print the ones with "DATA INCOMPLETE" in that last cell (CQ)

I have this working well other than this issue so any help will be highly appreciated. Please have patience with me as this is my first foray into Google Script and I am totally self taught.

Here is my 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);

}

CodePudding user response：

I believe your goal is as follows.

You want to check the column "CQ". When the value of column "CQ" is DATA INCOMPLETE, you want to skip in the loop of data.forEach(row => {,,,}).

In this case, how about the following modification? I think that in your situation, there are several methods.

From:

const data = currentSheet.getRange(7, 1,currentSheet.getLastRow()-6,96).getDisplayValues();

To:

const data = currentSheet.getRange(7, 1,currentSheet.getLastRow()-6,96).getDisplayValues().filter(r => r[94] != "DATA INCOMPLETE");

From:

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);
});

To

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);
  }
});

By this modification, data has the rows without DATA INCOMPLETE at the column "CQ".

Reference:

filter()