脚本:如何从指定的单元格中检索条件格式设置规则并将其选择性地重新应用到范围中? [英] Script: How to retrieve conditional formatting rules from specified cells and selectively reapply them to ranges?

问题描述

在Google表格中，将剪切值粘贴到表格中时，可能会破坏条件格式应用于该表格中的单元格范围.我的工作表经常被同事编辑，它会破坏条件格式.我正在尝试创建一个脚本-在编辑时-从工作表的每一列中复制条件格式设置规则，然后将这些规则重新应用于粘贴到这些列中的任何单元格中.

In Google Sheets, when pasting cut values into a sheet, it can disrupt the range of cells that conditional formatting applies to in that sheet. My sheet is edited frequently by coworkers and it mangles the conditional formatting. I'm attempting to create a script that - on edit - copies the conditional formatting rules from each column of a sheet and then reapplies those rules to any cells pasted into each of those columns.

我是脚本的新手，所以很多内容都是由有用的用户在

I'm new to scripting, so much of this was compiled by a helpful user in a previous question I had posed.

function onEdit(e) {
  var range = e.range;
  var column = range.getColumn();
  var sheet = range.getSheet();
  range.clearFormat();
  //clears formatting just on data that is pasted into the sheet

  //Get all Sheet rules and iterate through them
  var rules = sheet.getConditionalFormatRules();
  var newRules = [];
  newRules = newRules.concat(rules);

  for(var r = 0; r < rules.length; r++) {
    var rule = rules[r];
    //Get condition for each rule
    var booleanCondition = rule.getBooleanCondition();

    //Get the ranges to which each rule applies and iterate through
    var ranges = rule.getRanges();
    for (var i = 0; i < ranges.length; i++) {
      var ruleColumn = ranges[i].getColumn();  

      //If condition isn't null and edited column is the same as the one in the range, add rule
      if((ruleColumn == column) && (booleanCondition != null)) {
        var newRule = SpreadsheetApp.newConditionalFormatRule()
        .withCriteria(booleanCondition.getCriteriaType(), booleanCondition.getCriteriaValues())
        .setBackground(booleanCondition.getBackground())
        .setRanges([range])
        .build();
        newRules.push(newRule);
     }
   }
 }
 sheet.setConditionalFormatRules(newRules);
}

这将采用工作表中存在的条件格式设置规则，并将其重新应用到任何已编辑的范围，有选择地仅将规则应用于从其复制的列.唯一的问题是，它会在每一列中复制每条规则，包括同一规则的迭代.每次修改工作表时，应用于已编辑单元格的规则的数量都会成倍增加，而且我担心我的计算机即将爆炸.

This takes the conditional formatting rules present in the sheet and reapplies them to any edited range, selectively applying rules only to the columns they were copied from. The only issue is that it copies every rule in each column, including iterations of the same rule. Every time the sheet is edited, the number of rules applied to edited cells grows exponentially and I'm scared that my computer is about to explode.

是否有一种方法只能从工作表的第一行提取条件格式设置规则并将其(按列)应用于已编辑的范围?这将避免我的规则永远泛滥的问题，对此我将非常感谢.

Is there a way to only pull the conditional formatting rules from the first row of the sheet and apply them (by column) to the edited range? This would stop my problem of perpetual proliferation of rules and I would be very thankful for that.

推荐答案

检索条件格式设置规则的唯一方法是使用getConditionalFormatRules函数[1]，该函数从工作表中检索所有规则.您可以获取每个规则范围的行，并添加条件以过滤仅应用于第一行的条件.

The only way to retrieve conditional formatting rules is with getConditionalFormatRules function [1] which retrieves all the rules from the sheet. You can get the row for each rule range and add a condition to filter the ones that are applied only to the first row.

function onEdit(e) {
  var range = e.range;
  var column = range.getColumn();
  var sheet = range.getSheet();
  range.clearFormat();
  //clears formatting just on data that is pasted into the sheet

  //Get all Sheet rules and iterate through them
  var rules = sheet.getConditionalFormatRules();
  var newRules = [];
  newRules = newRules.concat(rules);

  for(var r = 0; r < rules.length; r++) {
    var rule = rules[r];
    //Get condition for each rule
    var booleanCondition = rule.getBooleanCondition();

    //Get the ranges to which each rule applies and iterate through
    var ranges = rule.getRanges();
    for (var i = 0; i < ranges.length; i++) {
      var ruleColumn = ranges[i].getColumn();  
      var ruleRow = ranges[i].getRow();  

      //If condition isn't null and edited column is the same as the one in the range, add rule
      if((ruleColumn == column) && (ruleRow == 1) && (booleanCondition != null)) {
        var newRule = SpreadsheetApp.newConditionalFormatRule()
        .withCriteria(booleanCondition.getCriteriaType(), booleanCondition.getCriteriaValues())
        .setBackground(booleanCondition.getBackground())
        .setRanges([range])
        .build();
        newRules.push(newRule);
     }
   }
 }
 sheet.setConditionalFormatRules(newRules);
}

如果规则范围从第一行开始(例如:"A1"，"A1:A4"，"D1:D10"等)，这将复制规则.

This will copy the rule if the rule range starts in the first row (ie: 'A1', 'A1:A4', 'D1:D10', etc).

[1] https://developers.google.com/apps-script/reference/spreadsheet/sheet#getconditionalformatrules

这篇关于脚本:如何从指定的单元格中检索条件格式设置规则并将其选择性地重新应用到范围中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！