Google Apps Script: Clear conditional formatting when marking cells complete with notes

Other Questions Google Sheets
1

I’m working on a project tracking spreadsheet where I need to use notes to mark tasks as finished. The sheet has conditional formatting that highlights upcoming dates with colors.

I want cells with notes to turn gray (showing they’re complete) but the conditional formatting keeps overriding my gray background. I found some code that works for regular cells but breaks when conditional formatting is involved.

Here’s what I’m using:

function markComplete() {
  var activeSheet = SpreadsheetApp.getActiveSheet();
  var range = activeSheet.getDataRange();
  var cellNotes = range.getNotes();
  var backgrounds = range.getBackgrounds();
  
  for (var row = 0; row < cellNotes.length; row++) {
    for (var col = 0; col < cellNotes[0].length; col++) {
      if (cellNotes[row][col]) {
        backgrounds[row][col] = '#808080';
      }
    }
  }
  
  range.setBackgrounds(backgrounds);
}

I tried adding activeSheet.clearFormats() but it crashed the whole sheet. How can I remove conditional formatting from specific cells without breaking everything? I just need the gray color to show through when a cell has a note attached to it.

2

Try clearConditionalFormatRules() on specific cells instead of the entire sheet. Use getRange(row+1, col+1) to grab the cell, then call clearConditionalFormatRules() on it before setting the gray background. This won’t mess with your other formatting rules.

3

Had the same problem last month with my task sheet. Here’s what worked: create a separate conditional formatting rule just for cells with notes instead of messing with your existing ones. Set up a new rule with higher priority using =LEN(N(NOTE(A1)))>0 as the formula and apply your gray formatting. Drag it across your whole data range and put it above your date rules in the formatting panel. The gray kicks in first when there’s a note, and your date colors only show on unmarked tasks. Way cleaner than trying to clear formats with code.

4

Conditional formatting always beats manual background colors - that’s your problem. Don’t try to override it, just tweak the rules instead. Add an extra condition to your existing formatting that skips cells with notes. Use a custom formula like =NOT(N(CELL("note", A1))>0) in your current rules. This makes conditional formatting ignore any cells that have notes. Your gray background will show up fine since there’s no competing format anymore. Just remember to add this exclusion to all your conditional formatting rules that mess with date highlighting.