How are non-adjacent ranges handled by the onEdit() simple trigger in Google Scripts for Sheets?

1
votes

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.

Thanks

1
google-apps-scriptgoogle-sheetstriggers

1 Answers

1
votes

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.

Example from 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());