Clash Royale CLAN TAG#URR8PPP

I have added a Row using a condition (If/Else Statement), how should I add a formula to the newly inserted row

Here is the code I have used.

function myFunction()
var app = SpreadsheetApp;
var ss = app.getActiveSpreadsheet()
var activeSheet = ss.getSheetByName("WOF")
ss.getRange("B14:F14").setFormula("=INDEX($H2:$BE2,INT(COLUMN()+(ROWS(B$14:B14)-1.1)*5))");
var cellp2 = activeSheet.getRange("p2").getValue();
if(cellp2 > 0)
activeSheet.insertRowsAfter(14, 1);

var cellu2 = activeSheet.getRange("u2").getValue();
if(cellu2 > 0)
activeSheet.insertRowsAfter(14, 1);

var cellz2 = activeSheet.getRange("z2").getValue();
if(cellz2 > 0)
activeSheet.insertRowsAfter(14, 1);

var cellae2 = activeSheet.getRange("ae2").getValue();
if(cellae2 > 0)
activeSheet.insertRowsAfter(14, 1);

var cellaj2 = activeSheet.getRange("aj2").getValue();
if(cellaj2 > 0)
activeSheet.insertRowsAfter(14, 1);

var cellao2 = activeSheet.getRange("ao2").getValue();
if(cellao2 > 0)
activeSheet.insertRowsAfter(14, 1);

var cellat2 = activeSheet.getRange("at2").getValue();
if(cellat2 > 0)
activeSheet.insertRowsAfter(14, 1);

var cellay2 = activeSheet.getRange("ay2").getValue();
if(cellay2 > 0)
activeSheet.insertRowsAfter(14, 1);

var cellbd2 = activeSheet.getRange("bd2").getValue();
if(cellbd2 > 0)
activeSheet.insertRowsAfter(14, 1);




1 Answer
1

Using the DRY (don't repeat yourself) guideline, we want to reduce code duplication as much as possible: any duplicated cases that could be handled together, should be. In your script, you repeat the same behavior on a number of ranges, and have written (the same) code for each one, changing only the string passed to getRange (and using a new variable name to hold the Range reference:

getRange

Range

var cellp2 = activeSheet.getRange("p2").getValue();
if (cellp2 > 0)
activeSheet.insertRowsAfter(14, 1);



A better approach would store the range names in an Array, which we could then iterate with a for loop, or use the Array#forEach method.

Array

for

Array#forEach

const rangesToCheck = [
"p2",
"u2",
...
];


If only 1 row needs to be inserted if any of the ranges meet the criteria, we should use the for loop, as we can exit the loop prematurely:

for

for (var i = 0; i < rangesToCheck.length; ++i)
if (activeSheet.getRange(rangesToCheck[i]).getValue() > 0)
activeSheet.insertRowsAfter(14, 1);
break; // exit the "nearest" loop scope.




Array#forEach is explicitly used when you want to do the same thing for each element in the Array. I.e., "For each" range, if its value is > 0, add a new row after row 14.

Array#forEach

Array

rangesToCheck.forEach(function (a1)
if (activeSheet.getRange(a1).getValue() > 0)
activeSheet.insertRowsAfter(14, 1);

);


This last case - multiple row insertion - can be improved by first counting the number of rows to insert, and then doing a single insert (i.e. changing the number of inserted rows from 1 to numPositiveValues:

1

numPositiveValues

var numPositiveValues = 0;
rangesToCheck.forEach(function (a1)
if (activeSheet.getRange(a1).getValue() > 0)
++numPositiveValues;

);
// Add as many rows as desired:
if (numPositiveValues > 0)
activeSheet.insertRowsAfter(14, numPositiveValues);



We can further optimize the above approaches (both single- and multiple-row insertions), using the relatively-new RangeList class to acquire disjoint Ranges from the same worksheet. An example use for multiple-row insertion is:

RangeList

Range

var numPositiveValues = 0;
const rl = activeSheet.getRangeList(rangesToCheck);
rl.getRanges().forEach(function (rg)
if (rg.getValue() > 0)
++numPositiveValues;

);
if (numPositiveValues > 0)
activeSheet.insertRowsAfter(14, numPositiveValues);



Simplifying your code in this manner means you now only need to handle setting values (and/or formatting, formulas, etc.) for the inserted rows in 1 place, rather than every place where you might add rows:

if (numPositiveValues > 0)
var insertedRangeToModify = activeSheet.insertRowsAfter(14, numPositiveValues)
.getRange(14, /** start column index */, numPositiveValues, /** end column index */);
// insertedRangeToModify.setValue(...)
// insertedRangeToModify.setValues(...)
// insertedRangeToModify.setFormula(...)
// insertedRangeToModify.setFormulas(...)
// insertedRangeToModify.setFormulaR1C1(...)
// insertedRangeToModify.setFormulasR1C1(...)
// etc.



Additional references

const

break

Sheet#insertRowsAfter

Sheet#getRange(row, col, numRows, numCols)

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.