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
641 stars 92 forks source link

Excel CustomFunctions AddIn not working / crashes - Microsoft 365 Admin Center deployment #4386

Open hol721 opened 3 weeks ago

hol721 commented 3 weeks ago

Hello everyone,

whe currently have major problems getting the excel addin with custom functions running. We already invested a lot of development effort into developing a excel adding using office-js technology. While developing on my mashine everything (except a few minor problems) went well.

When we decided to deploy the app to some test users within our company using Microsoft 365 admin center (https://admin.microsoft.com/Adminportal/Home#/Settings/AddIns) all other users are ending up with the following error: image When we turn on addin runtime logging we only see the error

"... Unexpected  CustomFunctions  [Installation] [Addin] [End] [Failure] Solution={GUID}".

No further details.

When trying to use the addin online - excel web (https://www.office.com/) the addin loads and the functions are working. However all parameter values are not showing up. (Maybe another topic). i.e. we have a excel custom function called log(message: string) the parameter message is not being displayed on excel web.

When creaing a addin without custom functions (taskpane addin). Everything works without an error.

We are currently at the point where we have no more ideas. Hopefully anyone can confirm this issue or have some useful hints.

All we tried without any success

Your Environment

Expected behavior

When deploying office 365 through admin center the excel custom functions should work.

Current behavior

When creating a office 365 addin with custom functions the addin crashes with the error message "failed to install functions".

Steps to reproduce

  1. Create a new excel addin with custom functions using yeoman generator (https://github.com/yeoman/generator) or just try out one of my examples (https://github.com/hol721/office-js-examples).
  2. Publish the build result to an public available webserver
  3. Distribute the AddIn to users using the manifest.xml via https://admin.microsoft.com/Adminportal/Home#/Settings/AddIns
  4. Experience the addin custom functions load error

Link to live example(s)

Feel free to use our already deployed minimal test app

  1. https://www.seven2one.de/analystofficeaddin-sharedruntime/manifest.xml

Useful logs

nshelia-da commented 3 weeks ago

We have the same problem and similar logs. Also using Admin Center deployment

davidchesnut commented 3 weeks ago

Hi @hol721, sorry you are running into this issue. Thanks for the detailed repo steps. @adrianwu8516 can you please help @hol721 with this issue? Thanks!

MiaofeiWang commented 3 weeks ago

Hi @hol721 , the error is likely to happen when parsing the functions' metadata file. But I am not able to reproduce the error with the manifest you provided https://www.seven2one.de/analystofficeaddin-sharedruntime/manifest.xml whose functions' metadata file is quite simple.

From the log, you seem to have some function named as "ANALYST.LOG". Could you share the metadata JSON file which has this function?

hol721 commented 3 weeks ago

@MiaofeiWang Thx for your fast reply.

We experience the issue with all office addins using custom functions. We are currently developing an addin called analyst. You can get the manifest from https://www.seven2one.de/analystofficeaddin/manifest.xml

But as mentioned this happens to all custom functions addins. This is the reason i shared stipped down samples on my repo. Created by the steps mentioned above.

If it is helpful i can provide a new log. But it won't tell you more. There is no further detail. Only showing: {Timestamp} Unexpected CustomFunctions [Installation] [Addin] [End] [Failure] Solution={AddInId}, Version={Version} And at the same time excel desktop is showing the error from above.

Best regards

MiaofeiWang commented 3 weeks ago

@hol721 , thanks for the clarification. And I see you have made many tries like cleaning cache (thanks for providing that information). However, I am not able to reproduce it on my test tenant. And the add-in runtime log only provides limited information.

image

So, to help figure out the root cause, could you please help try with more steps?

  1. Install any add-in with custom functions from store (instead of admin deployment), like "GPT for Excel Word" (https://appsource.microsoft.com/en-us/product/office/WA200005502?tab=Overview).
  2. Distribute some other add-in with the manifests via https://admin.microsoft.com/Adminportal/Home#/Settings/AddIns, for example manifest of "GPT for Excel Word".
hol721 commented 3 weeks ago

@MiaofeiWang Thx. We well give the the AddIn GPT for Excel Word a try.

hol721 commented 3 weeks ago

@MiaofeiWang First update. Sorry for late response. Having a lot of meetings today. We deployed the GPT for Excel and Word using the Admin Center by uploading your provided manifest and end up with the same behavior.

Excel Error Message: image

Runtime logs:

23.04.2024 10:20:15 Verbose CustomFunctions [Metadata] [Parsing] [Begin]
23.04.2024 10:20:15 Verbose CustomFunctions [Metadata] [Parsing] [Begin] Function
23.04.2024 10:20:15 Medium CustomFunctions [Metadata] [Parsing] [End] [Success] Function=GPT
23.04.2024 10:20:15 Verbose CustomFunctions [Metadata] [Parsing] [Begin] Function
23.04.2024 10:20:15 Medium CustomFunctions [Metadata] [Parsing] [End] [Success] Function=GPT_LIST
23.04.2024 10:20:15 Verbose CustomFunctions [Metadata] [Parsing] [Begin] Function
23.04.2024 10:20:15 Medium CustomFunctions [Metadata] [Parsing] [End] [Success] Function=GPT_HLIST
23.04.2024 10:20:15 Verbose CustomFunctions [Metadata] [Parsing] [Begin] Function
23.04.2024 10:20:15 Medium CustomFunctions [Metadata] [Parsing] [End] [Success] Function=GPT_CLASSIFY
23.04.2024 10:20:15 Verbose CustomFunctions [Metadata] [Parsing] [Begin] Function
23.04.2024 10:20:15 Medium CustomFunctions [Metadata] [Parsing] [End] [Success] Function=GPT_TRANSLATE
23.04.2024 10:20:15 Verbose CustomFunctions [Metadata] [Parsing] [Begin] Function
23.04.2024 10:20:15 Medium CustomFunctions [Metadata] [Parsing] [End] [Success] Function=GPT_EXTRACT
23.04.2024 10:20:15 Verbose CustomFunctions [Metadata] [Parsing] [Begin] Function
23.04.2024 10:20:15 Medium CustomFunctions [Metadata] [Parsing] [End] [Success] Function=GPT_TAG
23.04.2024 10:20:15 Verbose CustomFunctions [Metadata] [Parsing] [Begin] Function
23.04.2024 10:20:15 Medium CustomFunctions [Metadata] [Parsing] [End] [Success] Function=GPT_CONVERT
23.04.2024 10:20:15 Verbose CustomFunctions [Metadata] [Parsing] [Begin] Function
23.04.2024 10:20:15 Medium CustomFunctions [Metadata] [Parsing] [End] [Success] Function=GPT_FORMAT
23.04.2024 10:20:15 Verbose CustomFunctions [Metadata] [Parsing] [Begin] Function
23.04.2024 10:20:15 Medium CustomFunctions [Metadata] [Parsing] [End] [Success] Function=GPT_SUMMARIZE
23.04.2024 10:20:15 Verbose CustomFunctions [Metadata] [Parsing] [Begin] Function
23.04.2024 10:20:15 Medium CustomFunctions [Metadata] [Parsing] [End] [Success] Function=GPT_TABLE
23.04.2024 10:20:15 Verbose CustomFunctions [Metadata] [Parsing] [Begin] Function
23.04.2024 10:20:15 Medium CustomFunctions [Metadata] [Parsing] [End] [Success] Function=GPT_FILL
23.04.2024 10:20:15 Verbose CustomFunctions [Metadata] [Parsing] [Begin] Function
23.04.2024 10:20:15 Medium CustomFunctions [Metadata] [Parsing] [End] [Success] Function=GPT_SPLIT
23.04.2024 10:20:15 Verbose CustomFunctions [Metadata] [Parsing] [Begin] Function
23.04.2024 10:20:15 Medium CustomFunctions [Metadata] [Parsing] [End] [Success] Function=GPT_HSPLIT
23.04.2024 10:20:15 Verbose CustomFunctions [Metadata] [Parsing] [Begin] Function
23.04.2024 10:20:15 Medium CustomFunctions [Metadata] [Parsing] [End] [Success] Function=GPT_EDIT
23.04.2024 10:20:15 Verbose CustomFunctions [Metadata] [Parsing] [Begin] Function
23.04.2024 10:20:15 Medium CustomFunctions [Metadata] [Parsing] [End] [Success] Function=GPT_MATCH
23.04.2024 10:20:15 Verbose CustomFunctions [Metadata] [Parsing] [Begin] Function
23.04.2024 10:20:15 Medium CustomFunctions [Metadata] [Parsing] [End] [Success] Function=GPT_VISION
23.04.2024 10:20:15 Verbose CustomFunctions [Metadata] [Parsing] [Begin] Function
23.04.2024 10:20:15 Medium CustomFunctions [Metadata] [Parsing] [End] [Success] Function=GPT_WEB
23.04.2024 10:20:15 Medium CustomFunctions [Metadata] [Parsing] [End] [Success]
23.04.2024 10:20:15 Verbose CustomFunctions [Installation] [Addin] [Begin] Solution=cec2c794-cfc7-4347-a54b-1ced872b6768, Version=1.0.0.11
23.04.2024 10:20:15 Unexpected CustomFunctions [Installation] [Addin] [End] [Failure] Solution=cec2c794-cfc7-4347-a54b-1ced872b6768, Version=1.0.0.11

Deploying the AddIn throuh the store leads to the same issue:

23.04.2024 12:58:56 Verbose CustomFunctions [Metadata] [Parsing] [Begin]
23.04.2024 12:58:56 Verbose CustomFunctions [Metadata] [Parsing] [Begin] Function
23.04.2024 12:58:56 Medium CustomFunctions [Metadata] [Parsing] [End] [Success] Function=GPT
23.04.2024 12:58:56 Verbose CustomFunctions [Metadata] [Parsing] [Begin] Function
23.04.2024 12:58:56 Medium CustomFunctions [Metadata] [Parsing] [End] [Success] Function=GPT_LIST
23.04.2024 12:58:56 Verbose CustomFunctions [Metadata] [Parsing] [Begin] Function
23.04.2024 12:58:56 Medium CustomFunctions [Metadata] [Parsing] [End] [Success] Function=GPT_HLIST
23.04.2024 12:58:56 Verbose CustomFunctions [Metadata] [Parsing] [Begin] Function
23.04.2024 12:58:56 Medium CustomFunctions [Metadata] [Parsing] [End] [Success] Function=GPT_CLASSIFY
23.04.2024 12:58:56 Verbose CustomFunctions [Metadata] [Parsing] [Begin] Function
23.04.2024 12:58:56 Medium CustomFunctions [Metadata] [Parsing] [End] [Success] Function=GPT_TRANSLATE
23.04.2024 12:58:56 Verbose CustomFunctions [Metadata] [Parsing] [Begin] Function
23.04.2024 12:58:56 Medium CustomFunctions [Metadata] [Parsing] [End] [Success] Function=GPT_EXTRACT
23.04.2024 12:58:56 Verbose CustomFunctions [Metadata] [Parsing] [Begin] Function
23.04.2024 12:58:56 Medium CustomFunctions [Metadata] [Parsing] [End] [Success] Function=GPT_TAG
23.04.2024 12:58:56 Verbose CustomFunctions [Metadata] [Parsing] [Begin] Function
23.04.2024 12:58:56 Medium CustomFunctions [Metadata] [Parsing] [End] [Success] Function=GPT_CONVERT
23.04.2024 12:58:56 Verbose CustomFunctions [Metadata] [Parsing] [Begin] Function
23.04.2024 12:58:56 Medium CustomFunctions [Metadata] [Parsing] [End] [Success] Function=GPT_FORMAT
23.04.2024 12:58:56 Verbose CustomFunctions [Metadata] [Parsing] [Begin] Function
23.04.2024 12:58:56 Medium CustomFunctions [Metadata] [Parsing] [End] [Success] Function=GPT_SUMMARIZE
23.04.2024 12:58:56 Verbose CustomFunctions [Metadata] [Parsing] [Begin] Function
23.04.2024 12:58:56 Medium CustomFunctions [Metadata] [Parsing] [End] [Success] Function=GPT_TABLE
23.04.2024 12:58:56 Verbose CustomFunctions [Metadata] [Parsing] [Begin] Function
23.04.2024 12:58:56 Medium CustomFunctions [Metadata] [Parsing] [End] [Success] Function=GPT_FILL
23.04.2024 12:58:56 Verbose CustomFunctions [Metadata] [Parsing] [Begin] Function
23.04.2024 12:58:56 Medium CustomFunctions [Metadata] [Parsing] [End] [Success] Function=GPT_SPLIT
23.04.2024 12:58:56 Verbose CustomFunctions [Metadata] [Parsing] [Begin] Function
23.04.2024 12:58:56 Medium CustomFunctions [Metadata] [Parsing] [End] [Success] Function=GPT_HSPLIT
23.04.2024 12:58:56 Verbose CustomFunctions [Metadata] [Parsing] [Begin] Function
23.04.2024 12:58:56 Medium CustomFunctions [Metadata] [Parsing] [End] [Success] Function=GPT_EDIT
23.04.2024 12:58:56 Verbose CustomFunctions [Metadata] [Parsing] [Begin] Function
23.04.2024 12:58:56 Medium CustomFunctions [Metadata] [Parsing] [End] [Success] Function=GPT_MATCH
23.04.2024 12:58:56 Verbose CustomFunctions [Metadata] [Parsing] [Begin] Function
23.04.2024 12:58:56 Medium CustomFunctions [Metadata] [Parsing] [End] [Success] Function=GPT_VISION
23.04.2024 12:58:56 Verbose CustomFunctions [Metadata] [Parsing] [Begin] Function
23.04.2024 12:58:56 Medium CustomFunctions [Metadata] [Parsing] [End] [Success] Function=GPT_WEB
23.04.2024 12:58:56 Medium CustomFunctions [Metadata] [Parsing] [End] [Success]
23.04.2024 12:58:56 Verbose CustomFunctions [Installation] [Addin] [Begin] Solution=cec2c794-cfc7-4347-a54b-1ced872b6768, Version=1.0.0.11
23.04.2024 12:58:56 Unexpected CustomFunctions [Installation] [Addin] [End] [Failure] Solution=cec2c794-cfc7-4347-a54b-1ced872b6768, Version=1.0.0.11

MiaofeiWang commented 3 weeks ago

@hol721 Thanks for the information. It seems we still need more to address root cause. Could you or your test users install "GPT for Excel Word" from in-app store after clearing cache (https://learn.microsoft.com/en-us/office/dev/add-ins/testing/clear-cache)? Steps: "Home" tab on ribbon -> click "Add-ins" -> click right-botton button "More Add-ins" -> tab "Store" -> search "GPT for Excel Word" -> "Add" -> "Continue".

Also, it may help if you can provide the session id (for custom function installation failure) which could be found by clicking "File" -> "Account" -> "About Excel".

hol721 commented 3 weeks ago

@MiaofeiWang i will have a talk with out IT department. This can take a while. By our security policy this feature is turned off. Maybe you or someone else can make more investigations in the meanwhile.

We asked a different company to make some tests. They experience a similar issue with addins using custom functions. Maybe @nshelia-da can help us too? I read he also have problems.

nshelia-da commented 3 weeks ago

Clearing cache from those locations fixes the issue. Not a real solution unfortunately.

~/Library/Containers/Microsoft Excel/Data/Library/Application Support/Microsoft/Office/16.0/Wef/  
~/Library/Containers/Microsoft Excel/Data/Library/Caches/
jblainmks commented 3 weeks ago

We are experiencing a similar issue with our deployments. We support 2 environments internally, and only have the issue on one environment. The two environments are: citrix virtual desktop with excel version 2308 Build 16731.20636, and users' local machines which have version 2404 Build 17328.20282.

There are other differences between the two environments, such as citrix effectively reinstalling the add-in from scratch each time (my understanding only, not 100% sure how citrix interacts with add-ins). We're trying to upgrade our Excel version in citrix however that is in the control of our IT department.

Our failures consistently happen the first time the user opens excel and attempts to load our add in, then if the user closes excel and restarts they will have maybe a ~50% chance of it working. If they reset the add in cache and restart at that point they have a higher chance of it working, but still not guaranteed.

Is it likely caused by how citrix sessions are handled? Or is it related to the excel versions? We don't experience any issues at all when it's run locally, so my feeling is that it's related to the virtual machines

Example session ids: 57FB1543-2CD1-4471-9AF4-C419A7DCC44E 0A2C0686-31CA-45E9-9A0E-BE37CDF32483

MiaofeiWang commented 3 weeks ago

Thanks @nshelia-da . But @hol721 mentioned that they have tried to clean the cache which doesn't work.

@jblainmks I would lean to not related to the Excel version, as the feature of admin deployment has been there for quite a few years. So, I agree this is related to configuration of virtual machine. Unluckily we don't have citrix VM for in-house test. Does your case share the same error and add-in runtime log (metadata parsing success and add-in installation failed) with hol721?

hol721 commented 3 weeks ago

@MiaofeiWang we finally made some additional testing.

What we experienced?

We included more users for our test and experience the following while using the local addin store with the GPT App.

The runtime log looks the same as my previous posts:

.....
23.04.2024 12:58:56 Verbose CustomFunctions [Metadata] [Parsing] [Begin] Function
23.04.2024 12:58:56 Medium CustomFunctions [Metadata] [Parsing] [End] [Success] Function=GPT_WEB
23.04.2024 12:58:56 Medium CustomFunctions [Metadata] [Parsing] [End] [Success]
23.04.2024 12:58:56 Verbose CustomFunctions [Installation] [Addin] [Begin] Solution=cec2c794-cfc7-4347-a54b-1ced872b6768, Version=1.0.0.11
23.04.2024 12:58:56 Unexpected CustomFunctions [Installation] [Addin] [End] [Failure] Solution=cec2c794-cfc7-4347-a54b-1ced872b6768, Version=1.0.0.11

Sometimes the taskbar is not hiding the message and the status bar and is stuck on "We are starting the addin-runtime, please wait a moment". While the addin is working. No other addins are installed at this time. Sometimes happens on the very rare mashines/users where the addin starts working after a few restarts.

image

And idea how we can come to an solution?

hol721 commented 3 weeks ago

@hol721 Thanks for the information. It seems we still need more to address root cause. Could you or your test users install "GPT for Excel Word" from in-app store after clearing cache (https://learn.microsoft.com/en-us/office/dev/add-ins/testing/clear-cache)? Steps: "Home" tab on ribbon -> click "Add-ins" -> click right-botton button "More Add-ins" -> tab "Store" -> search "GPT for Excel Word" -> "Add" -> "Continue".

Also, it may help if you can provide the session id (for custom function installation failure) which could be found by clicking "File" -> "Account" -> "About Excel".

You asked for a session id. This is a session where GPT installed via local AddIn Store crashed: Sitzungs-ID: FE3923AA-DA95-4B52-BE0D-5C9D6709273E

jblainmks commented 2 weeks ago

Thanks @nshelia-da . But @hol721 mentioned that they have tried to clean the cache which doesn't work.

@jblainmks I would lean to not related to the Excel version, as the feature of admin deployment has been there for quite a few years. So, I agree this is related to configuration of virtual machine. Unluckily we don't have citrix VM for in-house test. Does your case share the same error and add-in runtime log (metadata parsing success and add-in installation failed) with hol721?

Thanks for the response. Yes we have the same runtime log, however I am now fairly confident that it is a cache error related to how this kind of VDI is configured, as clearing the add ins cache does fix the issue. I think it is related to the base citrix image containing an invalid/old/corrupt cache, which each users session uses as the starting point. I will be working with our internal IT department to validate this theory.

hol721 commented 2 weeks ago

@jblainmks for us this is not related to citrix stuff or similar. We have a common Windows 11 Installation on Notebooks and Office 365. Still having the same problems. No Solution so far :(

MiaofeiWang commented 2 weeks ago

@hol721 Sorry to hear that. This is the first case I have ever met that the clients cannot install any add-in with custom functions. The logs can't tell enough information. We are trying to figure out how to debug the issue.

hol721 commented 1 week ago

We found out that another excel (xll) addin (in our case written in c# using excel-dna) is causing custom function load error of all office-js addins. @jblainmks maybe this is helpful for you as well. The xll addin itself is working fine. Same for our new office-js addin. Only when both of them are installed it is causing the error.

MiaofeiWang commented 1 week ago

@hol721 Great to see you got unblocked. Is this xll add-in public one, or private for internal usage? If public, would you mind to share the name of it so we can investigate why it leads to all custom functions failing to register. Thank you.

4tti commented 1 week ago

oh no, hope it is not related to COM/VBA add-in UDF registration similar to https://github.com/OfficeDev/office-js/issues/2051

hol721 commented 1 week ago

We digged deeper into the root cause of this problem and currently ending at the point when using excel-dna https://excel-dna.net/ When you extend the ribbons which is using COM technology you will end up with the custom function load error of modern office-js addins.

I created a minimal example which should break your office-js excel custom functions: https://github.com/hol721/office-js-examples/tree/main/ExcelDnaTest

When you start the addin from the debugger everythin is fine. The problem is showing when you decide to install the .xll file via excel -> addins

Steps to reproduce:

We are currently using Excel 2402 using the monthly enterprise channel When swiching to version Excel 2404 it looks a little bit "better". At some point it's working. No long time experience. We testet only one specific machine with Excel 2404.

It would help if other ppl. can check that and give a feedback @MiaofeiWang

MiaofeiWang commented 1 week ago

@hol721 Thanks for the detailed steps. I built myself .xll file and followed the steps (even cleaned add-in cache before the last step). However, my JS custom functions still work well on both 2402 and 2404...

image

I have the tab of the XLL add-in:

image
hol721 commented 4 days ago

@MiaofeiWang Thx for your reply. Can u pls check if this is also the case for this version? image