How to speed up heavy conditional formatting rules

Question

At our marketing company/agency, we're using a master tracker in Google Sheets to keep track of all paid advertising campaigns that we are handling for our clients. The document is getting longer and longer, and the variety of conditional formatting rules we are using is getting heavy and slow upon any change made to the document.

Five employees are using the document at any given time, making changes to the "STATUS" column upon any change to the campaign – if it is ready to upload, if it is LIVE, if it is paused etc. The conditional formatting simply changes the color of each line based on the value in the "STATUS" column. It also looks at the start/end dates and marks the line red if there is an issue. Etc.

How can I speed up processing using this document? I have created a minified version of our tracker with one line for each conditional formatting rule to make it easy for you to have a look.

I'm sure there are smarter ways to consolidate the rules and/or build a script that can handle the task more easily and more efficiently.

Answer 1

This answer uses a script to change the background color of a row whenever the Status is changed (works for "READY", "LIVE" and "DONE").

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

The script is viewable under the "Tools - Script Editor..." menu. It is activated by an "onEdit" trigger (see Is it possible to automate Google Spreadsheets Scripts (e.g. without an event to trigger 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);
  }

}