Google Apps Script function getActiveSheet() does not return active sheet

Question

The Google Apps Script Developers' Guide's definition of an "active sheet":

The active sheet in a spreadsheet is the sheet that is being displayed in the spreadsheet UI.

If I have three sheets, named Mary, Moe, and Joe, and leave the Mary sheet on display (hence, it's the active sheet) and run this simple script:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  Logger.log(sheet.getName());

  ss.setActiveSheet(ss.getSheets()[2]);
  Logger.log(ss.getActiveSheet().getName());
}

I expect the output to say

[18-10-26 18:41:45:724 EDT] Mary
[18-10-26 18:41:45:730 EDT] Joe

But, in fact, it says:

[18-10-26 18:49:14:752 EDT] Moe
[18-10-26 18:49:14:755 EDT] Joe

And if I run it a second time without touching the sheets, I would expect the output to say

[18-10-26 18:50:51:930 EDT] Joe
[18-10-26 18:50:51:933 EDT] Joe

But it actually says the same thing as before

[18-10-26 18:50:51:930 EDT] Moe
[18-10-26 18:50:51:933 EDT] Joe

In other words, setting the active sheet does indeed change the sheet that's on display in the Spreadsheet UI, but it doesn't seem to internally register the new sheet as the active sheet. GAS is always recognizing Moe as the active sheet, even after setting the active sheet to a different one.

I would appreciate an explanation and a way to fix this problem. Basically, I simply need getActiveSheet() and getActiveRange() (which has similar issues) to give me the sheet that is currently visible in the Spreadsheet UI, exactly as defined in the Developer Guide.

Edit: I found a solution to this problem. I am quoting myself from the comments.

Hi everyone, thank you all for your input. I just discovered that getActiveSheet() does indeed work the way I was hoping it would, but only if you run the function through a custom menu UI built using SpreadsheetApp.getUi().createMenu(...), which needs an onOpen() trigger (thanks to @I'-'I for this tip). Running the function from the script editor causes the problems that inspired my original post.

However, while I am glad I found a solution, it feels more like a workaround leaving the core problem unresolved. I feel as though getActiveSheet() should act as defined in the Developer Guide in both cases, in the Menu UI and the script editor. It doesn't make sense that it works in one and it doesn't in the other.

can you share the spreadsheet with ful access to try the coe please? — JSmith
I actually created a copy because the sheet I used for my post contains confidential work-related information... should have thought ahead of that one. Anyway, this new sheet behaves in the same way, except I get Joe-Joe every time now, regardless of which sheet is on display docs.google.com/spreadsheets/d/… — Manuel
can you tell what web-browser you are using please. It works for me. — JSmith
Hi JSmith, Thanks for the feedback. I'm using Mozilla Firefox 63.0 (Quantum) for 64-bit Windows 10 OS — Manuel

Cooper Cooper · Accepted Answer · 2018-10-27T00:49:30

Display All Sheets for 10 seconds

This script will cycle through all of the sheets in a spreadsheet activating them one at a time and displaying them for approximately 10 second.

function cycleThroughAllSheets() {
  var ss=SpreadsheetApp.getActive();
  var shA=ss.getSheets();
  var start=new Date();
  for(var i=0;i<shA.length;i++){
    shA[i].activate();
    SpreadsheetApp.flush();
    ss.toast('Active Sheet is ' + shA[i].getName());
    do{
      //nothing
    }while(new Date().valueOf()-start.valueOf()<10000);
    start=new Date();
  }
  ss.toast('End of Script.')
}

Google Apps Script function getActiveSheet() does not return active sheet

1 Answers