I've created the below function which copies formulas I have in cells K2:M2 to column B:D only if column A contains the word "Copy".
However, the performance is very slow looping through each cell to determine if the cell in column A contains "Copy". How can I optimize this loop function?
function myFunction() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var Sheet1 = ss.getSheetByName("Sheet1");
var copy = Sheet1.getRange('K2:M2')
for (var i=1; i<=1000; i = i + 1)
if (Sheet1.getRange(i,1,1,1).getValue() == 'Copy'){
copy.copyTo(Sheet1.getRange(i,2,1,3),SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
}
}
function myFunction() {
var ss = SpreadsheetApp.getActive();
var sh = ss.getSheetByName("Sheet1");
var rg = sh.getRange("K2:M2");
var cs = rg.getFormulas();
var vs = sh.getRange(1,1,sh.getLastRow()).getValues();
var fs = sh.getRange(1,2,sh.getLastRow(),3).getFormulas();
vs.forEach((r,i) => {
if(r[0] == "Copy") {
fs[i][0] = cs[0][0];
fs[i][1] = cs[0][1];
fs[i][2] = cs[0][2];
}
})
sh.getRange(1,2,fs.length,3).setFormulas(fs);
}