Google App Script getActiveSheet returns leftmost sheet, not active sheet

0
votes

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.

2
google-apps-scriptgoogle-sheets

2 Answers

3
votes

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();
2
votes

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

Related