Is there a way to protect/copy only some formatting in Google Sheets?

Question

I have a spreadsheet that I use to track customer orders. It has the customer details on a row, their order number and a line of check boxes for the items that they want for that particular order. For the items they want, the check box is ticked and then when the item is manufactured, the cell with the respective checkbox is highlighted.

I'm looking to protect the formatting, or rather have it reapplied from a master cell should the need arise, as there are numerous people that need to access and edit the data but the formatting goes wonky after a short period of time, especially when copying data from one sheet to another. I came across the below script which seems to do the job wonderfully;

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() == "Sheet1"){
  var entryRange = e.range
  var range  = sheet.getRange(1,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() == 10){                                 //This column value will not be allowed modified except for row 1 of that column
    if (entryRange.getRow() != 1){                                  //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(3, 1, 1, sheet.getLastColumn())
  var dataRange = sheet.getDataRange()
  firstRow.copyFormatToRange(sheet, 1, dataRange.getNumColumns(), 3, dataRange.getNumRows())

}

The catch is that it does its job a little too wonderfully.

Whenever I highlight one of the fields that has a checkbox, the script forces the cell back to white, or whatever formatting is in the parent cell.

I'm unable to apply a blanket colour one way or another because I need both to be able to see what has and hasn't been manufactured.

Is there a way to modify the above script to (preferably) ignore the cell colour or to get it to ignore certain columns?

The formatting that I'd like to keep constant is font, size, positioning (left) and all cell borders.

Here's a link to the original thread where the code was posted that I'm using.

Here's a link to my spreadsheet so you can see what I mean.

Thanks in advance.

Answer 1

Google Sheets doesn't include a way to protect only some formatting. By the other hand it doesn't include a way to copy only some formatting.

One alternative is what you already did, to make a function to reapply the required formatting. A complete solution, I think, should include a way to figured out if it's needed to reapply the required formatting and it will be executed only when it's necessary.

You should replace the copyTo code line by other lines that apply the required formatting, like

• setBackgrounds
• setBorders
• etc.

See https://developers.google.com/apps-script/reference/spreadsheet/range for the available methods.

Answer 2

Thank you for your help, Ruben.

I have created the below to replace what I had;

function onEdit(e){
  var ss  = SpreadsheetApp.getActiveSheet();

  var range = ss.getDataRange();
  range.setBorder(true, true, true, true, true, true);
  range.setFontSize('10');
  range.setFontFamily('Arial');
  range.setHorizontalAlignment('left');
  range.setFontWeight('normal');
}

Is there a way to exclude columns F:N and rows 1:2?