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.