Google Apps Script - While loop

Question

I would appreciate any inputs on this Google Apps Script code!

I am trying to implement a do-while loop to copy the formula from cell B2 to C2 and follow that sequence from B3 to c3, B4 to c4, and so on.

The condition that I want to give to the do while loop is that as long as the active cell is > 0, the loop runs. If I remove the loop from the script and I run the code manually, it will work but with the code it gets stuck.

Copy of challenge and script in tab 1 and 2 here: https://docs.google.com/spreadsheets/d/1-3ZZBar2BExaFhOMzpjpAbDFMTDmupuX4x0irwv3ixM/copy

Images below:

Answer 1

This function should do what you are asking for:

function fillFormulaDown() {
  const sheet = SpreadsheetApp.getActiveSheet();
  let currentCell = sheet.getActiveCell();
  while (currentCell.getValue()) {
    let formula = currentCell.getFormulaR1C1();
    if (formula) {
      currentCell.offset(0, 1).setFormulaR1C1(formula);
    }
    currentCell = currentCell.offset(1, 0);
  }
}

...but that is an inefficient way of copying a larger number of formulas, because each cell is read and written separately. You can make the code run tens of times faster with something like this:

function betterFillFormulaDown() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const currentCell = sheet.getActiveCell();
  const numRows = sheet.getLastRow() - currentCell.getRow() + 1;
  const currentColumn = currentCell.offset(0, 0, numRows, 1);
  const values = currentColumn.getValues().flat();
  const formulas = currentColumn.getFormulas().flat();
  const numFormulasToCopy = values.findIndex((value, index) => !value || !formulas[index]);
  if (!numFormulasToCopy) {
    return;
  }
  if (numFormulasToCopy === -1) {
    numFormulasToCopy = numRows;
  }
  const rangeToCopy = currentCell.offset(0, 0, numFormulasToCopy, 1);
  rangeToCopy.offset(0, 1).setFormulasR1C1(rangeToCopy.getFormulasR1C1());
}

The better function runs faster, because it gets and sets a larger number of cells in one go. Operating on one cell with .setFormula() costs about the same as operating on one thousand cells with .setFormulas().

The copying will stop at the first cell that is blank, zero or does not contain a formula.