Why isn't getRange() working within the onChange event after unhiding rows?

Question

I want to run some script when a user unhides rows. The only way, that I can see, to detect when a user unhides rows is within the onChange event. The only way, that I can see, to try and determine what changed is through the changeType property. However, when a user unhides rows, the changeType is equal to OTHER. OTHER can mean all kinds of things. So I'd like to try and determine if rows were unhidden.

What I noticed is that when I unhide rows, those rows are now selected (have focus). So in theory, I should just be able to use getActiveRange() to return the rows that were just unhidden.

The problem is that when I use getActiveRange within the onChange event, it returns nothing. However, if I call getActiveRange after the onChange event function code has completed, then I get the correct information...even though I have not changed what I have currently selected in the sheet.

Why is getRange() returning nothing while within the onChange event?

The following onChange code will show a dialog box with the number of rows that is returned by the getActiveRange method when the user unhides rows. The code will add a menu item called Plan -> Test. Clicking Test will show a dialog box with the number of rows currently selected. To try this out, hide some rows, then unhide them. When you unhide them you will see a dialog box that says 0. Then go to Plan -> Test in the menu and you will see another dialog box that indicates the real number of rows that you just unhid.

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Plan')
      .addItem('Collapse Section', 'collapseSection')
      .addSeparator()
      .addItem('Test','testThis')
      .addToUi();
}

function testThis() {
  var thisSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var thisRange = thisSheet.getActiveRange();
  popMsg(thisRange.getNumRows());
}

function onChange(e){ 
  var activeSheet = e.source.getActiveSheet();
  if (e.changeType == "OTHER") {
    // some other event occurred, possible Unhiding Rows    
    if (activeSheet.getName() == "Portfolio") {
      // only execute this code if the OTHER event occurred while on the Portfolio tab
      var thisSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
      var thisRange = thisSheet.getActiveRange();
      popMsg(thisRange.getNumRows());
    }
  }
}

function popMsg (thisMsg) {
  var ui = SpreadsheetApp.getUi();  
  var thisMsg = ui.alert('Information',thisMsg,ui.ButtonSet.OK);
}

k4k4sh1 k4k4sh1 · Accepted Answer · 2017-05-01T21:51:14

It seems there's an open issue on retrieving the active range within onChange() event, check for any update at Issue Tracker.

Why isn't getRange() working within the onChange event after unhiding rows?

1 Answers