search

OfficeDev / office-js

A repo and NPM package for Office.js, corresponding to a copy of what gets published to the official "evergreen" Office.js CDN, at https://appsforoffice.microsoft.com/lib/1/hosted/office.js.
https://learn.microsoft.com/javascript/api/overview
Other
657 stars 93 forks source link

Sharing Excel Workbooks dependent on Add-ins will likely cause irrecoverable corruption #2610

Open david-ps opened 2 years ago

david-ps commented 2 years ago

Issue Summary

This is a critical Issue when sharing Excel Workbooks dependent on Add-ins.

If an Excel Workbook dependent on a Add-in, is shared with a user who is not prompted to install the Add-in, and/or the Add-in does not Auto-open, and that user makes changes to the workbook data, the workbook may be irrecoverably corrupted.

Your Environment

Expected behavior

Sharing an Excel workbook that is dependent on a Task Pane Add-in with a user who does not have the Task Pane Add-in installed, should always Auto-open the Task Pane container so the user is requested to Add/Trust the add-in to continue, or be made aware that the workbook is dependent on the Add-in being installed, and changes may corrupt the workbook.

Current behavior

Add-in does not Auto-open for users who do not have the Add-in installed unless the document is tagged using Open XML.

You can Tag the document on Client side using the Excel API but this does not address the expected behavior. 

