insert formula in newly created rows

Question

Hi everyone was wondering how do i insert a formula into the newly created row this script inserts 10 new row and i would like to instert a formula in the 10 new created row in the column E

var ss = SpreadsheetApp.getActive();
var target = ss.getSheetByName('Data Entry');
target.insertRowsAfter(target.getMaxRows(), 10)

what would i need to insert this formula in those newly created rows

=If(len(D3:D),vlookup(D3:D,'Configuration List'!A2:B,2,0),"")

Answer 1

You want to add rows to the last row, and put the formulas to column E in the created rows. You want to modify "D3:D" of the formulas. If my understanding is correct, how about these 2 solutions? I think that there are several solutions for your situation. So please think of this as two of them.

Pattern 1 :

In this script, it creates 2 dimensional array including the formulas. And put them to column E of the created rows using setFormulas().

var ss = SpreadsheetApp.getActive();
var target = ss.getSheetByName('Data Entry');
var maxRow = target.getMaxRows();
var r = target.insertRowsAfter(maxRow, 10);
var formulas = [];
for (var i = 1; i < maxRow; i++) {
  formulas.push(["=If(len(D" + (maxRow + i) + ":D),vlookup(D" + (maxRow + i) + ":D,'Configuration List'!A2:B,2,0),\"\")"]);
}
target.getRange(maxRow + 1, 5, 10, 1).setFormulas(formulas);

Pattern 2 :

In this script, it creates a formula. And put it to column E of the created rows using setFormula().

var ss = SpreadsheetApp.getActive();
var target = ss.getSheetByName('Data Entry');
var maxRow = target.getMaxRows();
var r = target.insertRowsAfter(maxRow, 10);
var formula = "=If(len(D" + (maxRow + 1) + ":D),vlookup(D" + (maxRow + 1) + ":D,'Configuration List'!$A$2:B,2,0),\"\")";
target.getRange(maxRow + 1, 5, 10, 1).setFormula(formula);

Note :

• Please select one of them for your situation.

References :

• setFormula(formula)
• setFormulas(formulas)

If I misunderstand your question, please tell me. I would like to modify it.