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
  range.copyFormatToRange(sheet, entryRange.getColumn(), entryRange.getNumColumns()+entryRange.getColumn()-1, entryRange.getRow(), entryRange.getNumRows()+entryRange.getRow()-1)
  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.

  • The included code doesn't protect the formatting. It includes three functions but the question doesn't describe clearly how or when they are used. Jun 3, 2019 at 22:59
  • I have updated the post which hopefully makes it clearer about what I'm trying to achieve. I understand that the code doesn't 'protect' the formatting but reapplies it from another cell.
    – Steven P
    Jun 3, 2019 at 23:15

2 Answers 2


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.

  • The above script does reapply the required formatting but I believe that it takes it from a designated cell of each column and applies it to the rest of that column? Meaning that if I make F3 have a background colour of green, it will apply the same thing to the rest of column F. Or the other way around, if I make F4 green, the script will apply the default formatting from F3 of white and apply that, overriding my change.
    – Steven P
    Jun 3, 2019 at 23:29
  • @StevenP I edited my answer. Jun 3, 2019 at 23:34

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);

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.