Currently, there is no way to achieve this without tagging the document using Open XML as there is no [OoxmlCoercion](https://docs.microsoft.com/en-us/javascript/api/requirement-sets/common/office-add-in-requirement-sets?view=common-js-preview&irgwc=1&OCID=AID2200057_aff_7815_1243925&tduid=(ir__vldi9j3htokf6numxvgw0cx1fe2xvzvakanikzm900)(7815)(1243925)(je6NUbpObpQ-1NhT.kkKrquj7YwXAsY7QA)()&irclickid=_vldi9j3htokf6numxvgw0cx1fe2xvzvakanikzm900#ooxmlcoercion) available in Excel (only Word).

However, webextension and taskpane parts cannot be updated using this method while the document is open, (ie. using JavaScript within a Task Pane app) which means you cannot share an Excel Workbook dependent on an Excel Add-in without ensuring that the recipient has the Add-in installed.

Steps to reproduce

  1. Insert an Add-in in a Workbook that calculations in that workbook rely on
  2. Share the workbook with a user who does not have the Add-in installed
  3. Workbook opens without advising the user there is a dependency with an Add-in associated with workbook
  4. User makes changes to the workbook that are dependent on the Add-in running
  5. Original user opens the workbook with Add-in running
  6. Workbook / Add-in mismatch > corruption

Link to live example(s)

No live example

Additional details

I have posted this to the Tech Ideas Forum but see it as an issue that should be posted as a bug. Suggestion added to Tech Ideas Forum

Some suggestions that may be options?

  1. Extend [OoxmlCoercion](https://docs.microsoft.com/en-us/javascript/api/requirement-sets/common/office-add-in-requirement-sets?view=common-js-preview&irgwc=1&OCID=AID2200057_aff_7815_1243925&tduid=(ir__vldi9j3htokf6numxvgw0cx1fe2xvzvakanikzm900)(7815)(1243925)(je6NUbpObpQ-1NhT.kkKrquj7YwXAsY7QA)()&irclickid=_vldi9j3htokf6numxvgw0cx1fe2xvzvakanikzm900#ooxmlcoercion) to Excel

  2. Extend Office.Settings to cover the requirements

  3. Extend Office.AutoShowTaskpaneWithDocument to cover requirements

Context

Issue raised originally with @Rick-Kirkham regarding Use Open XML to Auto Open and Excel Task Pane Add-in with additional comments by @davidchesnut

Useful logs

No logs

JHJ-MS commented 2 years ago

Thanks for reporting this issue. May I know does it occurs on Excel Online or Excel Desktop?

david-ps commented 2 years ago

The issue was identified on Desktop but logically it must be same Online.

JHJ-MS commented 2 years ago

Hi, @david-ps, sorry for the late response.

Just to confirm, have you tried setting the visibility to "1"? This should help you achieve the behavior you want. "If visibility is set to "1", the task pane opens automatically the first time the document is opened. The user is prompted to trust the add-in, and when trust is granted, the add-in opens. Thereafter, the add-in task pane opens automatically when the file is opened. However, when visibility is set to "1", you can't use Office.js to enable users to turn on or turn off the autoopen feature."

If the above way doesn't work. You can manually change the file extension from “.xlsx” to ".zip", then open the xl->webextensions->taskpanes.xml, set the visibility from "0" to "1"， and then change the file extension from ".zip" to ".xlsx", it should work as what you expect. image

david-ps commented 2 years ago

@JHJ-MS It seems you do not have the correct understanding of the issue and so your latest comment is not relevant.

Here is the posted issue restated to hopefully make it clear:

We need to distribute an Add-in with a workbook where the workbook data is dependent on the Add-in. Here is the relevant documentation: "If you also require the add-in to be distributed with the document you need to set the visibility property to 1; this can only be done using OpenXML"

ISSUE: You cannot set the Visibility property to 1 in the taskpane part using OpenXML while the document is open, (ie. using JavaScript within a Task Pane app) which means you cannot share an Excel Workbook dependent on an Excel Add-in without ensuring that the recipient has the Add-in installed because the Add-in will not be autoopened and request the user to trust and install it. Changes to the workbook data, when dependent on the Add-in, will then become corrupt, unknown to the user.

Can you please confirm this issue. Thanks.

david-ps commented 2 years ago

@massonj any ETA on the bug fix? This is a critical issue.

JHJ-MS commented 2 years ago

Hi @david-ps, we want to confirm with you what is your need.

  1. Are you requesting to let the OpenXML API can set the Visibility property to 1 using OpenXML not only while the document is closed but also while the document is open? Or do you want us to provide a JS API to implement changing the Visibility property's value? Or do you want us to provide a JS API to generate the webextension and task pane file?

  2. Are you troubled by the following problem as an add-in owner, you can not ask every add-in user who wants to share the worksheet dependent on your add-in to set manually set the Visibility property to 1 so that you want to have a JS API to implement it?

  3. I'm curious about what the " Insert an Add-in in a Workbook that calculations in that workbook rely on" do you mean in your case. I can guess two kinds of things. One is you use Custom Functions, the other is you used the setting APIs. Is that correct? It will help us repro it.

  4. Does corruption means it crashes?

It is really helpful if you can provide us with a demo video. Thanks.

davidchesnut commented 2 years ago

@david-ps I just realized that text from the article "this can only be done using OpenXML" is no longer true (at least not entirely). It is true that you can't do this using OpenXML, but you can do it using the Office JS API. This was a change introduced with the shared runtime.

  1. Configure your add-in to use the shared runtime.
  2. Add code to make your add-in run as soon as the document is opened.
  3. Add code to show the task pane (this would go in your Office initialization or onReady code).

My understanding is that these steps will allow you to send the spreadsheet to someone else and when they open it they will be prompted to install the add-in. But is that not working for you? @JHJ-MS am I incorrect in my understanding that this will prompt another user to install the add-in when they open the spreadsheet?

david-ps commented 2 years ago

@davidchesnut My understanding from the documentation, and our testing, is that the code you mention will only Auto-open the Add-in if it is already installed. It does not prompt you to trust and install the Add-in if it is not there.

The change that must be made according to the OpenXML documentation, is setting the taskpane part > Visibility = 1

This is not changed by the Office JS API, and I believe can only be done using OpenXML.

@JHJ-MS please confirm.

david-ps commented 2 years ago

Hi @JHJ-MS , please see answers below:

  1. Are you requesting to let the OpenXML API can set the Visibility property to 1 using OpenXML not only while the document is closed but also while the document is open? Or do you want us to provide a JS API to implement changing the Visibility property's value? Or do you want us to provide a JS API to generate the webextension and task pane file?

Answer>> We believe the best solution is for the JS API to change the Visibility property's value in the taskpane part while the document is open instead of the OpenXML approach

  1. Are you troubled by the following problem as an add-in owner, you can not ask every add-in user who wants to share the worksheet dependent on your add-in to set manually set the Visibility property to 1 so that you want to have a JS API to implement it?

Answer>> That is correct. User cannot effectively share Excel files that rely on the Add-in to others who do not have the Add-in already installed.

  1. I'm curious about what the " Insert an Add-in in a Workbook that calculations in that workbook rely on" do you mean in your case. I can guess two kinds of things. One is you use Custom Functions, the other is you used the setting APIs. Is that correct? It will help us repro it.

Answer>> Yes. Custom Functions are a good example. If the Custom Functions are included with a Task Pane Add-in and used in formulas, and they are not installed, those calculations fail. Another dependency is where the Add-in provides a function and/or updates the Custom XML that the workbook relies on.

  1. Does corruption mean it crashes?

Answer>> Not in the use case we have. Corruption means that calculations and/or relationships between the Workbook and the Add-in are not maintained and the workbook will contain errors.

david-ps commented 1 year ago

@JHJ-MS Please advise resolution status and if these answers are sufficient to proceed?

JHJ-MS commented 1 year ago

Hi @david-ps , my first reply ("You can manually change the file extension from “.xlsx” to ".zip", then open the xl->webextensions->taskpanes.xml, set the visibility from "0" to "1"， and then change the file extension from ".zip" to ".xlsx") will be a good workaround for you. One could do that when the workbook is closed. After one has done that for a workbook, he can then open it and share it with others. And the Add-in will request other users to trust and install it (other users do not need to do anything else).

If you are requesting a JS API to change the Visibility property's value, I'd recommend going to Microsoft 365 Developer Platform Ideas Forum and request a new feature. Our PM team will consider it at the Quarterly planning. Thanks!

david-ps commented 1 year ago

Hi @JHJ-MS I saw your workaround suggestion 49 days ago and replied 21 days ago to advise that this manual approach does not address our use cases and is not practical in any circumstance that I can think of.

Your advice to put an idea on the "Developer Forum" to solve this critical issue is very disappointing, particularly after waiting so long.

I have heard that all Development Ideas submissions are moving to be captured in Github. @davidchesnut Can you please confirm status on this?

I do not believe this issue should be addressed as an "idea" but as a major Bug with critical implications for development and distribution of Office Add-ins. I do not understand why it would be pushed back to a developer and not submitted and prioritised directly for resolution by a Product Manager?

davidchesnut commented 1 year ago

Hi @david-ps,

Sorry for the frustration on this. The only workaround I can think of in this case is perhaps the add-in can insert a message into the spreadsheet to notify the user to remember to install the add-in. I'll keep an eye on this issue from a samples perspective because I believe it also affects our Open in Excel scenario. Would be nice if at the conclusion of the scenario, the document is opened, and prompts the user to install the add-in.

We're still looking at moving suggestions/ideas to GitHub issues, but the plans have not been finalized. Hopefully that will happen soon. In the meantime, you can create a suggestion at https://aka.ms/m365dev-suggestions. If we change the location, we'll update that aka link.

Thanks, David

david-ps commented 1 year ago

@davidchesnut Way beyond frustrating...

Thanks for your workaround suggestion but that would mean a permanent message would have to be maintained in a prominent place and not removed by any shared user. A message like this cannot be added at runtime on Close because there is no close event.

I believe it also affects our Open in Excel scenario. Would be nice if at the conclusion of the scenario, the document is opened, and prompts the user to install the add-in.

Yes, this does affect Open in Excel and means any Task Pane Add-in that the workbook relies on eg. has Custom Functions, would not be installed and the workbook calculations would likely fail and show errors. The user is unlikely to know why.

I already posted the "suggestion" that this critical issue be resolved to the MS365 Dev Forum on 1 June over two months ago. There have been no comments, upvotes or responses from Microsoft.

NB. I was unable to mention @davidchesnut and @JHJ-MS in the suggestion comments as it appears neither of you are members of the Developer Forum?

I believe this situation should be made known to all unaware Office Add-in developers and so I will post a question for the August Office Add-ins Community Call that I hope will be addressed by the Product Manager.

Thanks David

Wenjun-Gong commented 1 year ago

Hi @david-ps , thanks for submitting the request on the M365 dev ideas forum. I have seen it. We will keep monitoring whether there are more partners would vote for this one.

Also I'd like to let you know that we are looking at this issue to understand your scenario better and seeking for possible workaround, or any possible new feature we should provide to mitigate this problem. I have a few questions and appreciate you can share more:

Another workaround which might help to mitigate this issue - the tenant admin can central deploy the add-in to their tenant users thus they don't need to install by themselves. Then when these users open the file, the add-in can be launched automatically.

Hope this helps and appreciate your patient on this.

JHJ-MS commented 1 year ago

Hi @david-ps, very sorry for letting you wait so long. After a further internal discussion, we'd found another solution that may be a quick workaround to unblock you:

Hope this workaround solution could unblock you. Thanks.

Other docs for your reference:

  1. Show or hide the task pane of your Office Add-in - Office Add-ins | Microsoft Docs
  2. Run code in your Office Add-in when the document opens - Office Add-ins | Microsoft Docs
david-ps commented 1 year ago

@JHJ-MS Thanks for your suggested work around.

Unfortunately, it does not address the use case: User can share a document with an add-in, with another user who does not have the add-in installed

You can see here in Microsoft documentation [Automatically open a task pane with a document]...(https://docs.microsoft.com/en-us/office/dev/add-ins/develop/automatically-open-a-task-pane-with-a-document)

If you want to distribute your add-in with the document, so that users are prompted to install it, you must set the visibility property to 1. You can only do this via Open XML. a requirement if you want to.

Users cannot share a document that uses, and is dependent on an add-in, to a user who does not have the add-in installed.

This is NOT a feature request or an idea that should need votes! It is a core, undocumented failing of the Office Add-in JS API.

@Wenjun-Gong a reply to your question has already been posted in this thread with a suggested solution.

"do you expect the add-in to control this behavior, or the document owner who added the add-in to the document to control this? or either one is OK?"

REQUIRED: If an Add-in is present in a document, and it is shared with a user who does not have the add-in installed, that user should be prompted to install it.

@davidchesnut I suggest you update all related documentation so developers are aware of this critical distribution and sharing issue.

david-ps commented 1 year ago

@davidchesnut after watching the Aug Office Add-ins Community call regarding the Open in Excel functionality I understand how any add-in are automatically installed. However, if the initial user shares that document with another user that does not have the add-in installed then I believe the add-in will not be present and any integration will fail or is Visibility = 1 set?

JHJ-MS commented 1 year ago

@David-ps, thanks very much for your patient for answering our questions!

We totally understand your requirement. But really sorry that currently it cannot be totally fulfilled as the scenario gap here. The autoopen feature was target for users who want to keep the add-in always open but don’t want the add-in expose to the consumers of the file. So the Tag the document on Client side solution isn’t suitable for your case here.

We are actively working on how to address your requirement and at same time also try to understand the impact of the gap. Based on the current design, could you help to see if below solution can partially migrate your problem?

For your question below, the answer is Yes.

@davidchesnut after watching the Aug Office Add-ins Community call regarding the Open in Excel functionality I understand how any add-in are automatically installed. However, if the initial user shares that document with another user that does not have the add-in installed then I believe the add-in will not be present and any integration will fail or is Visibility = 1 set?

If have any other concern or more suggestions, pleases feel free to let us know, or do you mind contacting you by mail for more further and detail discussion about your scenarios and requirements? Appreciated for any suggestions to help us improve!

david-ps commented 1 year ago

@JHJ-MS thanks for the update and confirmation of the scenario gap: Users always prompted to install included Add-in

Responses to your instructions:

  1. Modify your add-in to support shared runtime and use shared runtime** >> A: Yes, we do this already, does not address scenario

  2. Deploy the add-in via centralized deployment in a tenant >> A: Yes, we do this already, does not address scenario

I made these suggestions initially (see first post):

  1. Extend [OoxmlCoercion](https://docs.microsoft.com/en-us/javascript/api/requirement-sets/common/office-add-in-requirement-sets?view=common-js-preview&irgwc=1&OCID=AID2200057_aff_7815_1243925&tduid=(ir__vldi9j3htokf6numxvgw0cx1fe2xvzvakanikzm900)(7815)(1243925)(je6NUbpObpQ-1NhT.kkKrquj7YwXAsY7QA)()&irclickid=_vldi9j3htokf6numxvgw0cx1fe2xvzvakanikzm900#ooxmlcoercion) to Excel

  2. Extend Office.Settings to cover the requirements

  3. Extend Office.AutoShowTaskpaneWithDocument to cover requirements

However it seems that any of these suggestions would require an API upgrade and not be viable solution until the associated Requirement Set reaches all Current Channels (desktop) in 6-9 months time?

@JHJ-MS thanks for advising the Open in Excel function sets Visibility = 1. Please confirm that you mean this is using Open XML to tag the document so that the webextension and taskpane parts are added to the workbook and visibility=1 in the taskpane part? Can you please explain how this is achieved?

Note: Setting visibility to "1" is a good choice when the add-in and the template or content of the document are so closely integrated that the user would not opt out of the autoopen feature. [...] If you want to distribute your add-in with the document, so that users are prompted to install it, you must set the visibility property to 1. You can only do this via Open XML.

david-ps commented 1 year ago

@JHJ-MS with regard to my suggestion to Extend [OoxmlCoercion](https://docs.microsoft.com/en-us/javascript/api/requirement-sets/common/office-add-in-requirement-sets?view=common-js-preview&irgwc=1&OCID=AID2200057_aff_7815_1243925&tduid=(ir__vldi9j3htokf6numxvgw0cx1fe2xvzvakanikzm900)(7815)(1243925)(je6NUbpObpQ-1NhT.kkKrquj7YwXAsY7QA)()&irclickid=_vldi9j3htokf6numxvgw0cx1fe2xvzvakanikzm900#ooxmlcoercion) to Excel please note this other related issue about Ooxml Performance in Word

JHJ-MS commented 1 year ago

Hi @david-ps , would you mind provide your email address for us through this link for more further discussion?

david-ps commented 1 year ago

@JHJ-MS Unable to provide email with your link "You don't have permission to view or respond to this form"

JHJ-MS commented 1 year ago

Hi @david-ps , I have just changed the form's config, would you please try again?

david-ps commented 1 year ago

@JHJ-MS I have submitted my email but have had no contact. Please advise.

JHJ-MS commented 1 year ago

Hi @david-ps , we have just booked a meeting with you, look forward to talking with you, thanks.

JHJ-MS commented 1 year ago

Thanks @david-ps for joining the meeting. This issue is now added to our backlog(tracked as 6625256 internally). We will update you here if it has any progress.

david-ps commented 1 year ago

@JHJ-MS Thank you for taking time with your team to meet and clarify that the critical use case for our Excel Task Pane Add-in (and solution/business model) is not supported by the current Excel API.

Our use case is not supported because: "If you want to distribute your add-in with the document, so that users are prompted to install it, you must set the visibility property to 1. You can only do this via Open XML.".

There is no way to change this setting in the current, open Excel Workbook and there is no work around that addresses our core use case.

We have spent many months developing an Excel Add-in but missed this vital shortcoming of the current Excel API because we wrongly assumed that users would always be prompted to install/trust a Task Pane Add-in that had been included with a workbook and shared with others (like Content Pane add-ins).

We have been ready for release to Appsource and delayed since we submitted this issue three months ago on 3 June 2022, but have ceased development and suspended our release plans until this issue is addressed.

I appreciate this requirement is now "on the backlog" but we urgently need a timeframe for when you estimate this work will be complete.

Additionally, we would like to understand the planned solution you have documented in the backlog.

If it is extending [OoxmlCoercion](https://docs.microsoft.com/en-us/javascript/api/requirement-sets/common/office-add-in-requirement-sets?view=common-js-preview&irgwc=1&OCID=AID2200057_aff_7815_1243925&tduid=(ir__vldi9j3htokf6numxvgw0cx1fe2xvzvakanikzm900)(7815)(1243925)(je6NUbpObpQ-1NhT.kkKrquj7YwXAsY7QA)()&irclickid=_vldi9j3htokf6numxvgw0cx1fe2xvzvakanikzm900#ooxmlcoercion) to Excel then it will take many months (6+) to Preview and then for the associated Requirement Set to arrive in the Current Channel and this timing is critical for us.

Alternatively, we discussed not knowing why Visibility = 0 is the default for workbooks and whether it is a possible change that default?

We would really appreciate details about the above points and urge this work to be escalated and addressed asap (you mentioned an estimated effort of one week?).

Thanks for your support.

JHJ-MS commented 1 year ago

Involve our PM @yaweizhu-henson in this thread. He would be a better one to follow up on.

david-ps commented 1 year ago

@yaweizhu-henson can you please review this issue and provide us with an ETA for resolution, thanks

david-ps commented 1 year ago

@JHJ-MS can you please provide an update. I have received no response from @yaweizhu-henson

Wenjun-Gong commented 1 year ago

@david-ps, we are looking into the details of this issue including how many cases are running into this scenario today and the possible solution for this. We take this as a new feature ask. We don't have any ETA to share at this moment. Thanks for your patient.

david-ps commented 1 year ago

@Wenjun-Gong thanks for getting back to me. You mention "We take this as a new feature ask. " Can you please explain how you arrived at the assesement of this as a new feature request when the critical failure described will occur for any add-in that shares data with the workbook and occurs during a typical workflow of sharing of a workbook with other users?

Wenjun-Gong commented 1 year ago

Hi David,

This is a scenario we haven't supported for a long time, instead of a regression or bug. That's why we take this as a new feature. We did not receive many feedback for this scenario in the past. We will take further look at the impact and design next. And this has been tracked as a feature backlog internally (6625256). Appreciate for your feedback and long discussion with us. Well received. We will share on the monthly community call once we have clarify for the roadmap. Thanks again.

david-ps commented 1 year ago

@Wenjun-Gong Thank you, I really appreciate you taking this issue seriously, it has stalled our product development effort. I look forward to seeing you update on this in the community call.