Google App Script getActiveSheet returns leftmost sheet, not active sheet

Question

I guess this question is the most similar to my current question. But basically, I have a google sheets file that has several sheets along the bottom. I would like for the user to execute this script on whatever sheet they have open at the time.

And like the previous question said according to this documentation, the active sheet is the one that is being displayed in the spreadsheet UI. I assume this means if I have the google sheets file open in another tab, that the sheet currently selected and viewable by me is intended to be the active sheet.

However, the actual active sheet returned is always the leftmost sheet along the bottom tabs. I've confirmed it by switching the order of the sheets. So no matter what sheet I actually have open and visible in the UI, it always gets the leftmost sheet. Here is the line that gets it.

var Sheet = SpreadsheetApp.openById("redactedid").getActiveSheet()

Is this a known error with App Script? Or is the documentation wrong? If it is, is there a workaround for getting the currently open sheet in the UI? I don't want anyone to have to hardcode a sheetname in the code as the file is constantly changing and sheets are being added.

Langobardic

Answer 1

Notes:

To use getActiveSheet() or any active object, the calling

• Script must be bound to the Spreadsheet
• Script function should be invoked from menu or button or macro keyboard shortcuts and NOT from webapp or API( Script editor also works, but not preferred).
• Spreadsheet must be open/visible in the user interface/browser preferably in the active/currently selected window.
• All previous calls to get objects must have used active. For eg, To get active range, You should have first got active spreadsheet => active sheet => active range(the chain of active objects). If any of the calls is not to the active object(say if you used getSheetByName() instead of getActiveSheet() in the middle step), the final call is likely to default to the default object(First sheet A1).

Solution:

• Here, To get Spreadsheet, use SpreadsheetApp.getActive() instead of SpreadsheetApp.openById(), So that getActiveSheet() will be under the chain of active objects.

Snippet:

const sheet = SpreadsheetApp.getActive().getActiveSheet();

Mastin

can you explain "all previous calls must use active" please? – Coyote

Yes, the script is bound to the spreadsheet. I have 2 tabs open: one with the sheet I want visible and one with the script editor so I can run the code. I'm not sure what you mean by "all previous calls must use active" – Langobardic

@Langobardic Did you try my snippet? To get to speadsheet, use getActive() instead of openById() – Mastin

@Coyote Added a explanation. See if it helps. – Mastin

Question RE bullet 2: Using the script editor located at script.google.com/home/projects it always pulls the first sheet regardless of which sheet I'm actually looking at. Is there no way around that? Can't exactly debug the script from using a button on the sheet itself. – Keeler

@ZeekAran Is the script bound? All the scripts bound and unbound are located in your given location – Mastin

Answer 2

It's known that the getActiveSheet method returns the first sheet when used with an spreadsheet that is not the active spreadsheet.

The active spreadsheet is retrieved by using getActiveSpreadsheet. I only works on scripts bounded to an spreadsheet and in G Suite Editors add-on for spreadsheets.

In the the same way, getCurrentCell and getActiveRange returns A1 when corresponding sheet is not an active sheet (meaning it's not a sheet from an active spreadsheet)

Resources

• https://developers.google.com/apps-script/guides/sheets

Related

• Google Apps Script, select a sheet - Sets values in first sheet tab - not a specific sheet tab

Saretta

Answer 3

All the points in the accepted solution are all true, but sometimes your spreadsheet looses some connection to something on Google's back end or the cache gets corrupted, who knows. The fix is to close the all tabs for the worksheet, editor, triggers, executions -- whichever ones you have open for that spreadsheet, close them. Reopen the spreadsheet. Start again. It's fine now.

Finsteraarhorn