Google Apps脚本 - 表格 - 条件格式 - X =突出显示-Java 学习之路

我有一张超过100,000个单元格（很快就会有300,000个单元格）用作甘特图 . 每个单元格都有一个IF公式，如果相应列的 Headers （日期）在行的开始日期和结束日期之间，则返回字母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'); }
}

3 回答

1
这是使用批处理函数Range.setBackgrounds()的方法 . 此函数采用2D数组值作为参数，允许您在单个API调用中设置范围的所有单元格背景 .

另请注意，函数Range.getValues()返回一个2D数组值 . 要检查每个单独的单元格值，您需要循环遍历数组 .

由于您将2D数组作为输入和输出处理，因此构建背景值输出数组的逻辑会反映您需要用来检查当前单元格值的逻辑 . 因此，您可以在循环遍历单元格值时构建背景值的2D数组 .
```
function setCellBackgrounds() {
  // The name of the sheet to process.
  var sheetName = "Sheet1";
  // The range of cells to inspect.
  var range = "A1:Z100";

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var range = sheet.getRange(range);
  var values = range.getValues();
  var colors = [];
  for (var x = 0; x < values.length; x++) {
    colors[x] = [];
    for (var y = 0; y < values[x].length; y++) {
       if (values[x][y] == 'X') {
         colors[x][y] = '#999999';
       } else {
         colors[x][y] = '#ffffff';
       }
    }
  }
  range.setBackgrounds(colors);
}
```
使用批处理功能，而不是重复调用非批处理版本，是a documented Apps Script best practice .
回复于 2024-04-26T04:06:55+08:00
1
- cellValues 是一个二维数组;必须检查每个单元格是否为 X . 在循环中运行它以获取被测元素的（x，y） .
- 既不能使用 ss.range 也不能使用 cellValue 来设置背景 . 它必须是 ss.getRange(x, y).setBackground... 使用（x，y）坐标来指定 X ed单元格 .
没有测试过，但这应该工作：
```
function formatting() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet 1");
  var range = ss.getRange("A1:A100");
  for (var x = 0; x < range.length; x++) {
    for (var y = 0; x < range[0].length; y++) {
      if (cellValue == 'X') {
        ss.getRange(x, y).setBackgroundColor('#000000');
      } else {
        ss.getRange(x, y).setBackgroundColor('#ffffff');
      }
    }
  }
}
```
虽然这可能是您正在寻找的，但人们担心速度 . 另一种方法是在需要时添加/删除规则 . 可以使用ConditionalFormatRule以编程方式完成，如果它太多而无法手动重新创建 .
回复于 2024-04-26T04:06:55+08:00
1
如上所述here，您没有正确引用检查 . 然而，使用 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);
}
```
如果只需要考虑刚刚修改过的单元格，我建议使用"simple trigger."
回复于 2024-04-26T04:06:55+08:00

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

3 回答

相关问题