Google表格脚本中的onEdit（）简单触发器如何处理非相邻范围？ [英] How are non-adjacent ranges handled by the onEdit() simple trigger in Google Scripts for Sheets?

问题描述

我正在尝试获取简单的onEdit（）触发器，以对Google表格中所有已编辑的单元格执行操作。问题是，如果您编辑不相邻单元格的集合，则事件对象只会选择第一个选定的范围。

I am trying to get the onEdit() simple trigger to do an action on all edited cells in Google Sheets. The problem is that if you edit a collection of non adjacent cells, the event object only picks up the first range selected.

例如，我有以下代码。如果我在工作表中输入公式或对单元格进行赋值，脚本将自动将背景色更改为红色。

For example, I have the following code. If I enter a formula or value a cell in my sheet, the script automatically changes the background color to red.

function onEdit(e) {
  var range = e.range;
  range.setBackgroundRGB(255, 0, 0); //set color to red 
}

如果我选择许多范围（或单元格），并使用彼此不相邻的鼠标一次更改所有值（例如，如果我从某个地方复制一个值，然后一次将其粘贴到许多相邻的单元格中）。脚本不会更改其所有背景色，而只会更改所选第一个范围的颜色。

The problem arises if I select many ranges (or cells) with the mouse that are not adjacent to each other and change all their values at once (for example if I copy a value from somewhere, and paste it into many adjacent cells at once). Instead of the script changing all of their background colors, it only changes the color of the first range selected.

谢谢

推荐答案

我们可以从范围中获取活动表事件对象的属性，而不是选择对象的属性，但是我们可以使用getSelection和getRangeList。第一个返回一个对象，该对象可用于获取选定的范围引用，该对象通过getRangeList完成。

We could get the active sheet from the range property of the event object but not the selection, but we could use getSelection and getRangeList. The first returns a object that could be used to get the selected ranges references which is done through getRangeList.

来自 https ：//developers.google.com/apps-script/reference/spreadsheet/selection

var activeSheet = SpreadsheetApp.getActiveSheet();
var rangeList = activeSheet.getRangeList(['A1:B4', 'D1:E4']);
rangeList.activate();

var selection = activeSheet.getSelection();
// Current Cell: D1
Logger.log('Current Cell: ' + selection.getCurrentCell().getA1Notation());
// Active Range: D1:E4
Logger.log('Active Range: ' + selection.getActiveRange().getA1Notation());
// Active Ranges: A1:B4, D1:E4
var ranges =  selection.getActiveRangeList().getRanges();
for (var i = 0; i < ranges.length; i++) {
  Logger.log('Active Ranges: ' + ranges[i].getA1Notation());
}
Logger.log('Active Sheet: ' + selection.getActiveSheet().getName());

这篇关于Google表格脚本中的onEdit（）简单触发器如何处理非相邻范围？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！