CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
XY6
XY6

Reputation: 3650

In AppScript, when multiple ranges are edited at once, How is rangeList obtained?

I wrote script for Google Sheets which is triggered by an onEdit event.

To ilustrate my problem, let's suppose that the contents of ranges A3:A5, C3:C5 and E1:E8 were selected and then deleted at once by pressing [delete]. In this situation, the onEdit(e){...} function would be called. The problem is that object e only has information about the first edited range: e.range.getA1Notation() == 'A3:A5'.

I tried obtaining the other edited ranges by calling SpreadsheetApp.getActiveSheet().getActiveRangeList().getRanges(); but only the first range is being returned.

Upvotes: 1

Views: 632

Answers (2)

Tanaike
Tanaike

Reputation: 201378

  • You want to retrieve all selected ranges when the OnEdit event trigger is fired.
    • In your case, when the cells of A3:A5, C3:C5 and E1:E8 are manually deleted, you want to retrieve the ranges of A3:A5, C3:C5 and E1:E8.

If my understanding is correct, how about this answer? Please think of this as just one of several answers.

Issue:

At first, I summarized the current situation as follows. As the sample case, it supposes the situation that the cells of A3:A5, C3:C5 and E1:E8 are deleted.

  • The event object of OnEdit event trigger cannot return the values of multiple ranges like A3:A5, C3:C5 and E1:E8.
  • When the OnEdit event trigger is fired, the script of by running the trigger returns only A3:A5 which is the first selected range.
    • The script is SpreadsheetApp.getActiveSpreadsheet().getSelection().getActiveRangeList().getRanges().
  • When the above script is run by the script editor, A3:A5, C3:C5 and E1:E8 are returned.

These were mentioned by XY6 and ziganotschka. I also think that this might be a bug.

From above situation, I guessed that when the OnEdit event trigger is fired, if the script is indirectly run by the trigger, SpreadsheetApp.getActiveSpreadsheet().getSelection().getActiveRangeList().getRanges() might return A3:A5, C3:C5 and E1:E8. By this, I thought the following workaround.

Workaround:

In this workaround, above my guess was achieved using a dialog. The flow of this workaround is as follows.

  1. Cells of A3:A5, C3:C5 and E1:E8 on the Spreadsheet are manually deleted.
  2. The installable OnEdit event trigger is fired and a script is run.
  3. The script opens a dialog.
    • The side bar can be also used for this situation.
  4. The script of SpreadsheetApp.getActiveSpreadsheet().getSelection().getActiveRangeList().getRanges() is run using Javascript at the dialog.

By this, running indirectly getSelection can be achieved. As the result, it was found that this workaround can retrieve all selected ranges when the OnEdit event trigger is fired.

Sample script:

The sample script for this workaround is as follows. When you use this script, please install myFunction() as the installable OnEdit event trigger.

function myFunction() {
  var script = "<script>google.script.run.withSuccessHandler((e)=>{google.script.host.close()}).getSelectedRanges()</script>";
  var html = HtmlService.createHtmlOutput(script).setWidth(100).setHeight(100);
  SpreadsheetApp.getUi().showModalDialog(html, "sample");
}

function getSelectedRanges() {
  var selection = SpreadsheetApp.getActiveSpreadsheet().getSelection().getActiveRangeList().getRanges();
  var ranges = selection.map(function(e) {return e.getA1Notation()});
  Logger.log(ranges) // ["A3:A5", "C3:C5", "E1:E8"]
}
  • When the cells of A3:A5, C3:C5 and E1:E8 on the active Spreadsheet are manually deleted, a dialog is opened by myFunction(), and Javascript of the dialog runs getSelectedRanges(). Then, the dialog is automatically closed.

References:

If I misunderstood your question and this was not the direction you want, I apologize.

Upvotes: 1

ziganotschka
ziganotschka

Reputation: 26796

The way to do it would be:

function onEdit(e) {
  var selection = e.source.getSelection(); 
  var ranges=selection.getActiveRangeList().getRanges();
  for (var i = 0; i < ranges.length; i++) {
  Logger.log(ranges[i].getA1Notation());
  }
}

However, only the first range will be returned if you run the function onEdit() instead of manually with var selection = SpreadsheetApp.getActiveSheet().getSelection();.

This seems to be a bug and has already been filed on Issuetracker:

https://issuetracker.google.com/issues/115931946

I suggest you to give it a "star" to increase visibility.

Upvotes: 2

Related Questions