Stop script from creating PDF duplicates

Question

I'm new to the coding thing but I've been able to write a script that will take info from a spreadsheet and turn it into a PDF. The trouble I'm having now is trying to find a way to get the code to stop before creating duplicates every time I run the code. I've tried a try catch block, return, throw and if else statements but I'm just not getting it. I have spent a lot of time searching to see if I could find a solution but can't seem to figure it out.

function createBulkPDFs(){
  
  const docFile = DriveApp.getFileById(//myfileID);
  const tempFolder = DriveApp.getFolderById(//myfolderID);
  const pdfFolder = DriveApp.getFolderById(//myfolderID);
  const RemittanceInfo = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Query");
   const data = RemittanceInfo.getRange(2,1,RemittanceInfo.getLastRow()-1,13).getDisplayValues();
  const checkedColumnIndex = 12;
  const addCheckbox = RemittanceInfo.getRange(2,13,RemittanceInfo.getLastRow()-1,1).insertCheckboxes();
  const check = "TRUE";
  let Print = [];
  
   data.forEach(function(row) {
     if (row[checkedColumnIndex] = check) {
         return;
         }
     else if (row[checkedColumnIndex] !== check){
       return addCheckbox
     }
    else  (row[checkedColumnIndex] !== check){
      return createPDF(row[0],row[1],row[2],row[3],row[4],row[9],row[5],row[6],row[7],row[8],row[11],row[0]+" "+row[9]+" Inv "+row[6],docFile,tempFolder,pdfFolder);
    }
    Print.push([check]);
  });
  RemittanceInfo.getRange(2,13,RemittanceInfo.getLastRow()-1,1).setValues(Print);  
}

function createPDF(Vendor,Street,City,Prov,PostalCode,DatePaid,Account,Invoice,AmountDue,Chq,Memo,pdfName,docFile,tempFolder,pdfFolder) {
 
  const tempFile = docFile.makeCopy(tempFolder);
  const tempDocFile = DocumentApp.openById(tempFile.getId());
  const body = tempDocFile.getBody();
  body.replaceText("{Vendor}", Vendor);
  body.replaceText("{Street}", Street);
  body.replaceText("{City}", City);
  body.replaceText("{Prov}", Prov);
  body.replaceText("{PostalCode}", PostalCode);
  body.replaceText("{DatePaid}", DatePaid);
  body.replaceText("{Account}", Account);
  body.replaceText("{Invoice}", Invoice);
  body.replaceText("{AmountDue}", AmountDue);
  body.replaceText("{Chq}", Chq);
  body.replaceText("{Memo}", Memo);
  tempDocFile.saveAndClose();
  const PDFContentBlob = tempFile.getAs(MimeType.PDF);
  pdfFolder.createFile(PDFContentBlob).setName(pdfName);
  tempFolder.removeFile(tempFile);
}

I'd really appreciate some help on this.

Answer 1

After sleeping on this for a few days, I was able to figure out my issue. The code below works for creating a single copy of a PDF and only generates one copy. Thank you for all your help Diego!

  
  const docFile = DriveApp.getFileById(" template ID ");
  const tempFolder = DriveApp.getFolderById(" temp folder ID ");
  const pdfFolder = DriveApp.getFolderById(" PDF fold ID ");
  const RemittanceInfo = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Query");
  const data = RemittanceInfo.getRange(2,1,RemittanceInfo.getLastRow()-1,13).getDisplayValues();
  const addCheckbox = RemittanceInfo.getRange(2,13,RemittanceInfo.getLastRow()-1,1).insertCheckboxes();
  const checkedColumnIndex = 13;
  const check = "TRUE";
  let Print = [];
  
   data.forEach(function(row) {
     if(row[checkedColumnIndex-1] !== check) {
       createPDF(row[0],row[1],row[2],row[3],row[4],row[9],row[5],row[6],row[7],row[8],row[11],row[0]+" "+row[9]+" Inv "+row[6],docFile,tempFolder,pdfFolder);
    }
    Print.push([check]);
  });
  RemittanceInfo.getRange(2,checkedColumnIndex,RemittanceInfo.getLastRow()-1,1).setValues(Print);
}

function createPDF(Vendor,Street,City,Prov,PostalCode,DatePaid,Account,Invoice,AmountDue,Chq,Memo,pdfName,docFile,tempFolder,pdfFolder) {
 
  const tempFile = docFile.makeCopy(tempFolder);
  const tempDocFile = DocumentApp.openById(tempFile.getId());
  const body = tempDocFile.getBody();
  body.replaceText("{Vendor}", Vendor);
  body.replaceText("{Street}", Street);
  body.replaceText("{City}", City);
  body.replaceText("{Prov}", Prov);
  body.replaceText("{PostalCode}", PostalCode);
  body.replaceText("{DatePaid}", DatePaid);
  body.replaceText("{Account}", Account);
  body.replaceText("{Invoice}", Invoice);
  body.replaceText("{AmountDue}", AmountDue);
  body.replaceText("{Chq}", Chq);
  body.replaceText("{Memo}", Memo);
  tempDocFile.saveAndClose();
  const PDFContentBlob = tempFile.getAs(MimeType.PDF);
  pdfFolder.createFile(PDFContentBlob).setName(pdfName);
  tempFolder.removeFile(tempFile);
}```

Answer 2

This is duplicating the PDF because you already ran the script, so there isn't actually any error and thus the try...catch won't do what you expect it to do.

Since you're checking the box in Column N to indicate that a PDF was created, simply look to see if that box is already checked when running the script. If not checked, then create the PDF.

function createBulkPDFs(){
  const docFile = DriveApp.getFileById(//myfileID);
  const tempFolder = DriveApp.getFolderById(//myfolderID);
  const pdfFolder = DriveApp.getFolderById(//myfolderID);
  const RemittanceInfo = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Query");
  const data = RemittanceInfo.getRange(2,1,RemittanceInfo.getLastRow()-1,14).getDisplayValues();
  const checkedColumnIndex = 13;
  const check = "TRUE";
  
  let Print = [];
  data.forEach(function(row) {
    if (row[checkedColumnIndex] !== check) {
      createPDF(row[0],row[1],row[2],row[3],row[4],row[9],row[5],row[6],row[7],row[8],row[11],row[0]+" Inv "+row[6]+" - "+row[9],docFile,tempFolder,pdfFolder);
    }
    Print.push([check]);
  });
  
  RemittanceInfo.getRange(2,checkedColumnIndex+1,RemittanceInfo.getLastRow()-1,1).setValues(Print);  
}

Notes:

• Defined checkedColumnIndex & check so they're easier to change later.
• Because you're using getDisplayValues(), the values returned by checkboxes are either the strings "TRUE" or "FALSE" (case-sensitive). Thankfully, the string "TRUE" will be correctly interpreted into a checkbox–no need to pass a boolean.
• Not using an arrow function simply because it messes up the formatting in the Apps Script editor.
• Pushing a value to Print even if skipping the PDF creation because otherwise the checks won't correspond with the correct row.
• When printing back to the sheet, remember to offset checkedColumnIndex since arrays are 0-indexed but ranges are 1-indexed.