If I'm using a script to autofill column Q, and an "On Edit" trigger, can I stop the FillColumn script at a specific row?

Question

I'm pretty new to scripts, and think there is probably a way to fix this, but I don't know how. What I have now is a script that automatically fills the Q Column. It also has on 'On Edit' trigger so if we add a row it will fill as well.

My problem is that it fills the whole column, never ending.

1. Can I have it stop based on the contents of another cell in the row I want it to stop at?
2. If not, can I just make is stop at row 70, for example? If I can set to stop at 70, will I still able to add rows and have the range end shift with the new rows (i.e. move the stopping row to 71 if I insert a row within the range?

Here's the script currently running:

`function FillFormulas() {   

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');   

var lastRow = spreadsheet.getLastRow();   

spreadsheet.getRange("Q2").setFormula("=IF(ISBLANK(A2),0,1)");   
var fillDownRange = spreadsheet.getRange(2,17,(lastRow-1));   spreadsheet.getRange("Q2").copyTo(fillDownRange); 


}`

Answer 1

Reading your question and your code, it looks to me like what you want to do is just make sure that the formula at Q2 (top of the sheet) is copied down automatically for every new row, so long as the value in another cell (in the same row) matches some criteria? If that is correct, maybe something like this will work for you...

function fillFormulas() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName('Sheet1');
  var qRange = ss.getRange('Q2:Q'); //all of row Q
  var qValues = qRange.getFormulas();
  var checkRange = ss.getRange('A2:A') //or whatever column in the row it is you want to use as your test
  var checkValues = checkRange.getValues();
  var hasChanged = false;

  for (i=0; i < checkValues.length; i++) {
    if ( checkValues[i][0].length >  0 ) {
    //change the test above to whatever you need it to be
      qValues[i][0] = "IF(ISBLANK(A2),0,1)";
      hasChanged = true;
    }
  }

  if (hasChanged){ //only update the sheet if needed
    qRange.setFormulas(qValues);
  }
}

And if your sheet is having data added to it from a Google form, the Copy Down plugin might help too - https://workspace.google.com/u/0/marketplace/app/copy_down/889269636541