Why does getActiveRange() not work and are there alternatives?

Question

When I try to invoke the sheet.getActiveRange() function it does not return a range that represents the currently selected cells of the sheet I am using. Instead it usually thinks that the active range is cell(1, 1) but that is not true. The function worked for a few minutes after not working but as since reverted to the same problem. This issue seems documented here1 but there was no answer so I'm bumping the issue again.

I've tried using activate() on getActiveRange() but it only changes my on-screen selection to cell(1, 1) which clearly illustrates the problem.

var rng = master.getActiveRange();
Logger.log(rng.getHeight());

Hypothetically the selected range could have a height of 5 or whatever but it always comes back as one because cell(1, 1) has a height of one. Basically it just always thinks the active range is cell(1, 1).

What's the rest of the code? Where is master defined? – Cooper Jul 25 '19 at 23:52 — Cooper, Jul 25 '19 at 23:52

score 1 · Answer 1 · answered Jul 26 '19 at 12:55

I was testing this and I kept having the same behavior you are, turns out if you're using SpreadsheetApp.getActiveSheet() after some time you need to "update" it. To do this you simply need to go to the sheet and open the script editor from tools -> script editor and when you run the code then it will be updated.

Additionally, I ran my test using the Selection class, which offers a good example on how to manage it.

For a quick test, you can use the following code:

function selection(){
  var selection = SpreadsheetApp.getActiveSheet().getSelection();
  var activeRange = selection.getActiveRange();
  Logger.log("Active Cell: " + selection.getCurrentCell().getA1Notation())
  Logger.log("Range: " + activeRange.activate().getA1Notation());
}

score 0 · Answer 2 · answered Jul 26 '19 at 00:20

This function will get the active ranges on the active sheet.

function runOne() {
  var rl=SpreadsheetApp.getActiveRangeList().getRanges();
  var ls='';
  for(var i=0;i<rl.length;i++) {
    if(i>0) {
      ls+=', ';
    }
    ls+=Utilities.formatString('\'%s\'!%s',rl[i].getSheet().getName(),rl[i].getA1Notation());
  }
  SpreadsheetApp.getUi().alert(ls);
}

Why does getActiveRange() not work and are there alternatives?

2 Answers2