getActiveWorksheet error

[christian-sap]

Provide required information needed to triage your issue

The API call context.workbook.worksheets.getActiveWorksheet() throws an error depending on the active sheet type.

Your Environment

• Platform [PC desktop, Mac, iOS, Office on the web]: PC desktop
• Host [Excel, Word, PowerPoint, etc.]: Excel
• Office version number: All latest Excel channel versions
• Operating System: Win + Mac

Expected behavior

context.workbook.worksheets.getActiveWorksheet() should always return the active worksheet

Current behavior

Depending on the type of the active sheet calling context.workbook.worksheets.getActiveWorksheet() throws an error.

Steps to reproduce

1. Create a new workbook
2. Right-click on "Sheet1" -> "Insert..." -> Chart
3. Call context.workbook.worksheets.getActiveWorksheet() by running the attached ScriptLab script
4. A RichApi.Error error is thrown (see below)

See attached ScriptLab script. getActiveWorksheet.yaml.txt

See attached screen capture. https://user-images.githubusercontent.com/79985386/155088025-4f5d6daa-3b38-4a08-a31e-4d0999531a82.mp4

Provide additional details

The call to context.workbook.worksheets.getActiveWorksheet() throws an error for the following two sheet types "Chart" "MS Excel 4.0 Macro"

Link to live example(s)

Useful logs

{ "stack": "RichApi.Error: The current selection is invalid for this operation.\n at new n (https://appsforoffice.microsoft.com/lib/1/hosted/excel-win32-16.01.js:26:238287)\n at n.i.processRequestExecutorResponseMessage (https://appsforoffice.microsoft.com/lib/1/hosted/excel-win32-16.01.js:26:302031)\n at https://appsforoffice.microsoft.com/lib/1/hosted/excel-win32-16.01.js:26:300136", "message": "The current selection is invalid for this operation.", "name": "RichApi.Error", "code": "InvalidSelection", "traceMessages": [], "innerError": null, "debugInfo": { "code": "InvalidSelection", "message": "The current selection is invalid for this operation.", "errorLocation": "WorksheetCollection.getActiveWorksheet", "statement": "var activeWorksheet = worksheets.getActiveWorksheet();", "surroundingStatements": [ "var workbook = context.workbook;", "var worksheets = workbook.worksheets;", "// >>>>>", "var activeWorksheet = worksheets.getActiveWorksheet();", "// <<<<<" ], "fullStatements": [ "Please enable config.extendedErrorLogging to see full statements." ] }, "httpStatusCode": 400 }

[RuizhiSunMS]

Thank @KelBowen for reporting. After some investigations, we colleagues found that it was by design. Chart sheet and macro sheet are not worksheet. Most APIs don't support non worksheet type.

[christian-sap]

Hi @RuizhiSunMS , and how can we find out that the currently activated sheet is not a worksheet? For example a property "type" on Excel.Worksheet would be an idea that tells us which sheet type it is. Or maybe return null in case of an unsupported sheet. Currently we call getActiveWorksheet and get an exception which is not the best behavior.

[christian-sap]

@RuizhiSunMS beside that my event handler on context.workbook.worksheets.onActivated is not called when a chart sheet is activated. This should be fixed as well.

[christian-sap]

Hi @RuizhiSunMS , do you have answers to my questions? Is it possible to fix the issue that we do not even get a "sheet activate" event when the user activates such a sheet?

[RuizhiSunMS]

Hello @christian-sap . https://docs.microsoft.com/en-us/javascript/api/excel/excel.worksheet?view=excel-js-preview As above doc said, we regard a grid of cells as a 'worksheet' which is the only target of our apis about 'sheet'. In other words, one can not achieve the goal of using an event handler of worksheet to deal with non-worksheets. That is by-designed feature for now.

[christian-sap]

Hi @RuizhiSunMS , I understand that this is the designed behavior but then I have to say that the design is not really good because it is incomplete. I do not expect that all API functions are working on those non-worksheet sheets but at least we need 1 (!!!) API function that tells us that the user has activated an unsupported sheet. At the moment the Excel user can use chart sheets and our add-in will throw errors because there is no API available that can be used to prevent this error.

[RuizhiSunMS]

Hello, @christian-sap , With some investigation, one way might work is to use try-catch and getActiveWorksheet ( that api would throw error if active sheet is not worksheet). This is just a advice programming-wise. Sorry for saying that your requirement is not supported. But I'd like to suggest you to forum on which we track feature request. Here is the link: Microsoft 365 Developer Platform Ideas Forum](https://aka.ms/m365dev-suggestions. And we will take them into consideration when going through. Hope these could help. Thanks.

[ghost]

This issue has been automatically marked as stale because it is marked as needing author feedback but has not had any activity for 4 days. It will be closed if no further activity occurs within 3 days of this comment. Thank you for your interest in Office Add-ins!

[ghost]

This issue has been closed due to inactivity. Please comment if you still need assistance and we'll re-open the issue.