时间戳

首页 > 解决方案 > Google Apps 脚本 - 表格 - 条件格式 - X = 突出显示

google-apps-script - Google Apps 脚本 - 表格 - 条件格式 - X = 突出显示

问题描述

我有一张包含超过 100,000 个单元格（很快将超过 300,000 个单元格）的表格用作甘特图。每个单元格都有一个 IF 公式，如果相应列的标题（日期）在行的开始日期和结束日期之间，则返回字母 X。

但是，显着减慢工作表的是它背后的条件格式。条件格式表示如果单元格的值为 X，则将单元格的背景颜色和字体颜色更改为绿色。如果没有 X，则背景颜色应为白色。据我了解，由于条件格式会在您对工作表进行任何编辑时重新计算，因此性能非常差。所以我的想法是删除条件格式并将其添加为脚本，并带有一个菜单按钮，我可以在我想要它运行的任何时候单击它，而不是每次我对工作表进行编辑时运行条件格式。

这是我使用脚本的地方，它不起作用。我已经尝试了几十种变体，但找不到有效的方法 - 有时我让它运行没有错误，有时会出现错误。我的上下文在某处有问题吗？

function formatting() {

  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet 1");
  var range = ss.getRange("A1:A100");
  var cellValue = range.getValues();
  if (cellValue === 'X') {
    ss.range.setBackgroundColor('#000000'); }
  else {
    cellValue.setBackgroundColor('#ffffff'); }
}

标签： google-apps-scriptgoogle-sheetsgs-conditional-formatting

解决方案

如此处所述，您没有正确引用支票。然而，setBackground即使是 100 个细胞，也不能真正使用，更不用说 1000 或 300,000 个。您会发现需要将批处理方法Range#setBackgrounds()与您希望应用的背景颜色的“2D”数组一起使用。

您可以通过使用有关甘特图电子表格的构造和操作的特定于应用程序的详细信息来进一步减少对大量 API 的需求，以减少您修改的范围。也许 X 只能在刚刚编辑过的单元格中出现或消失，或者它们只能从左到右或从上到下等填充。

此函数假定最坏的情况 - 每次调用它时，您都需要重新计算背景颜色。

function greenify() {
  const sheet = SpreadsheetApp.getActive().getSheetByName("Gantt");
  const HAS_X = "green", NO_X = null;
  const dr = sheet.getDataRange();
  const colors = dr.getBackgrounds();
  const VALUES = dr.getValues();

  // Inspect the value array and modify the corresponding index in colors.
  for (var r = 0, rows = VALUES.length; r < rows; ++r)
    for (var c = 0, cols = VALUES[0].length; c < cols; ++c)
      colors[r][c] = (VALUES[r][c] === "X") ? HAS_X : NO_X;

  // Write the output.
  dr.setBackgrounds(colors);
}

如果只需要考虑刚刚修改的单元格，我建议使用“简单触发器”。

推荐阅读