google spreadsheet array formula vs. paste down best practice

I really can't provide any references for this answer, it is based on anecdotal evidence and discussion with other Google Sheets users.

For a relatively simple calculation, like the one in your question, I believe the array solution has better performance for very large data sets. It also mitigates against the possibility of bumping the formula limit in Sheets (40,000, but CONTINUE functions populated by array formulae do not contribute to this count).

However, for very complicated calculations, both options really have the ability to grind the spreadsheet to a halt. Notorious examples of this are array formulae where we need to resort to string manipulation (eg CONCATENATE-ing arrays together, and SPLIT-ting them apart again). In this case, I personally would go to plan C, and write a Google Apps Script custom function, that takes an array (arrays) as an argument (arguments), uses pure Javascript to manipulate the data, and outputs an array.

You can kinda test this with two scripts

Multiplying 30,000 rows where each cell is 10 by 10

The Auto expansion takes ~ .275 s The CopyDown ~ .678 s

https://docs.google.com/spreadsheets/d/1djHUp_kTS02gYnKf5Y3AvpHCIt_69x_X02eesOSywzw/edit?usp=sharing

 function AutoExpand() {
 var ss    =SpreadsheetApp.getActive();
 var sheet =ss.getSheetByName('AAA');
 var LC    = sheet.getLastColumn();
 var LR    = sheet.getLastRow();

 var start = new Date();
 //Auto-expanding Formulas to be added
 //Two dim array with 1 row
 var formulas = [["=ArrayFormula(A:A*10)"]];

 //Add auto-expanding formulas to Cell(s)
 var cell = sheet.getRange(1,LC+1,1,formulas[0].length);
 cell.setFormulas(formulas);
 SpreadsheetApp.flush();

 //Get range and post back Display Values
 //var r = sheet.getRange(1,LC+1,LR,formulas[0].length);
 //var v = r.getDisplayValues();
 //r.setValues(v);

 var end = new Date();
 var executiontime = end - start;
 Logger.log(executiontime); 
 }

CopyDown

function CoppyDown() {
var ss    =SpreadsheetApp.getActive();
var sheet =ss.getSheetByName('AAA');
var LC    = sheet.getLastColumn();
var LR    = sheet.getLastRow();

var start = new Date();
//NON Auto-expanding Formula(s) to be added
//Two dim array with 1 row
var formulas = [["=A:A*10"]];

//Add NON auto-expanding formula(s) to Cell(s)
var cell = sheet.getRange(1,LC+1,1,formulas[0].length);
cell.setFormulas(formulas);
SpreadsheetApp.flush();

//Get range FULL Range of Cells W/Formulas
var r = sheet.getRange(1,LC+1,LR,formulas[0].length);

//Add formulas to Row1 Cell(s)
var cells = sheet.getRange(1,LC+1,1,formulas[0].length);
cells.setFormulas(formulas);

//Copy formulas down
cells.copyTo(r);
SpreadsheetApp.flush();

//Get the Display Values of the Range W/Formulas
//var v = r.getDisplayValues();

//Clear the Formulas Before the Postback
//This Super Speeds up the Postback
//r.clear();

//Postback Formulas as Values
//r.setValues(v);

var end = new Date();
var executiontime = end - start;
Logger.log(executiontime); 
}