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.

Obviously the most straight-forward solution, thanks for pointing it out. If we wish to keep the entire rows, I am sure there is more process-efficient way of doing what we've done? Like combining rules and or finding a different solution altogether. Any thought on this? — Henrik Söderlund, Aug 25 '16 at 01:43

score 4 · Accepted Answer · edited May 23 '17 at 11:46

4

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);
  }

}

edited May 23 '17 at 11:46

Community

1
1

answered Aug 25 '16 at 10:18

opowell

568
4
20

how interesting! A couple of questions, are scripts less resource-heavy than CF? If I need to adjust the color of the text for each row at the same time (change background color & text color), what is the additional code for this? – Henrik Söderlund Aug 26 '16 at 02:20
I do not know whether scripts are more or less resource-intensive than CF. The function for text color is "setFontColor", see https://developers.google.com/apps-script/reference/spreadsheet/range#setfontcolorcolor. – opowell Aug 26 '16 at 07:59
Thanks for your help, the bounty is yours :-) If you have a solution to the other conditaionl formatting rules in the document, that would be most helpful too. There are a few overlapping ones, that need to run in certain order to work properly that I still struggle with. – Henrik Söderlund Sep 01 '16 at 02:08
Happy to help :) Re: the other rules, if you write down in plain language the functionality you want (this happens if this, otherwise the other thing, etc., as you did in your question). I'd be happy to help. – opowell Sep 01 '16 at 11:17
I started typing it out, but honestly, it is much easier to understand by simply observing the rules that are already existing inside the test document above. There are 13 CF rules always running in order on all lines, it would be awesome if they could be scripted instead. – Henrik Söderlund Sep 05 '16 at 02:31
Part of the reason I suggested writing it out is that it will help you when it comes time to write the code. I don't want to write the code for you, but if you make an attempt to add it to the script above, I am happy to help fix any errors. – opowell Sep 05 '16 at 07:58

score 3 · Answer 2 · answered Apr 25 '18 at 16:54

I had whole rows changing colors depending on some conditions. So I extracted complex formulas from conditional formatting panel into columns on sheet (I got "TRUE" or "FALSE") and referenced those columns in conditional formatting rules. For some reason calculation of conditional formatting formulas is much slower than same calculation inside cells.

How to speed up heavy conditional formatting rules

2 Answers2

Linked