Being iterate through all pasted into cells

1
votes

I'm trying to call an external API for every cell that gets pasted into in my spreadsheet using either an onEdit on onChange trigger, neither seem to work completely when the selected cells are non-contiguous. No matter whether I use

SpreadsheetApp.getSelection().getActiveRangeList().getRanges()

or

SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getActiveRangeList().getRanges()

it only ever returns a single range that is the first-selected range. When I run the function directly, it works fine, but when triggered, it does not. I'm guessing this is either a bug or is just plain ol' impossible, but I'm hoping I'm just missing something.

Sheet:
sheet

Console log:

console log

function updateCategoryOnChange(e) {
  var selection = SpreadsheetApp.getSelection()
  var rangeList = selection.getActiveRangeList()

  //var ss=SpreadsheetApp.getActiveSpreadsheet()
  //var sheet=ss.getActiveSheet()
  //var rangeList=sheet.getActiveRangeList()
  

  var ranges=rangeList.getRanges()
  Logger.log("ranges length "+ranges.length);
  for (var i=0; i<ranges.length; i++) {
    Logger.log('x '+ranges[i].getA1Notation()+' '+ranges[i].getNumRows()+' rows, '+ranges[i].getNumColumns()+' columns')
  }
  return;
2
google-apps-scriptgoogle-sheets
Hi, was this answer useful? - Iamblichus

2 Answers

0
votes

This seems to be a bug.

Multiple ranges cannot currently be retrieved via onEdit trigger (or via onChange, for that matter).

getActiveRangeList(), in this case, will only retrieve the range corresponding to getCurrentCell() (see this answer for more details on the difference between getCurrentCell() and getActiveCell()).

This behaviour was reported in Issue Tracker:

To anyone affected by this, I'd suggest subscribing to this issue by starring it in order to keep track of this and to help prioritizing it.

0
votes

It works for me. Here's my logger output

Mar 19, 2021, 3:38:34 PM    Info    ranges length 1
Mar 19, 2021, 3:38:34 PM    Info    x G5:G12 8 rows, 1 columns

I ran it with an installable onEdit trigger

Here's a bigger paste:

Mar 19, 2021, 3:40:58 PM    Info    ranges length 1
Mar 19, 2021, 3:40:58 PM    Info    x K8:P18 11 rows, 6 columns

if you add the toast below you can tell when it runs:

function onMyEdit(e) {
  e.source.toast('entry');
  var selection = SpreadsheetApp.getSelection();
  var rangeList = selection.getActiveRangeList();
  var ranges=rangeList.getRanges()
  Logger.log("ranges length "+ranges.length);
  for (var i=0; i<ranges.length; i++) {
    Logger.log('x '+ranges[i].getA1Notation()+' '+ranges[i].getNumRows()+' rows, '+ranges[i].getNumColumns()+' columns')
  }
}