CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Chrizh
Chrizh

Reputation: 25

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

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.

Upvotes: 0

Views: 1620

Answers (1)

Mike Steelson
Mike Steelson

Reputation: 15318

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

Upvotes: 1

Related Questions