Script Assistance to Add New Line & Copy formulas

[yvettew78]

Hi Guys

I am trying to work out a script so that when I click on the PLUS Button I have created it inserts a new row and inserts the formulas that relate to it. Please see a screenshot of the sheet.

At the moment the script I have running is as follows:-

function insertRowsAfter() {
var ss = SpreadsheetApp.getActiveSpreadsheet()
var s = ss.getActiveSheet()
var cell = s.getActiveCell()
var row = cell.getRow()

var num = Browser.inputBox("Enter No. of rows:")
s.insertRowsAfter(18,1)
}


Cell B18 is a dropdown field, as follows:-

B18 also has a reset / clear cell formula using a script. The script is as follows-
function ClearCells() {
var sheet = SpreadsheetApp.getActive().getSheetByName('Calculator');
sheet.getRange('B7').setValue('Select Panel');
sheet.getRange('B8').setValue('Select Mounting Equipment');
sheet.getRange('B9').setValue('Select Inverter');
sheet.getRange('B10').setValue('Select Smart Meter');
sheet.getRange('B13').setValue('Select Extras');
sheet.getRange('B14').setValue('Select Extras');
sheet.getRange('B15').setValue('Select Extras');
sheet.getRange('E3').setValue('Select');
sheet.getRange('E4').setValue('Select');
sheet.getRange('E5').setValue('Select');
sheet.getRange('C8').setValue('=ROUNDUP(C7/6,0)');
sheet.getRange('C7').clearContent();
sheet.getRange('C9:C27').clearContent();

}

Cell D18 formula: =IFERROR(VLOOKUP(B18,Extras!A5:B24,2,FALSE),0)
Cell E18 formula: =+C18*D18

If someone can help with how I keep the formatting when I run the script to enter a new row, that would be awesome.

Thank you

 

[rondeondo]

Hi Yvette
When you make a new line have you tried a copy&paste for the line above? Either for a specific range or cell by cell in a loop.
A table is another option, when you add a line to a table the formulas and formats automatically get repeated in the new row