CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Miles Zoltak
Miles Zoltak

Reputation: 221

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

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).

Upvotes: 4

Views: 1265

Answers (2)

AMolina
AMolina

Reputation: 1379

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());
}

Upvotes: 2

Cooper
Cooper

Reputation: 64032

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);
}

Upvotes: 0

Related Questions