I've created a Google Sheets document intended for daily use by more than a dozen people. Text inputted in two of the columns is set up, via conditional formatting, to change colour based on the name entered (Smith = Red, Baker = Blue, Williams = Green and so on). I'm also running a script that forces all entries in these columns to be capitalised.

The issue I have is that all users need to be able to move the column entries up and down as and when required. For example, a job may be scheduled at 6am, then need to be moved down to an 8am slot.

When that happens, the formatting breaks and has to be replaced.

I understand there may be a way to lock or force the formatting using a control column/row and a script, but nothing I've tried seems to work. Any help or pointers would be much appreciated!

  • @Rubén Are you proposing this as a duplicate because of your answer of "it's not possible" on the other question? Otherwise, this seems like a different problem altogether.
    – jonsca
    Jan 5, 2019 at 2:16
  • @jonsca I consider that the both questions are related (and one a dubplicate of the other) because both are about cell permissions. Contrary as occurs on Excel, on Google Sheets it's not possible to "lock" any cell properties while allowing to edit its value/formula. Jan 5, 2019 at 2:29
  • @Rubén Haha, your answer wasn't visible when I saw this in the queue! Fair enough.
    – jonsca
    Jan 5, 2019 at 2:33

1 Answer 1


Google Sheets doesn't include a built-in feature to lock/force the cell formatting.

One alternative is to use a dialog or side panel for user input.

Another alternative is to use a script to reapply the correct format as needed. For this you could use an onEdit or onChange triggers.



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