To extend formulas to entire columns within Google Sheets, we recommend using the useful ARRAYFORMULA function. Unfortunately, this does not work in combination with some other important functions such as QUERY or INDIRECT , which is why you have to completely customize the logic using Google Apps Script or, alternatively, manually copy the formula down to the last line.
As an alternative for the second option, we'll create a small helper function that searches for formulas in row 2 of all worksheets and automatically copies them to the end of the worksheets. ARRAYFORMULA formulas are automatically ignored. This function can be added via Tools > Script Editor and scheduled to run once per minute using Edit > Trigger for the current project.:
f9c221988a2b56958ffb5b0649bbdf27