添加条件格式设置规则 [英] Add conditional formatting rule

问题描述

Google表格有一个很好的交互方式来声明性地定义条件格式。有没有办法使用应用程序脚本编写相同的声明模型？

我有一个脚本可以生成许多电子表格。我使用工作表和 DataValidation 类。但是，我似乎无法为条件格式做同样的事情。我希望找到一个 ConditionalFormattingRule 对象或类似的东西。

注意：我知道自定义脚本可以编写的代码可以监听 onEdit触发器。但是，该脚本需要添加到生成的电子表格的每个电子表格中。此外，这种非声明性规则不会出现在条件格式侧栏中

我简单地将红色背景应用于没有值的单元格。

是这是不可能的？

解决方案

我相信你将不得不在这种情况下使用解决方法，如果适合你的情况。

您必须在源电子表格中创建一个模板表格，该表格的格式为条件格式（由您手动完成）。然后您的脚本会将此模板表复制到您的目标电子表格中，然后使用 copyTo 方法将模板表单中一个或一系列单元格的格式复制到您的选择工作表（最后，您可以从目标电子表格中删除此模板工作表）。 （copyTo方法仅在同一电子表格中从一个范围复制到另一个范围）。

类似这样：

<$ （）{
var targetSs = SpreadsheetApp.openByUrl（'https://docs.google.com/spreadsheets/d/1dLv8V5fKmmxRLi5maLIOgrxVGVaqGGOD7STHbEremQs/edit#gid=0'） ;
var targetSsDisplaySheet = targetSs.getSheets（）[0];
var sourceSs = SpreadsheetApp.openByUrl（'https://docs.google.com/spreadsheets/d/14RxLXktm0wj-lSFrl5Fas9B-smMYhknFt3-dYQbwH3o/edit#gid=933388275'）;
var templateSheet = sourceSs.getSheetByName（'Template'）;
var targetSsFormatTemplateSheet = templateSheet.copyTo（targetSs）;

targetSsFormatTemplateSheet.getRange（A1）。copyTo（targetSsDisplaySheet.getRange（B：D），{formatOnly：true}）;
targetSs.deleteSheet（targetSsFormatTemplateSheet）;
}

Google Sheets has a great interactive way to declaratively define conditional formatting. Is there a way to program the same declarative model using App Script?

I have a script that generates many spreadsheets. I set data and data validation rules using the Sheet and DataValidation classes. However, I can't seem to do the same for conditional formatting. I expected to find a ConditionalFormattingRule object or something like that.

Note: I know that custom script can be written that listens for an onEdit trigger. However, that script would need to be added to each of the generated spreadsheets. Also, non-declarative rules like that would not appear in the conditional formatting sidebar.

I'm simply trying to apply a red background to cells that have no value.

Is this just not possible?

解决方案

I believe that you will have to use a workaround in this case, if appropriate for your circumstance.

You would have to create a template sheet that had cells formatted by conditional formatting (manually done by you) in a source spreadsheet.

Then your script will copy this template sheet over to your target spreadsheet, and then use the copyTo method with advanced parameters of {formatOnly:true} to copy the format of one or a range of cells in the template sheet over to your chosen sheet (finally, you can delete this template sheet from the target spreadsheet). (The copyTo method only copies from one range to another within the same spreadsheet).

Something like this:

function transferFormatting() {
  var targetSs = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/1dLv8V5fKmmxRLi5maLIOgrxVGVaqGGOD7STHbEremQs/edit#gid=0');
  var targetSsDisplaySheet = targetSs.getSheets()[0];
  var sourceSs = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/14RxLXktm0wj-lSFrl5Fas9B-smMYhknFt3-dYQbwH3o/edit#gid=933388275');
  var templateSheet = sourceSs.getSheetByName('Template');
  var targetSsFormatTemplateSheet =  templateSheet.copyTo(targetSs);

  targetSsFormatTemplateSheet.getRange("A1").copyTo(targetSsDisplaySheet.getRange("B:D"), {formatOnly:true});
  targetSs.deleteSheet(targetSsFormatTemplateSheet);
}

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