0

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),"")
TheMaster
  • 45,448
  • 6
  • 62
  • 85
Yvan L
  • 233
  • 3
  • 12
  • 1
    Well, have you looked at the API methods available to the `Range` class? Perhaps they may allow such tasks as getting or setting values or formulas – tehhowch Jul 12 '18 at 21:16
  • 1
    A3: `=arrayformula(If(len(D3:D),vlookup(D3:D,'Configuration List'!A2:B,2,0),""))` That's enough. – TheMaster Jul 12 '18 at 21:47
  • i used an array formula but the problem is sometime i need to change the value in those cell and then it breaks the array formula that is why i would like to insert it in all the new row created instead. – Yvan L Jul 12 '18 at 21:53
  • @Yvan Leduc If you are required to achieve it using the script, you can do it. In that case, can I ask you about what you want? When the formula is put to the created rows, how you want to modify each a1notation of the formula? – Tanaike Jul 12 '18 at 23:41
  • I need the script to find the last row with data and add 10 new rows and insert this formula in each of the 10 new rows so if the new row starts at row number 12 and creates 10 new row from row 12 to row 22 it would insert this formula so for exemple in row 12 column E it would have this formula =If(len(D12),vlookup(D12,'Configuration List'!A2:B,2,0),"") and in row 13 column E it would have this formula =If(len(D13),vlookup(D13,'Configuration List'!A2:B,2,0),"") so on so forth all the way to Row 22 – Yvan L Jul 12 '18 at 23:55

1 Answers1

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 :

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

Tanaike
  • 181,128
  • 11
  • 97
  • 165