Hey guys. I’m no expert and haven’t written code for 30 years BUT, I have a very complex google sheet that has 100s of columns with formulae. Some cant use the arrayformula as they are custom functions that never anticipated that eventuality.
If found a tutorial on how to copy formulae down with an app script and just implemented across my columns. The code was all contained within a single function f=definition called CopyFormulae() { code }
I then set an onChange trigger to run the function. I added button to Glide called Add to Match Portal. This had an action of Add Row to Tutor Detail which allows the values of the Tutor Detail columns to be set by pointing at field values in the sheet opened in the Glide App. So you push the button, it adds static field values to the sheet and the onChange trigger fires and copies down all the formulae.
Works perfectly every time. Saving me so much time.
Snippet of App Script here which shows how
/*
* Copy Down Formulae after Adding New Tutor Row
*/
function CopyFormulae() {
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Tutor Detail");
/*
* Address
*/
ss.getRange("H9").setFormula('=CONCATENATE(D9," ",E9," ",F9," ",G9)') ;
var lr = ss.getLastRow();
var fillDownRange = ss.getRange(9,8,lr-8);
ss.getRange("H9").copyTo(fillDownRange);
"rest of code here"
}
Note: When I saved the msg it didn’t cope with the comment lines and formatted them strangely