Here is another one that I use all the time as an alternative to ARRAYFORMULA.
The neat thing about this is that it works with any formulas, whereas ARRAYFORMULA has some limitations.
Again, I didn’t write this one myself, and I can’t recall where I got it from (probably Stackoverflow), so I can’t take any credit for it. But I’ve been using it for 3 or 4 years and it’s never failed me.
What it does and how it works is described in the comments.
Summary
/**
* Copy Formulas Down
* This script copies functions from a source row (usually the first data row in a spreadsheet) to
* rows at the end of the sheet, where the colums for these formulas are still empty.
* It checks row from the bottom upwards, copying formulas into the rows where there are no formulas yet,
* and stops as soon as it finds a row which has a formula already.
* When copying formulas, it leaves values in other cells unchanged.
*/
/**
* Copy formulas from the a source row in a sheet to rows below
* @param {Sheet} sheet Google Spreadsheet sheet
* @param {int} sourceRow (optional) 1-based index of source row from which formulas are copied, default: 2
*/
function copyFormulasDown(sheet, sourceRow) {
if (sourceRow === undefined) {
sourceRow = 2;
}
var formulas = getFormulas_(sheet, sourceRow);
if (formulas !== {}) {
var rows = sheet.getDataRange().getFormulas();
for (var r = rows.length - 1; r >= sourceRow && rows[r].join('') === ''; r--) {
copyFormulas_(sheet, r, formulas);
}
}
}
/**
* Copy formulas into row r in sheet
* @param {Sheet} sheet Google Spreadsheet sheet
* @param {int} r 1-based index of row where formulas will be copied
* @param {array} formulas array of objects with column index and formula string
*/
function copyFormulas_(sheet, r, formulas) {
for (var i = 0; i < formulas.length; i++) {
sheet.getRange(r + 1, formulas[i].c + 1).setFormulaR1C1(formulas[i].formula);
}
}
/**
* Read formulas from the source row, creating an array of objects
* Each objects contains the column index and formula string
* @param {Sheet} sheet Google Spreadsheet sheet
* @param {int} r 1-based index of source row
* @return {array} array of objects
*/
function getFormulas_(sheet, r) {
var row = sheet.getRange(r, 1, 1, sheet.getLastColumn()).getFormulasR1C1()[0];
var formulas = [];
for (var c = 0; c < row.length; c++) {
if (row[c] !== '') {
formulas.push({
c: c,
formula: row[c]
});
}
}
return formulas;
}
Edit: one thing I should point out is that if you’re adding rows then you need to run this every time a new row (or rows) get added. But that’s easy enough with a trigger.