CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetstriggersevent-handling
Pravin Kumar Raja
Pravin Kumar Raja

Reputation: 41

Handling onSelectionChange event causes further fires onSelectionChange event

I wanted to select an entire row when the user selects any cell / area of the google sheet. It works fine. Here is the code:

function onSelectionChange(e) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange();
  var rangelist = sheet.getActiveRangeList().getRanges();
  var newlist = [];
  var cell = sheet.getActiveCell();
  for (var i = 0; i < rangelist.length; i++) {
    var row = rangelist[i].getRow();
    var lines = rangelist[i].getLastRow() - row + 1;
    newlist.push(sheet.getRange(row, 1, lines, 11).getA1Notation());
  }
  sheet.setActiveRangeList(sheet.getRangeList(newlist));
  sheet.setCurrentCell(cell);
}

But the current cell gets shifted to the first column of the last selected row. I am afraid that by changing the selection, onSelectionChange is fired again.

In EXCEL, we could supress this by setting Application.EnableEvents as False before changing the selection and setting it back to True after the selection done. How can we solve this problem in Google Sheet?

Upvotes: 0

Views: 332

Answers (2)

Cooper
Cooper

Reputation: 64082

I played around with this for a while. I agree the last setActiveRangeList generates a new onSelectionChange. I'm not sure why it doesn't keep doing it for an endless loop but I'm glad it doesn't. It would be nice to have a way to inhibit the the onSelectionChange trigger if you wish. Perhaps you should request a new feature. I did it a little different but it's essentially the same thing that you have. I just tried to use the event object parameters where possible to try to minimize the number of functions. I used r.getNumRows() to get the rows in the range.

function onSelectionChange(e) {
  //e.source.toast('entry');
  //Logger.log(JSON.stringify(e));
  const sh=e.range.getSheet();
  //sh.getRange(1,1).setValue(JSON.stringify(e));//just a copy of e
  const l=sh.getActiveRangeList().getRanges();
  let al=[];
  l.forEach(function(r,i){
    al.push(sh.getRange(r.getRow(),1,r.getNumRows(),11).getA1Notation());
  });
  //sh.getRange(2,1).setValue(al.join(','));//a copy of the rangelist  
  sh.setActiveRangeList(sh.getRangeList(al));
}

Diego has a good point I didn't remember that from the restrictions so perhaps submitting it as an issue will fix the problem instead of requesting a feature.

Upvotes: 2

Diego
Diego

Reputation: 9571

This is a bug. From the trigger restrictions, "Script executions and API requests do not cause triggers to run."

I think it would be worth filing a bug report in the Issue Tracker.

Upvotes: 2

Related Questions