Google Sheets Preset Conditional Formatting Rules?

Question

I currently have a Google sheet conditionally formatted the way I need it. The problem is when I share the sheet, people often copy and paste cells messing up the conditional formatting rules. Is it possible to somehow store my preferred conditional format rules in a macro, then when someone edits the sheet, it will auto reset any changes to the preferred rules?

Answer 1

Try duplicating the sheet where you have the pristine conditional formatting rules in place. To restore the formatting:

1. Go to the pristine sheet.
2. Press Control+A once or twice to select all cells.
3. Press Control+C.
4. Go to the messy sheet.
5. Select cell A1.
6. Choose Edit > Paste special > Paste conditional formatting only.

The process can be automated with a script like this:

function resetMessyConditionalFormatting() {
  const ss = SpreadsheetApp.getActive();
  const pristineRange = ss.getSheetByName('Pristine').getDataRange();
  const messyRange = ss.getSheetByName('Messy').getRange('A1');
  pristineRange.copyTo(messyRange, SpreadsheetApp.CopyPasteType.PASTE_CONDITIONAL_FORMATTING, false);
}