Trying to stop my mail merge function from running from Row 2 each time a new row is added

Question

As the title says:

Each time a new row is added, the program is supposed to only run the latest inserted row cos i am using a "Success" string to stop it from running an already merged row.

How do i make sure it only runs or merges newly inserted row not starting from row 2 each time the function runs? Even though i have added in my code "Success" string so that it sees it and skips that row.

Below is my code:

var Success = "Success";

function createBulkPDFs()
{
    const pdfFolder = DriveApp.getFolderById("1_jdFncxgkyCuH181MpwyYf25gWrWHMO");

    const docFile = DriveApp.getFileById("1XAn-1MqH6ER01dnbATLzG9gYWQpFDKfQhUwWHRRcyo");

    const tempFolder = DriveApp.getFolderById("141fjXWkg0fU3q0u22WUPkJDl82JakTj");

    const currentSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");

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

    let errors = [];
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").activate();
    var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    var lr = ss.getLastRow();

    for (var i = 2; i <= lr; i++)
    {
        var check = ss.getRange(i, 33).getValue();

        if (check != Success)
        {
            data.forEach(row =>
            {
                try
                {
                    createPDF(row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9],
                        row[10], row[11], row[12], row[13], row[14], row[15], row[16], row[17], row[18],
                        row[19], row[20], row[21], row[22], row[23], row[24], row[25], row[26], row[27],
                        row[28], row[29], row[30], row[31], row[3], docFile, tempFolder, pdfFolder);

                    errors.push(["Success"]);
                }
                catch (err)
                {
                    errors.push(["Failed"]);
                }
            }); //close forEach

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

        }

    }

}

function createPDF(RepTime, Rep_Name, RepIC_No, Ven_Company, RepDs, Ven_Comp_Regis, Ven_Cont_no, Ven_Fax_no, Ven_Add,
    PODquestionone, PODquestiontwo, PODquestionthree, PODquestionfour, PODquestionfive, PODquestionsix, RPOquestionone,
    RPOquestiontwo, RPOquestionthree, RPOquestionfour, RPOquestionfive, RPOquestionsix, RPOquestionseven,
    CIACquestionone, CIACquestiontwo, Prepquestionone, MACCquestionone, MACCquestiontwo, MACCquestionthree,
    MACCquestionfour, WCquestionone, WCquestiontwo, WCquestionthree, pdfName, docFile, tempFolder, pdfFolder)
{

    SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").activate();
    var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    var lr = ss.getLastRow();

    //for(var i = 2; i<=lr; i++){

    var checks = ss.getRange(i, 33).getValue();
    //var che = ss.getRange(i,1).getValue();

    //if(checks != ""){

    const tempFile = docFile.makeCopy(tempFolder);

    const tempDocFile = DocumentApp.openById(tempFile.getId());

    const body = tempDocFile.getBody();
    body.replaceText("{RepTime}", RepTime);
    body.replaceText("{RepFullName}", Rep_Name);
    body.replaceText("{RepICNo}", RepIC_No);
    body.replaceText("{VCompany}", Ven_Company);
    body.replaceText("{RepDesig}", RepDs);
    body.replaceText("{VCompanyRegis}", Ven_Comp_Regis);
    body.replaceText("{VConNo}", Ven_Cont_no);
    body.replaceText("{VFaxNo}", Ven_Fax_no);
    body.replaceText("{VAddress}", Ven_Add);

    body.replaceText("{PODQuestionone}", PODquestionone);
    body.replaceText("{PODQuestiontwo}", PODquestiontwo);
    body.replaceText("{PODQuestionthree}", PODquestionthree);
    body.replaceText("{PODQuestionfour}", PODquestionfour);
    body.replaceText("{PODQuestionfive}", PODquestionfive);
    body.replaceText("{PODQuestionsix}", PODquestionsix);

    body.replaceText("{RPOQuestionone}", RPOquestionone);
    body.replaceText("{RPOQuestiontwo}", RPOquestiontwo);
    body.replaceText("{RPOQuestionthree}", RPOquestionthree);
    body.replaceText("{RPOQuestionfour}", RPOquestionfour);
    body.replaceText("{RPOQuestionfive}", RPOquestionfive);
    body.replaceText("{RPOQuestionsix}", RPOquestionsix);
    body.replaceText("{RPOQuestionseven}", RPOquestionseven);

    body.replaceText("{CIACQuestionone}", CIACquestionone);
    body.replaceText("{CIACQuestiontwo}", CIACquestiontwo);

    body.replaceText("{PrepQuestionone}", Prepquestionone);

    body.replaceText("{MACCQuestionone}", MACCquestionone);
    body.replaceText("{MACCQuestiontwo}", MACCquestiontwo);
    body.replaceText("{MACCQuestionthree}", MACCquestionthree);
    body.replaceText("{MACCQuestionfour}", MACCquestionfour);

    body.replaceText("{WCQuestionone}", WCquestionone);
    body.replaceText("{WCQuestiontwo}", WCquestiontwo);
    body.replaceText("{WCQuestionthree}", WCquestionthree);

    tempDocFile.saveAndClose();

    const pdfContentBlob = tempFile.getAs(MimeType.PDF);

    const pdfFile = pdfFolder.createFile(pdfContentBlob).setName(pdfName);

    tempFolder.removeFile(tempFile);
}

Answer 1

A very likely explanation for if (check != Success) returning false is that the active sheet ss is not the same like currentSheet.

You can avoid this kind of errors by skipping activate() and getActiveSheet() requests - instead retrieve your sheet of interest always by name.

Now, if you are using a form submit trigger, you can restructure your code in a more elegant way by using event objects.

• The formSubmit event object allows you to retrieve directly all relevant information related to newest form response - that is the values of the response and the range where this form response was inserted into the sheet.
• You do not need to loop through the whole sheet to find the correct row.

Sample to be run on formSubmit trigger:

const pdfFolder = DriveApp.getFolderById("1_jdFncxgkyCuH181MpwyYf25gWrWHMO");
const docFile = DriveApp.getFileById("1XAn-1MqH6ER01dnbATLzG9gYWQpFDKfQhUwWHRRcyo");
const tempFolder = DriveApp.getFolderById("141fjXWkg0fU3q0u22WUPkJDl82JakTj");

function createBulkPDFs(event)
{ 
  let error;
  var row = event.values;
 // have a look at the values:
  Logger.log(row);
  try {
    createPDF(row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9], row[10], row[11], row[12], row[13], row[14], row[15], row[16], row[17], row[18], row[19], row[20], row[21], row[22], row[23], row[24], row[25], row[26], row[27],row[28], row[29], row[30], row[31], row[3], docFile, tempFolder, pdfFolder);
    error = "Success";
  } catch (err){
     error="Failed";
  }
  var row = event.range.getRow();
  var currentSheet = event.range.getSheet();
  currentSheet.getRange(row, 33).setValue(error);
}
function createPDF(...){
  ...
}

When using funcitons with event objects, keep in mind that they can only be triggered by an event (form submit) - if you try to run the funciton manually - it will error because the event object won't be defined.