Google-sheets – protect the formatting of a Google Sheets

google sheetsgoogle-apps-scriptgoogle-sheets-cell-formatgoogle-sheets-protection

I’ve got a Google Sheets that has lots of formatting and formulas, in various columns, some columns are formula driven, and some columns that are user input driven. The spreadsheet is shared with various users and I administer the spreadsheet.

Problems :

  • Formula protection :
    I don’t want a user of the spreadsheet to be able to affect any formulas columns, so I want to protect. There is a built in protect feature (either range, or sheet based) in Google Sheets, but this also stops you inserting new rows, which I still need to allow users to do.

  • Format protection :
    As users input data into the spreadsheet over time, the formatting always breaks and/or gets changed.

Is there a way to lock the formatting of a spreadsheet so that it can’t be changed?

For example, Column F would always be:

text-align: right aligned,
cell-format: financial
2 decimal places
text-color: black text
cell-background: light green background
font: Arial

I've had a look online but these features can't be achieved via inbuilt Google Sheets settings, is the above achievable via custom Google Sheets scripting?

Best Answer

The way to address the problem is to use a combination of custom function in google spreadsheet and google script. I will use examples to try to address solution for your problem

Formula protection :

My understanding is you will have users enter new entries into a new row. This means you could apply the formula automatically to this new row, which might prevent them from modifying that formula in the first place

1) To apply the formula to the new row, use ArrayFormula. The below example will sum values from column F to Column I in each row

=arrayFormula((F:F+G:G+H:H+I:I))
 read as = F1+G1+H1+I1 in row 1, =F2+G2+H2+I2 in row2 and so on

you can get more elaborate and remove 0 values from the sheet like so

=arrayFormula(if((F:F+G:G+H:H+I:I) = 0,"",(F:F+G:G+H:H+I:I)))

More details can be found here. By doing so you negate the need for a user to type out the formula and hence lesser modification. ArrayFormula automatically applies/fixes the formula even when a new row is inserted or deleted.

2) Since the arrayFormula would be applied in the first row (or a specific row). Set Protection in the first row(or that specific row) so that only you can modify it and which indirectly means no one can modify any formula in the sheet. This doesn't prevent user for modifying below row 1 which affects how arrayFormula works, that is why you have use column formula protection (described Below)

Format Protection:

1) Similar to above, you can set up the first row to be the master row which will be used to format the remaining rows. Since you will also lock it down, no one except you will be able to modify it!

2) To automatically copy the formatting, you can use onEdit(e) trigger in google sheets. The below code does exactly that:

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(1, 1, 1, sheet.getLastColumn())
  var dataRange = sheet.getDataRange()
  firstRow.copyFormatToRange(sheet, 1, dataRange.getNumColumns(), 2, dataRange.getNumRows())

}

Copy the above to your script Editor in google sheets and save it. Then reload your google sheet. At this point, anything you modify in row "1" will be unaffected. However, any changes to rows below that will trigger onEdit to automatically copy the format from the first row and apply it to that row in the corresponding column.

Column Formula Protection: This code within the onEdit function will prevent modification to specific column, column 10 in this case. But can be modified to protect any column. (To understand why we need this, read Formula Protection: 2) above)

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

It will replace the oldvalue back in column 10 from column 2 onwards. However, if someone copies a whole new row in, which also happens to modify row 10, this will not prevent it from getting modified.

You can modify it to allow you access to modify row 10 like so

if(entryRange.getColumn() == 10 && Session.getEffectiveUser().getEmail() != "Your Email Here")

Note: If you make this modification, make sure to run the onEdit function from script editor once to give it permission to access your emailID.

Finally, When you reload the sheet you will notice a new menu item called Format Sheet, you can use this to apply the format in row "1" to all the rows below it! Which is done by the function setFormat(). This a fallback code, in case people modify the formatting after onEdit trigger sets the formatting. onEdit(e) is not triggered when the user modifies the format of a cell.

Hope that helps!

Related Topic