Is there a way to lock or protect conditional formatting in Google Sheets?

Question

I have several columns that indicate the current state of a project task. When I complete some task (for example), I copy the "complete" text from some other cell and paste it in the new cell to avoid typing it (task states change often and it would be a massive pain to have to type every state manually).

How can I prevent conditional formatting from changing or moving when I copy/paste/move cells in Google Sheets?

Is it possible to "lock" or protect the conditional formatting? Or perhaps is there a way to tell conditional formatting to use absolute cell references instead of relative?

It's incredibly annoying that I don't seem to be able to apply it to a whole column regardless of how I move data around. I'm aware I can edit the cell and copy/cut/paste the text inside without affecting formatting, but that doesn't help when I need to change several cells at once.

---

Here's an example spreadsheet. Cut cells E14:G14 and paste them down one row to make room for another subtask of "Do something arty", then try to type "up next", "in progress" or "complete". There will be no coloured background because the conditional formatting cell references have become messed up.

Answer 1

I'm afraid that currently it's not possible to use cut & paste / drag & drop. Consider to send a feature request directly to Google through Google Feedback: Click on menu Help > Report a problem.

An alternative to using Cut and Paste is to use Insert Cells > Shift down

1. Select E14:G14
2. Right clic over the selected range
3. Click on Insert Cells > Shift down

NOTE: Keyboard shortcut for Spanish Latinamerican layout Ctrl++ with override Chrome shorcuts enabled.

Answer 2

While @Rubén's answer is correct (and will remain accepted), for future reference I did actually come up with a script that completely sorts out my problem, though it's a little quirky.

function onEdit(e) {
  var sleepTime = 5000;
  var lastEditedTime = new Date().getTime();
  PropertiesService.getScriptProperties().setProperty('lastEditedTime', lastEditedTime);
  Utilities.sleep(sleepTime);
  var newTime = new Date().getTime();
  if(newTime - PropertiesService.getScriptProperties().getProperty('lastEditedTime') < sleepTime - 50) {
    return;
  }

  var range = e.range; //range just edited
  var sheet = range.getSheet();
  if(sheet.getName() !== 'Tasks') {
    return;
  }

  var rules = sheet.getConditionalFormatRules();
  var newRules = [];
  for(var r = 0; r < rules.length; r++) {
    var booleanCondition = rules[r].getBooleanCondition();
    if(booleanCondition != null) {
      var rule = SpreadsheetApp.newConditionalFormatRule()
        .withCriteria(booleanCondition.getCriteriaType(), booleanCondition.getCriteriaValues())
        .setBackground(booleanCondition.getBackground())
        .setRanges([sheet.getRange("E:E"),sheet.getRange("G:G")])
        .build();
      newRules.push(rule);
      sheet.setConditionalFormatRules(newRules);
    }
  }
};

Since onEdit is called for every edit you make, and the conditional formatting takes about 0.5 - 1 second to update, if you make several edits very quickly the script will run several times very close together, and subsequent runs after the first one may try to read the conditional formatting rules while they're still updating, and end up effectively removing some or all rules.

This would be easy to fix if we could use setTimeout, but Google Apps Scripts are synchronous and don't support that function. Instead each time the script runs it records the current timestamp in a property (which is global across script executions), then sleeps for 5 seconds. If the current time after waking is less than ~4.95 seconds after the value recorded in the lastEditedTime property, that means the script has been run at least once more since this execution (because the lastEditedTime value has been updated to a later time). Therefore this isn't the most recent execution of the script so we just return and do nothing.

Then it's a simple matter of reading the current conditional formatting rules and writing them back again, but with the correct ranges set.

The script works pretty well and so far hasn't screwed anything up, but sometimes I notice it run twice. I'm not sure if I'm somehow making two edits less than 50ms apart or if it's something to do with the way the scripts get executed on Google's servers but it hasn't caused problems so far.

Answer 3

I use a script like this to remove all conditional formatting and put it back how I want it and tied it to onEdit so that it would run after anyone changed a cell and then they can cut paste or whatever and it will just rebuild. Couldn't find a better solution.

    function onEdit(e) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Worksheet');
  //var rules = sheet.getConditionalFormatRules();
  var rules = [];
  sheet.clearConditionalFormatRules();


/*
  // Logs the conditional format rules in a sheet.
  //var rules = SpreadsheetApp.getActiveSheet().getConditionalFormatRules();
  for (var i = 0; i < rules.length; i++) {
    var rule = rules[i];
    Logger.log(rule);
  }
*/


  var rangeToHighlightTeamA = sheet.getRange("b6:b50");
  var ruleTeamA = SpreadsheetApp.newConditionalFormatRule()
      .whenFormulaSatisfied('=u6<>""')
      .setBackground("#46bdc6")
      .setRanges([rangeToHighlightTeamA])
      .build();
  rules.push(ruleTeamA);


  sheet.setConditionalFormatRules(rules);
}