如何加快繁琐的条件格式设置规则 [英] How to speed up heavy conditional formatting rules

问题描述

在我们的营销公司/代理商中，我们使用Google表格中的主跟踪器来跟踪我们为客户处理的所有付费广告系列.文档变得越来越长，并且对文档进行的任何更改都会使我们使用的各种条件格式设置规则变得繁琐和缓慢.

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.

五名员工在任何给定时间使用该文档，如果对广告系列进行任何更改，则在状态"列中进行更改-如果该文档准备好上载，是否为LIVE，是否已暂停等，则为条件格式.只需根据状态"(STATUS)列中的值更改每行的颜色.它还会查看开始/结束日期，并在出现问题时将行标记为红色.等等.

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.

推荐答案

状态更改时，此答案使用脚本更改行的背景色(适用于"READY"，"LIVE"和"DONE")

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

现场演示: https://docs.google.com/spreadsheets/d/1bVZP4R5 edit?usp = sharing

可以在工具-脚本编辑器..."菜单下查看该脚本.它由"onEdit"触发器激活(请参见

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)?).

以下是脚本本身:

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

}

这篇关于如何加快繁琐的条件格式设置规则的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！