时间戳

首页 > 解决方案 > 加快从脚本到谷歌表格中分散单元格的更改

google-apps-script - 加快从脚本到谷歌表格中分散单元格的更改

问题描述

我有一张表，显示基于其他支持表的计算结果。更改是手动进行的，但“摘要”表只是公式。这些变化出现在分散的细胞中，大部分是不连续的。

我想突出显示在手动更改支持表后摘要表中哪些单元格发生了更改。为此，我使用了第二张摘要表，它作为主要摘要表的副本。

最后一个成分是在编辑后运行的脚本。它遍历汇总范围并将值与第二个副本进行比较。任何差异都会在主摘要中突出显示并复制回第二个摘要。

这个过程确实有效，但速度很慢，我认为是由于更新。伪代码：

var src = summary.getRange(...)
var dst = copy.getRange(...)

var src_cell;
var dst_cell;

src.setBackground('white'); // Bulk reset of changes

for (row = 1; row < src.getNumRows(); row++) {
    for (col = 1; col < src.getNumColumns(); col++) {
        src_cell = src.getCell(row, col);
        dst_cell = src.getCell(row, col);

        if (src_cell.getDisplayValue() != dst_cell.getDisplayValue()) {
            dst_cell.setValue(src_cell.getDisplayValue());
            src_cell.setBackground('gray');
        }
    }
}

我认为没有办法批量更新分散的范围，这似乎是一个简单的解决方案。

我正在寻找加快此过程的方法，无论是在脚本中还是通过使用其他策略。

标签： google-apps-scriptgoogle-sheets

解决方案

根据官方“最佳实践”，您应该批量读取关联的单元格数据，而不是重复读取和可能写入值。此语句确实假设设置值dst不会影响未来读取的值。

因此，最简单的更改是使用Range#getDisplayValuesonsrc和dst：

...
src.setBackground("white");
var srcValues = src.getDisplayValues();
var dstValues = dst.getDisplayValues();

srcValues.forEach(function (srcRow, r) {
  var dstRow = dstValues[r];
  srcRow.forEach(function (value, c) {
    if (value !== dstRow[c]) {
      dst.getCell(r + 1, c + 1).setValue(value);
      src.getCell(r + 1, c + 1).setBackground("gray");
    }
  });
});

另一个优化是使用RangeList类来批处理更改。要创建RangeList，您需要一个单元格/范围符号数组，它可以使用 R1C1 或 A1 样式的寻址。R1C1 是最容易计算的。

...
var dstChanges = [];
var srcChanges = [];
...

    if (value !== dstRow[c]) {
      dstChanges.push({row: r + 1, col: c + 1, newValue: value});
      srcChanges.push({row: r + 1, col: c + 1});
    }
...

if (srcChanges.length > 0) {
  var srcRow = src.getRow();
  var dstRow = dst.getRow();
  var srcCol = src.getColumn();
  var dstCol = dst.getColumn();
  copy.getRangeList(dstChanges.map(function (obj) {
    return "R" + (obj.row + dstRow) + "C" + (obj.col + dstCol);
  }).getRanges().forEach(function (rg, i) {
    rg.setValue(dstChanges[i].newValue);
  });
  summary.getRangeList(srcChanges.map(function (obj) {
    return "R" + (obj.row + srcRow) + "C" + (obj.col + srcCol);
  }).setBackground("gray");
}
...

其他参考

推荐阅读