google-apps-script - 如果列值超过阈值，则将 G-Sheet 列值替换为单元格中“n”个先前值的平均值

Question

当列中的值高于我的阈值范围时，我想替换列中的值，列中“n”个先前值的平均值具有一致的值。（使用 G-Sheets 本身中的任何公式）

这是我的 G-Sheet 使用 API 动态生成值。

从 g-sheet 图像的上述链接中，有一些值以黑色突出显示。这些是超过我的值“55”阈值的值。

我试过条件格式。但是，在那种情况下，我无法替换这些值。我所需要的只是 G 表本身的解决方案。

标签： google-apps-scriptgoogle-sheets

Answer 1

您可以使用以下 GAS 代码：

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Fix values')
  .addItem('Run fixer', 'fixValues')
  .addToUi();
}

function fixValues() {
  var n = 5;
  var threshold = 55;
  var values = SpreadsheetApp.getActiveRange().getValues();
  for (var i=0; i<values.length; i++) {
    if (values[i][0] > threshold && i==0)
      values[i][0] = threshold;
    if (values[i][0] > threshold && i>0) {
      var startAvgIdx = (i-n >= 0) ? i-n : 0;
      var sum = 0;
      for (var j=startAvgIdx; j<i; ++j) sum += values[j][0];
      var avg = sum / (i-startAvgIdx);
      values[i][0] = avg;
    }
  }
  SpreadsheetApp.getActiveRange().setValues(values);
}

这将在您的表格文档顶部创建一个菜单，其中包含一个运行该功能的按钮。该函数将在所选范围内运行。您可以在下面看到一个结果示例：

只剩下一个需要考虑的问题：如果初始值已经大于阈值怎么办？是否应该保持原样，是否应该将其设置为阈值...？在我的代码中，我创建了它以在发生这种情况时设置为阈值，但可以随意更改它（if (values[i][0] > threshold && i==0)行后的代码）。