遇到了保持新的条件规则的问题

huangapple
go评论79阅读模式
英文:

Having issues with keeping a new conditional rule to stay

问题

以下是格式化代码的翻译部分：

//
// 背景颜色
//

function whiteBackground() {
  // 向表格添加一个条件格式规则，使范围内的所有单元格背景颜色变为白色
  var CFstore = [];
  var sheet = SpreadsheetApp.getActive().getActiveSheet();
  var sBG = SpreadsheetApp.getActiveSpreadsheet().getActiveRange().getFontColorObject();
  var range = sheet.getActiveRange();
  var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenCellNotEmpty()
    .setBackground("#ffffff")
    .setFontColor(sBG)
    .setRanges([range])
    .build()
  var rules = sheet.getConditionalFormatRules();
  rules.pop(rule);
  rules.push(rule);
  sheet.setConditionalFormatRules(rules);
}

function blackBackground() {
  // 向表格添加一个条件格式规则，使范围内的所有单元格背景颜色变为黑色
  var sheet = SpreadsheetApp.getActive().getActiveSheet();
  var sBG = SpreadsheetApp.getActiveSpreadsheet().getActiveRange().getFontColorObject();
  var range = sheet.getActiveRange();
  var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenCellNotEmpty()
    .setFontColor(sBG)
    .setBackground("#000000")
    .setRanges([range])
    .build()
  var rules = sheet.getConditionalFormatRules();
  rules.pop(rule);
  rules.push(rule);
  sheet.setConditionalFormatRules(rules);
}

// 其余的颜色函数照此格式翻译即可，包括redBackground、orangeBackground等等。

//
// 字体颜色
//

function whiteFont() {
  // 向表格添加一个条件格式规则，使范围内的所有单元格字体颜色变为白色
  var sBG = SpreadsheetApp.getActiveSpreadsheet().getActiveRange().getBackground();
  var sheet = SpreadsheetApp.getActive().getActiveSheet();
  var range = sheet.getActiveRange();
  var rule = SpreadsheetApp.newConditionalFormatRule()
    .whenCellNotEmpty()
    .setBackground(sBG)
    .setFontColor("#ffffff")
    .setRanges([range])
    .build()
  var rules = sheet.getConditionalFormatRules();
  rules.pop(rule);
  rules.push(rule);
  sheet.setConditionalFormatRules(rules);
}

// 其余的颜色函数照此格式翻译即可，包括blackFont、redFont等等。

注意：这些代码是用于 Google Sheets 的条件格式规则，可以根据需要更改背景颜色和字体颜色。

英文:

I was having the issue of keeping the conditional formatting rule to stay. If you use the code and select a cell to color, it works. However if I want to do it for another cell, it'll erase the previous conditional formatting.

I know it has to do with pop(); and push();. But I can't find a way to make a list and have the formatting be added to that list. Along with that, ensuring that if I change that same cell, the rule would be overridden for that specific cell.

I'm currently trying to do it with rules.splice(); and I'll keep this post up to date.

The menu for the code to work

function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('🔵 Background Color')
.addItem('White', 'whiteBackground')
.addSeparator()
.addItem('Black', 'blackBackground')
.addSeparator()
.addItem('Red', 'redBackground')
.addSeparator()
.addItem('Orange', 'orangeBackground')
.addSeparator()
.addItem('Brown', 'brownBackground')
.addSeparator()
.addItem('Yellow', 'yellowBackground')
.addSeparator()
.addItem('Green', 'greenBackground')
.addSeparator()
.addItem('Cyan', 'cyanBackground')
.addSeparator()
.addItem('Blue', 'blueBackground')
.addSeparator()
.addItem('Purple', 'purpleBackground')
.addSeparator()
.addItem('Pink', 'pinkBackground')
.addToUi();
ui.createMenu('🔴 Font Color')
.addItem('White', 'whiteFont')
.addSeparator()
.addItem('Black', 'blackFont')
.addSeparator()
.addItem('Red', 'redFont')
.addSeparator()
.addItem('Orange', 'orangeFont')
.addSeparator()
.addItem('Brown', 'brownFont')
.addSeparator()
.addItem('Yellow', 'yellowFont')
.addSeparator()
.addItem('Green', 'greenFont')
.addSeparator()
.addItem('Cyan', 'cyanFont')
.addSeparator()
.addItem('Blue', 'blueFont')
.addSeparator()
.addItem('Purple', 'purpleFont')
.addSeparator()
.addItem('Pink', 'pinkFont')
.addSeparator()
.addToUi();
}

The formatting code

//
// Background colors
//
function whiteBackground() {
// Adds a conditional format rule to a sheet that causes all cells in the range to turn the background color white
var CFstore = [];
var sheet = SpreadsheetApp.getActive().getActiveSheet();
var sBG = SpreadsheetApp.getActiveSpreadsheet().getActiveRange().getFontColorObject();
var range = sheet.getActiveRange();
var rule = SpreadsheetApp.newConditionalFormatRule()
.whenCellNotEmpty()
.setBackground("#ffffff")
.setFontColor(sBG)
.setRanges([range])
.build()
var rules = sheet.getConditionalFormatRules();
rules.pop(rule);
rules.push(rule);
sheet.setConditionalFormatRules(rules);
}
function blackBackground() {
// Adds a conditional format rule to a sheet that causes all cells in the range to turn the background color black
var sheet = SpreadsheetApp.getActive().getActiveSheet();
var sBG = SpreadsheetApp.getActiveSpreadsheet().getActiveRange().getFontColorObject();
var range = sheet.getActiveRange();
var rule = SpreadsheetApp.newConditionalFormatRule()
.whenCellNotEmpty()
.setFontColor(sBG)
.setBackground("#000000")
.setRanges([range])
.build()
var rules = sheet.getConditionalFormatRules();
rules.pop(rule);
rules.push(rule);
sheet.setConditionalFormatRules(rules);
}
function redBackground() {
// Adds a conditional format rule to a sheet that causes all cells in the range to turn the background color red
var sheet = SpreadsheetApp.getActive().getActiveSheet();
var sBG = SpreadsheetApp.getActiveSpreadsheet().getActiveRange().getFontColorObject();
var range = sheet.getActiveRange();
var rule = SpreadsheetApp.newConditionalFormatRule()
.whenCellNotEmpty()
.setFontColor(sBG)
.setBackground("#fc1c1c")
.setRanges([range])
.build()
var rules = sheet.getConditionalFormatRules();
rules.pop(rule);
rules.push(rule);
sheet.setConditionalFormatRules(rules);
}
function orangeBackground() {
// Adds a conditional format rule to a sheet that causes all cells in the range to turn the background color orange
var sheet = SpreadsheetApp.getActive().getActiveSheet();
var sBG = SpreadsheetApp.getActiveSpreadsheet().getActiveRange().getFontColorObject();
var range = sheet.getActiveRange();
var rule = SpreadsheetApp.newConditionalFormatRule()
.whenCellNotEmpty()
.setFontColor(sBG)
.setBackground("#f78e14")
.setRanges([range])
.build()
var rules = sheet.getConditionalFormatRules();
rules.pop(rule);
rules.push(rule);
sheet.setConditionalFormatRules(rules);
}
function brownBackground() {
// Adds a conditional format rule to a sheet that causes all cells in the range to turn the background color brown
var sheet = SpreadsheetApp.getActive().getActiveSheet();
var sBG = SpreadsheetApp.getActiveSpreadsheet().getActiveRange().getFontColorObject();
var range = sheet.getActiveRange();
var rule = SpreadsheetApp.newConditionalFormatRule()
.whenCellNotEmpty()
.setFontColor(sBG)
.setBackground("#593408")
.setRanges([range])
.build()
var rules = sheet.getConditionalFormatRules();
rules.pop(rule);
rules.push(rule);
sheet.setConditionalFormatRules(rules);
}
function yellowBackground() {
// Adds a conditional format rule to a sheet that causes all cells in the range to turn the background color yellow
var sheet = SpreadsheetApp.getActive().getActiveSheet();
var sBG = SpreadsheetApp.getActiveSpreadsheet().getActiveRange().getFontColorObject();
var range = sheet.getActiveRange();
var rule = SpreadsheetApp.newConditionalFormatRule()
.whenCellNotEmpty()
.setFontColor(sBG)
.setBackground("#faf73c")
.setRanges([range])
.build()
var rules = sheet.getConditionalFormatRules();
rules.pop(rule);
rules.push(rule);
sheet.setConditionalFormatRules(rules);
}
function greenBackground() {
// Adds a conditional format rule to a sheet that causes all cells in the range to turn the background color green
var sheet = SpreadsheetApp.getActive().getActiveSheet();
var sBG = SpreadsheetApp.getActiveSpreadsheet().getActiveRange().getFontColorObject();
var range = sheet.getActiveRange();
var rule = SpreadsheetApp.newConditionalFormatRule()
.whenCellNotEmpty()
.setFontColor(sBG)
.setBackground("#14a633")
.setRanges([range])
.build()
var rules = sheet.getConditionalFormatRules();
rules.pop(rule);
rules.push(rule);
sheet.setConditionalFormatRules(rules);
}
function cyanBackground() {
// Adds a conditional format rule to a sheet that causes all cells in the range to turn the background color cyan
var sheet = SpreadsheetApp.getActive().getActiveSheet();
var sBG = SpreadsheetApp.getActiveSpreadsheet().getActiveRange().getFontColorObject();
var range = sheet.getActiveRange();
var rule = SpreadsheetApp.newConditionalFormatRule()
.whenCellNotEmpty()
.setFontColor(sBG)
.setBackground("#1095a3")
.setRanges([range])
.build()
var rules = sheet.getConditionalFormatRules();
rules.pop(rule);
rules.push(rule);
sheet.setConditionalFormatRules(rules);
}
function blueBackground() {
// Adds a conditional format rule to a sheet that causes all cells in the range to turn the background color blue
var sheet = SpreadsheetApp.getActive().getActiveSheet();
var sBG = SpreadsheetApp.getActiveSpreadsheet().getActiveRange().getFontColorObject();
var range = sheet.getActiveRange();
var rule = SpreadsheetApp.newConditionalFormatRule()
.whenCellNotEmpty()
.setFontColor(sBG)
.setBackground("#3e49b0")
.setRanges([range])
.build()
var rules = sheet.getConditionalFormatRules();
rules.pop(rule);
rules.push(rule);
sheet.setConditionalFormatRules(rules);
}
function purpleBackground() {
// Adds a conditional format rule to a sheet that causes all cells in the range to turn the background color purple
var sheet = SpreadsheetApp.getActive().getActiveSheet();
var sBG = SpreadsheetApp.getActiveSpreadsheet().getActiveRange().getFontColorObject();
var range = sheet.getActiveRange();
var rule = SpreadsheetApp.newConditionalFormatRule()
.whenCellNotEmpty()
.setFontColor(sBG)
.setBackground("#8c16c7")
.setRanges([range])
.build()
var rules = sheet.getConditionalFormatRules();
rules.pop(rule);
rules.push(rule);
sheet.setConditionalFormatRules(rules);
}
function pinkBackground() {
// Adds a conditional format rule to a sheet that causes all cells in the range to turn the background color pink
var sheet = SpreadsheetApp.getActive().getActiveSheet();
var sBG = SpreadsheetApp.getActiveSpreadsheet().getActiveRange().getFontColorObject();
var range = sheet.getActiveRange();
var rule = SpreadsheetApp.newConditionalFormatRule()
.whenCellNotEmpty()
.setFontColor(sBG)
.setBackground("#f86efa")
.setRanges([range])
.build()
var rules = sheet.getConditionalFormatRules();
rules.pop(rule);
rules.push(rule);
sheet.setConditionalFormatRules(rules);
}
//
// Font Colors
//
function whiteFont() {
// Adds a conditional format rule to a sheet that causes all cells in the range to turn the font color white
var sBG = SpreadsheetApp.getActiveSpreadsheet().getActiveRange().getBackground();
var sheet = SpreadsheetApp.getActive().getActiveSheet();
var range = sheet.getActiveRange();
var rule = SpreadsheetApp.newConditionalFormatRule()
.whenCellNotEmpty()
.setBackground(sBG)
.setFontColor("#ffffff")
.setRanges([range])
.build()
var rules = sheet.getConditionalFormatRules();
rules.pop(rule);
rules.push(rule);
sheet.setConditionalFormatRules(rules);
}
function blackFont() {
// Adds a conditional format rule to a sheet that causes all cells in the range to turn the font color black
var sBG = SpreadsheetApp.getActiveSpreadsheet().getActiveRange().getBackground();
var sheet = SpreadsheetApp.getActive().getActiveSheet();
var range = sheet.getActiveRange();
var rule = SpreadsheetApp.newConditionalFormatRule()
.whenCellNotEmpty()
.setBackground(sBG)
.setFontColor("#000000")
.setRanges([range])
.build()
var rules = sheet.getConditionalFormatRules();
rules.pop(rule);
rules.push(rule);
sheet.setConditionalFormatRules(rules);
}
function redFont() {
// Adds a conditional format rule to a sheet that causes all cells in the range to turn the font color white
var sBG = SpreadsheetApp.getActiveSpreadsheet().getActiveRange().getBackground();
var sheet = SpreadsheetApp.getActive().getActiveSheet();
var range = sheet.getActiveRange();
var rule = SpreadsheetApp.newConditionalFormatRule()
.whenCellNotEmpty()
.setBackground(sBG)
.setFontColor("#fc1c1c")
.setRanges([range])
.build()
var rules = sheet.getConditionalFormatRules();
rules.pop(rule);
rules.push(rule);
sheet.setConditionalFormatRules(rules);
}
function orangeFont() {
// Adds a conditional format rule to a sheet that causes all cells in the range to turn the font color white
var sBG = SpreadsheetApp.getActiveSpreadsheet().getActiveRange().getBackground();
var sheet = SpreadsheetApp.getActive().getActiveSheet();
var range = sheet.getActiveRange();
var rule = SpreadsheetApp.newConditionalFormatRule()
.whenCellNotEmpty()
.setBackground(sBG)
.setFontColor("#f78e14")
.setRanges([range])
.build()
var rules = sheet.getConditionalFormatRules();
rules.pop(rule);
rules.push(rule);
sheet.setConditionalFormatRules(rules);
}
function brownFont() {
// Adds a conditional format rule to a sheet that causes all cells in the range to turn the font color white
var sBG = SpreadsheetApp.getActiveSpreadsheet().getActiveRange().getBackground();
var sheet = SpreadsheetApp.getActive().getActiveSheet();
var range = sheet.getActiveRange();
var rule = SpreadsheetApp.newConditionalFormatRule()
.whenCellNotEmpty()
.setBackground(sBG)
.setFontColor("#593408")
.setRanges([range])
.build()
var rules = sheet.getConditionalFormatRules();
rules.pop(rule);
rules.push(rule);
sheet.setConditionalFormatRules(rules);
}
function yellowFont() {
// Adds a conditional format rule to a sheet that causes all cells in the range to turn the font color white
var sBG = SpreadsheetApp.getActiveSpreadsheet().getActiveRange().getBackground();
var sheet = SpreadsheetApp.getActive().getActiveSheet();
var range = sheet.getActiveRange();
var rule = SpreadsheetApp.newConditionalFormatRule()
.whenCellNotEmpty()
.setBackground(sBG)
.setFontColor("#faf73c")
.setRanges([range])
.build()
var rules = sheet.getConditionalFormatRules();
rules.pop(rule);
rules.push(rule);
sheet.setConditionalFormatRules(rules);
}
function greenFont() {
// Adds a conditional format rule to a sheet that causes all cells in the range to turn the font color white
var sBG = SpreadsheetApp.getActiveSpreadsheet().getActiveRange().getBackground();
var sheet = SpreadsheetApp.getActive().getActiveSheet();
var range = sheet.getActiveRange();
var rule = SpreadsheetApp.newConditionalFormatRule()
.whenCellNotEmpty()
.setBackground(sBG)
.setFontColor("#14a633")
.setRanges([range])
.build()
var rules = sheet.getConditionalFormatRules();
rules.pop(rule);
rules.push(rule);
sheet.setConditionalFormatRules(rules);
}
function cyanFont() {
// Adds a conditional format rule to a sheet that causes all cells in the range to turn the font color white
var sBG = SpreadsheetApp.getActiveSpreadsheet().getActiveRange().getBackground();
var sheet = SpreadsheetApp.getActive().getActiveSheet();
var range = sheet.getActiveRange();
var rule = SpreadsheetApp.newConditionalFormatRule()
.whenCellNotEmpty()
.setBackground(sBG)
.setFontColor("#1095a3")
.setRanges([range])
.build()
var rules = sheet.getConditionalFormatRules();
rules.pop(rule);
rules.push(rule);
sheet.setConditionalFormatRules(rules);
}
function blueFont() {
// Adds a conditional format rule to a sheet that causes all cells in the range to turn the font color white
var sBG = SpreadsheetApp.getActiveSpreadsheet().getActiveRange().getBackground();
var sheet = SpreadsheetApp.getActive().getActiveSheet();
var range = sheet.getActiveRange();
var rule = SpreadsheetApp.newConditionalFormatRule()
.whenCellNotEmpty()
.setBackground(sBG)
.setFontColor("#3e49b0")
.setRanges([range])
.build()
var rules = sheet.getConditionalFormatRules();
rules.pop(rule);
rules.push(rule);
sheet.setConditionalFormatRules(rules);
}
function purpleFont() {
// Adds a conditional format rule to a sheet that causes all cells in the range to turn the font color white
var sBG = SpreadsheetApp.getActiveSpreadsheet().getActiveRange().getBackground();
var sheet = SpreadsheetApp.getActive().getActiveSheet();
var range = sheet.getActiveRange();
var rule = SpreadsheetApp.newConditionalFormatRule()
.whenCellNotEmpty()
.setBackground(sBG)
.setFontColor("#8c16c7")
.setRanges([range])
.build()
var rules = sheet.getConditionalFormatRules();
rules.pop(rule);
rules.push(rule);
sheet.setConditionalFormatRules(rules);
}
function pinkFont() {
// Adds a conditional format rule to a sheet that causes all cells in the range to turn the font color white
var sBG = SpreadsheetApp.getActiveSpreadsheet().getActiveRange().getBackground();
var sheet = SpreadsheetApp.getActive().getActiveSheet();
var range = sheet.getActiveRange();
var rule = SpreadsheetApp.newConditionalFormatRule()
.whenCellNotEmpty()
.setBackground(sBG)
.setFontColor("#f86efa")
.setRanges([range])
.build()
var rules = sheet.getConditionalFormatRules();
rules.pop(rule);
rules.push(rule);
sheet.setConditionalFormatRules(rules);
}

答案1

得分: 0

我认为首先解释以下方法可能会有帮助：

  • pop - 从数组中删除最后一个元素并返回已删除的元素。这会改变数组的长度。
  • push - 将指定的元素添加到数组的末尾，并返回数组的新长度。原始数组将被修改。
  • splice - 通过删除或替换现有元素和/或在指定位置添加新元素来更改数组的内容。

你指出了两个问题。一方面，当使用菜单项时，你的代码有效并正确应用条件格式，但在后续调用时，先前的格式规则会丢失。这是由于你在函数中随处使用rules.pop(rule)调用所致（此外，pop不接受任何参数，所以通常当你需要它时，只需调用rules.pop()，但在这种情况下不需要）。删除这些调用应该使你的条件格式规则在通过菜单项的后续调用中保持存在，因为你对rules.push(rule)的调用将正确地将最新的条件格式规则添加到条件格式规则列表中。

另一个问题是覆盖已应用格式的范围中的条件格式。目前，你不断将规则添加到列表的末尾，因此如果一个范围有多个规则，最近应用的规则将被较早应用的规则掩盖，因为Sheets按顺序应用格式规则。这似乎不符合你的需求。你可以使用splice，但然后你需要添加一些逻辑来确定现有规则中哪些范围重叠（可能通过类似rule.getRanges().forEach((r) => r.getA1Notation())的方式），但这可能过于复杂（尤其是对于仅部分重叠的范围）。我建议取而代之利用unshift()，它是push()的反义词，将指定的元素添加到数组的开头。这样，如果你对相同范围应用条件格式，最新的规则将优先生效。不确定你的要求是什么，所以这可能不足够，但可能足以让你开始。

英文:

I think it might be helpful to explain the following methods first:

  • pop - removes the last element from an array and returns that removed element. This changes the length of the array.
  • push - adds the specified elements to thee end of the array and returns the new length of the array. The original array is modified.
  • splice - Changes the contents of an array by removing or replacing existing elements and/or adding new elements in the specified positions.

You pointed out two issues. On the one hand, your code works and correctly applies the conditional formatting when using the menu items, but on subsequent calls, previous formatting rules are lost. This is due to the rules.pop(rule) calls you have throughout your functions. (Also, pop does not accept any arguments, so normally when you need it, you just call rules.pop(), but it's unnecessary in this case regardless.) Removing those calls should have your conditional formatting rules persist on subsequent calls via the menu items as your calls to rules.push(rule) will correctly add the newest conditional formatting rules to the sheets list of conditional formatting rules.

Your other concern is regarding overwriting the conditional formatting in a range that already has formatting applied. At the moment, you are continuously adding rules to the end of the list, so if a range has multiple rules, the most recently applied rules will be overshadowed by rules that were applied earlier, as Sheets applies formatting rules in order. This does not sound like what you want. You could use splice, but then you would have to add some logic to determine which of the existing rules have overlapping ranges (likely via something like rule.getRanges().forEach((r) => r.getA1Notation())), but that may be unnecessarily complicated (especially for ranges that only partially overlap). I would recommend instead leveraging unshift(), which is the opposite of push() and adds the specified elements to the beginning of the array. That way if you apply conditional formatting to the same range, the newest rules will take precedent. Not 100% sure what your requirements are, so this may not be sufficient, but may be enough to get you going.

huangapple
  • 本文由 发表于 2023年7月17日 20:42:08
  • 转载请务必保留本文链接：https://go.coder-hub.com/76704563.html
  • google-apps-script
  • google-sheets
  • java
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定