Force conditional formatting apply

Question

I have a google spreadseet table with conditional background formatting rules on it. I also have a function that autosort sheet when user change a cell (it's a list of tasks with priorities). Unfortunately, the autosorting can somehow set a wrong background for cells which can be fixed manually by selecting a column and then clicking "background->reset parameters" button. I need to do the same with script automatically to keep correct backgrounding of cells. How do I force google-spreadsheet to re-render cells with its conditional formatting rules? Autosorting function:

function onEdit(event){
  var sheet = event.source.getActiveSheet();
  var editedCell = sheet.getActiveCell();

  var columnToSortBy = 1;
  var tableRange = "A2:Z1000";

  if(editedCell.getColumn() == columnToSortBy){   
    var range = sheet.getRange(tableRange);
    range.sort( { column : columnToSortBy, ascending: false } );
    // code below doesn't help to solve the problem:
    //var statusRange = sheet.getRange("B2:B1000");
    //statusRange.clear({formatOnly: true})
    //statusRange.clearFormat();
    //SpreadsheetApp.flush()
  }
}

Niccolo Niccolo · Accepted Answer · 2014-08-15T12:16:31

What is possibly happening is that you have applied conditional formatting to a range of cells.

When your sheet is sorted by a script some/all of the cells may be affected because the range the conditional formatting applies to is being moved.

I will try to answer your question but I think you may be looking at the problem from the wrong angle. Given that your table is huge, entire page except header row? You could be applying the conditional formatting incorrectly. If it is acting on the whole page then reordering the page should not affect it. I have seen similar problems when rows are added or deleted within a formatting range. So if you want to post your formatting rules I may spot something there.

Getting back to your questions.

var range = sheet.getRange(tableRange); is correct.

I don't think you can use

statusRange.clear({formatOnly: true})
statusRange.clearFormat();

because .clear and .clearFormat are applied to the sheet.

Your table looks like it is taking up the entire sheet except for a header row? If so then try.

sheet.clearFormat();

This will clear your sheet formats so you are now left with white background colour.

Now, I do not know any way of using a script to set conditional formatting rules directly. I do have two possible solutions that I have used myself.

Abandon conditional formatting and set the format you want with a script.

You can set various cell formats directly, one of the the Background colour. I don't know hoe you want to do this, your logic, but there are several functions to do this with CSS or RGB or to set a range to different colours.

When you have selected your cell or range you can use

range.setBackground("red");

red can be replaced with notation e.g. '#ff0000'.

Use a hidden conditional formatting template .

After your header row create another row. So adjust your table range to

var tableRange = "A3:Z1000";

Now row 2 becomes your template. Set the conditional formatting rules you want in row 2 and hide the row. Don't clear the formatting, instead copy the format form row 2 over the page.

var sheet  = event.source.getActiveSheet();
var source = sheet.getRange(2,1,1,26); // Gets row 2 

source.copyFormatToRange(sheet, 1, 26, 3, 1000);  // will repeat down rows.

Force conditional formatting apply

1 Answers

Abandon conditional formatting and set the format you want with a script.

Use a hidden conditional formatting template .