添加新行时，Google表格条件格式会发生变化 [英] Google Sheets Conditional Formatting changes when new rows added

问题描述

所以我点击A和1之间的单元格来选择整个表格，然后点击格式，然后条件格式并设置规则。基本上，我有大约15个不同的条件，但都在列F到O，所以我使用F：O。例如，如果文本正好是YES，则将背景更改为绿色。

问题是，当我添加一个新行时，该行的格式停止并且F：O规则被替换为F1：O15，F17：O59等，跳过第16行。

我可以使用一个在添加行时永不改变的脚本吗？

解决方案

您可以设置一个onEdit触发器，它在您每次编辑工作表时应用格式。我提供了一个将单元格A1的格式复制到表单中所有单元格的函数的示例。此链接会将您带到Google的此类工作文档中。
https://developers.google.com/apps-script/reference / spreadsheet / range

以下是关于触发器的文档...
https://developers.google.com/apps-script/guides/triggers/

  function formatRange（）{var ss = SpreadsheetApp.getActiveSpreadsheet（）; var sheet = ss.getSheetByName（Sheet1）; var range = sheet.getRange（A1）; range.copyFormatToRange（sheet，1，sheet.getLastColumn（），1，sheet.getLastRow（））}  

So I click on the cell between A and 1 to select entire sheet, then I click "Format" then "Conditional Formatting" and set the rules. Basically, I have about 15 different conditions but all are in columns F through O so I use F:O. For example, if text is exactly YES change the background to green.

The issue is when I add a new row, the formatting stops for that row and the F:O rules are replaced with F1:O15, F17:O59, etc. skipping row 16.

Can I use a script that will never change when rows are added?

解决方案

You can set up an onEdit trigger that applies the formatting every time you edit the sheet. I've provided an example of a function that would copy the format of cell A1 to all cells in the sheet. This link will bring you to Google's documentation for this type of work. https://developers.google.com/apps-script/reference/spreadsheet/range

Here's the documentation on triggers... https://developers.google.com/apps-script/guides/triggers/

function formatRange(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet1");
  var range = sheet.getRange("A1");
  
  range.copyFormatToRange(sheet,1,sheet.getLastColumn(),1,sheet.getLastRow())
  
}

这篇关于添加新行时，Google表格条件格式会发生变化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！