How to choose between date in Google Sheet table and given date with Google Apps Script

Question

I am trying to generate PDFs for each row of a particular table... If you look at createPDF(), I'm having some difficulty handling the startingDate argument:

• I want to compare the date in my table to the first day of the current month;
• If the date in my table is more recent, then I'll use it as my startingDate argument, otherwise I want to use the first day of the current month.

I'm guessing maybe I should declare a let startingDate[] and write into it, but I can't seem to make it work.

Would you guys be so kind as to help me? THANK YOU! ^_^

function createBulkPDFs(){
  const docFile = DriveApp.getFileById("1CEJjt50pwNQpK_9tefzpNg5fzie8BeswZV3B4cecpPs");
  const tempFolder = DriveApp.getFolderById("1keBnLhrMWjGokTOFdi2o25tjkTKjrKbO");
  const pdfFolder = DriveApp.getFolderById("18N2DyrBRNggXle9Pfdi3wbn0NTcdgt93");
  const currentSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Récupération du lead")

  const data = currentSheet.getRange(2,1,currentSheet.getLastRow()-1,33).getDisplayValues();

  const date = new Date();
  const firstDayOfMonth = new Date(date.getFullYear(), date.getMonth(), 1).toLocaleString("fr-FR").slice(0,10);
  
  let errors = [];
  data.forEach(row => {
    try{
      createPDF(row[2],row[3],row[10],row[11],
      /* find startingDate 
      IF row[4] is newer than firstDayOfMonth select row[4] as startingDate
      ELSE select firstDayOfMonth as startingDate
      */
      row[2] + " " + row[3],docFile,tempFolder,pdfFolder);
      errors.push([""]);
    } catch(err){
      errors.push(["Failed"]);
    }   
  });

  currentSheet.getRange(2,32,currentSheet.getLastRow()-1,1).setValues(errors);

}

function createPDF(firstName,lasttName,formation,client,startingDate,pdfName,docFile,tempFolder,pdfFolder) {
  
  const tempFile = docFile.makeCopy(tempFolder);
  const tempDocFile = DocumentApp.openById(tempFile.getId());
  const body = tempDocFile.getBody();
  body.replaceText("{PRENOM}", firstName);
  body.replaceText("{NOM}", lasttName);
  body.replaceText("{FORMATION}", formation);
  body.replaceText("{CLIENT}", client);
  body.replaceText("{DEBUT}", debut);
  tempDocFile.saveAndClose();
  const pdfContentBlob = tempFile.getAs(MimeType.PDF);
  pdfFolder.createFile(pdfContentBlob).setName(pdfName);
  tempFolder.removeFile(tempFile);

}

Here's a look at my table:

MainID	Task ID	LastName	FirstName	Start Date	Due Date	Comment	Progress	Statut	URL Session	Formation	Client
b093eed1-d55c-41f1-9999-644b6af411bb	120239E15	DOE	John	2022-05-02 0:00:00	2023-01-07 12:00:00	Lorem ipsum	Lorem ipsum	Ok	Lorem ipsum	BBB
b093eed1-d55c-41f1-9999-644b6af511cc	120239Z00	SMITH	Anna	2022-06-02 0:00:00	2023-08-09 12:00:00	Lorem ipsum	Lorem ipsum	Pending	Lorem ipsum	CCC

Answer 1

Try this

I modified your script by removing the try-catch statements and then replaced it with a for loop so that each row in your spreadsheet would be checked one by one. In addition, I have listed the important variables inside the for loop to lessen confusion.

As for the comparison of dates, I used startingDate >= firstDayOfMonth since it returns true when startingDate is more recent than firstDayOfMonth.

On the other hand, your createPDF() function works just fine. I just modified the "debut" part so that it links the recent date.

function createBulkPDFs(){
  const docFile = DriveApp.getFileById("doc file ID");
  const tempFolder = DriveApp.getFolderById("temp folder ID");
  const pdfFolder = DriveApp.getFolderById("pdf folder ID");
  const currentSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Récupération du lead")

  const date = new Date();
  const firstDayOfMonth = new Date(date.getFullYear(), date.getMonth(), 1).toLocaleString("fr-FR").slice(0,10);
  for(i=2; i<=currentSheet.getLastRow(); i++) {
    var firstName = currentSheet.getRange(i,4,1,1).getValues();
    var lasttName = currentSheet.getRange(i,3,1,1).getValues();
    var formation = currentSheet.getRange(i,11,1,1).getValues();
    var client = currentSheet.getRange(i,12,1,1).getValues();
    var startingDate = currentSheet.getRange(i,5,1,1).getValues();
    var debut = (startingDate >= firstDayOfMonth) ? startingDate : firstDayOfMonth;
    createPDF(firstName, lasttName, formation, client, debut, firstName + " " + lasttName, docFile,tempFolder,pdfFolder);
  }
}

function createPDF(firstName,lasttName,formation,client,startingDate,pdfName,docFile,tempFolder,pdfFolder) {
  const tempFile = docFile.makeCopy(tempFolder);
  const tempDocFile = DocumentApp.openById(tempFile.getId());
  const body = tempDocFile.getBody();
  body.replaceText("{PRENOM}", firstName);
  body.replaceText("{NOM}", lasttName);
  body.replaceText("{FORMATION}", formation);
  body.replaceText("{CLIENT}", client);
  body.replaceText("{DEBUT}", startingDate);
  tempDocFile.saveAndClose();
  const pdfContentBlob = tempFile.getAs(MimeType.PDF);
  pdfFolder.createFile(pdfContentBlob).setName(pdfName);
  tempFolder.removeFile(tempFile);
}

Output

As for the output, I just assumed that debut = startingDate. The output should look like this:

The pdf folder has the created PDFs. And since you did not provide a copy of the doc file, I merely made a test case format:

Which should be converted to the one below once the script is done running: