How to speed up the implementation of conditional formatting rules

Question

At our marketing company / agency, we use the master tracker in Google Sheets to track all the paid advertising campaigns that we process for our customers. The document becomes longer and longer, and the various conditional formatting rules that we use become heavy and slow with any changes made to the document.

Five employees use the document at any time, making changes to the "STATUS" column for any changes in the campaign - if it is ready to download, if it is LIVE, if it is paused, etc. Conditional formatting simply changes the color of each row based on the value in the STATUS column. It also looks at the start / end dates and marks a red line if there is a problem. Etc.

How to speed up processing with this document? I created a mini version of our tracker with one line for each conditional formatting rule to make it easier for you to view.

I am sure that there are more reasonable ways to consolidate the rules and / or build the script that can handle the task more easily and efficiently.

Answer 1

This answer uses a script to change the background color of a line when the status changes (works for "READY", "LIVE" and "PERFECT").

Live demo: https://docs.google.com/spreadsheets/d/1bVwM1wSBVlZTmz5S95RXSrRQxlTKWWN_Hl4PZ81sbGI/edit?usp=sharing

the script can be viewed in the menu "Tools - script Editor ...". It is activated by the onEdit trigger (see. Is it possible to automate scripts of Google tables (for example, without an event to run them)? )

Here is the script itself:

function onEdit(e) { var STATUS_COL = 18; var MAX_COLS = 18; var COLOR_READY = "grey"; var COLOR_LIVE = "#512da8"; var COLOR_DONE = "green"; var activeSheet = SpreadsheetApp.getActiveSheet(); var cell = activeSheet.getActiveSelection(); var val = cell.getValues()[0][0]; var color = null; switch (val) { case "READY": color = COLOR_READY; break; case "LIVE": color = COLOR_LIVE; break; case "DONE": color = COLOR_DONE; break; } if (color != null) { var row = activeSheet.getRange(cell.getRow(), 1, 1, MAX_COLS); row.setBackgroundColor(color); } }