Preserving row format and formula when inserting new row in Google Sheets using Apps Script?-CodePudding

I have a Google Sheets database:

https://docs.google.com/spreadsheets/d/1VzHY8fTq8OsXhpHYHESSSPxeVNOnqxpjcsyWJpbuEOs/edit?usp=sharing

'Base Stats' sheet (Sheet 1) is regularly updated with new rows added. Row 1 is a header row. Row 2 onward columns 5-8 have font colours applied. Column 6 has a drop down list. Columns 7, 12 & 13 have formulas. When a new row is inserted, currently it will not copy the formatting and formula detailed above.

I did some searching and I found this possible answer:

function onOpen(e){
 var ui = SpreadsheetApp.getUi()
 ui.createMenu("Format sheet").addItem("Format", "setFormat").addToUi()

}
function onEdit(e){           //This is activated each time a modification happens in the sheet
  var ss = SpreadsheetApp.getActive()
  var sheet =ss.getActiveSheet()
  if(sheet.getSheetName() == "Base Stats"){
  var entryRange = e.range
  var range  = sheet.getRange(2,entryRange.getColumn(),1,entryRange.getNumColumns())              //This will be you range to get the formatting from row "1" and corresponding column based on the column being edited
  Logger.log(entryRange.getA1Notation())
  range.copyFormatToRange(sheet, entryRange.getColumn(), entryRange.getNumColumns() entryRange.getColumn()-1, entryRange.getRow(), entryRange.getNumRows() entryRange.getRow()-1)
  Logger.log(entryRange.getColumn())
  if(entryRange.getColumn() == 12){                                 //This column value will not be allowed modified except for row 1 of that column
    if (entryRange.getRow() != 2){                                  //The columns in row "1" will be allowed to modified
    e.range.setValue((e.oldvalue == undefined? "": e.oldvalue))
    }
  }
  }
}

function setFormat(){
  var ss = SpreadsheetApp.getActive()
  var sheet = ss.getActiveSheet()
  var firstRow = sheet.getRange(1, 1, 1, sheet.getLastColumn())
  var dataRange = sheet.getDataRange()
  firstRow.copyFormatToRange(sheet, 1, dataRange.getNumColumns(), 2, dataRange.getNumRows())

}

However, I'm not sure what I need to edit in the script to apply to my Google Sheets example? Any help greatly appreciated.

CodePudding user response：

You do not need to copy formulas, change for arrayformula, example in M1

={"Score 
(Weighted)";arrayformula((H2:H*2) (E2:E*1.5) (F2:F G2:G))}

immediately apply formatting for all columns (and whole columns) and you won't need to copy formatting rules