使用脚本编辑器将条件格式应用于多列 [英] Apply Conditional Format to Multiple Columns with Script Editor

问题描述

我正在基于列A的值创建一个onEdit(e)函数来更新我的图表格式.我已将列A"设置为使用条件格式"和数据验证"下拉菜单进行适当的格式化，但是如何使与更新的单元格相邻的其他列具有相同的格式?

I am creating an onEdit(e) function to update my charts format, based on column A's value. I have Column A set up to format appropriately with Conditional Formatting and a Data Validation drop down menu, but how can I make the other columns adjacent to the updated cell the same format?

请参见下面的代码:

function onEdit(e) {

// Get current active sheet
  var actSh = SpreadsheetApp.getActiveSpreadsheet();

  // Get the integer value of the location the change is made at
  var row = e.range.getRow();
  var col = e.range.getColumn();

  // Define the max range in columns
  var max = actSh.getLastColumn();

  // Set a range to apply the conditional rule to
  var range = actSh.getRange(row,(col+1),1,(max-1));

  if (col == 1) {
    // Get Rule for Column A
    var rule = e.newConditionalFormatRules().copy();
    var newRule = range.setConditionalFormatRules(rule);
  }
}

下面是我正在尝试使编辑器工作的电子表格的屏幕截图，以供参考. 请参阅下面的链接.

Below is a screenshot of the spreadsheet I am trying to get the editor working on, for reference to what I am trying to accomplish. See Link below.

推荐答案

如何在新范围内扩展条件格式

步骤如下:

How to extend conditional formatting over new ranges

The steps are the following:

• 检索感兴趣的表
• 使用getConditionalFormatRules()
检索此工作表的条件规则
• 遍历所有规则
• 使用getRanges()
检索每个规则的ranges
• 浏览范围以确认您感兴趣的列是范围之一
• 如果发现该规则已应用于您感兴趣的列(if(ranges[j].getColumn() == 1))，请使用copy()
复制相应的规则
• 使用setRanges([range])将兴趣范围分配给新复制的规则，并使用build
完成请求
• 使用push()
将新规则添加到已存在的规则数组中
• 退出所有循环后，使用setConditionalFormatRules(rules)
用新的规则集更新工作表

• Retrieve the sheet of interest
• Retrieve the conditional rules for this sheet with getConditionalFormatRules()
• Loop through all the rules
• Retrieve the ranges for each rule with getRanges()
• Loop through the ranges to verify that the your column of interest is one of the ranges
• If the rule was found to be applied to your column of interest (if(ranges[j].getColumn() == 1)) - make a copy of the corresponding rule with copy()
• Assign a range of interest to the new copied rule with setRanges([range]) and finish the request with build
• Add the new rule to the array of already exisitng rules with push()
• After exiting all loops, update your sheet with the new set of rules with setConditionalFormatRules(rules)

样品

function onEdit(e) {  
  // Get current active sheet
  var actSh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();  
  // Get the integer value of the location the change is made at
  var row = e.range.getRow();
  var col = e.range.getColumn();  
  // Define the max range in columns
  var max = actSh.getLastColumn();  
  // Set a range to apply the conditional rule to
  var range = actSh.getRange(row,(col+1),1,(max-1));  
  if (col == 1) {
    var rules = actSh.getConditionalFormatRules();
    for (var i = 0; i < rules.length; i++){
      var ranges = rules[i].getRanges();
      for (var j = 0; j < ranges.length; j++){
        if (ranges[j].getColumn() == 1){
          var rule = rules[i].copy();
          rule.setRanges([range]).build();
          rules.push(rule);
          break;
        }
      }
    }
    actSh.setConditionalFormatRules(rules);
  }
}

参考

• Class ConditionalFormatRule
• Classal ConditionalFormatRuleBuilder

• Class ConditionalFormatRule
• Class ConditionalFormatRuleBuilder

这篇关于使用脚本编辑器将条件格式应用于多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！