2

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.

2

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.

  • 1
    That works, but it's much slower and more fiddly than copy-paste. I was hoping for a way to make conditional formatting use absolute references rather than work around the dodgy relative references. Incidentally Ctrl-+ brings up the "shift cells" popup but only after I turned on a setting in Sheets to override default Chrome shortcuts (and it's a very awkward shortcut anyway). – Clonkex Aug 22 '18 at 3:52
  • I also may not want to simply add a single set of cells or a single row, it's often more complicated than that and I want to be able to quickly cut and paste data around. – Clonkex Aug 22 '18 at 3:53
  • 1
    @Clonkex I'm afraid that currently it's not possible. Consider to send a feature request directly to Google through Google Feedback: Click on menu Help > Report a problem. – Rubén Aug 22 '18 at 4:01
  • If you want to put that comment into an answer I'll mark it correct. It's not what I wanted to hear but it sounds like it's the correct answer. – Clonkex Aug 22 '18 at 4:16
  • @Clonkex Done! :) – Rubén Aug 22 '18 at 4:22
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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