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
685 stars 95 forks source link

Excel Add-in dialog box not sending data to Excel Desktop #3106

Open Suyash-Muley opened 1 year ago

Suyash-Muley commented 1 year ago

Provide required information needed to triage your issue

Your Environment

Expected behavior

For my custom functions, I use a dialog box using displayDialogAsync method to open the dialog box and display a list of data that which user can select, and that selected value is then sent to Excel using messageParent. where It takes the value for example - "Hello" is the value selected and sent from the Dialog box and displayed in the cell where Excel Custom Functions was invoked.

Current behavior

Steps to reproduce

Only reproducible for customers

  1. Launch the Add-in
  2. invoke a custom function that will open a dialog box
  3. select the data from the dialog box
  4. Selected value should be displayed in the cell where the custom function was invoked

Provide additional details

Context

The Add-in uses Taskpane as well as Custom Functions in Excel. Taskpane has some functionality independent of custom functions.

Useful logs

penglongzhaochina commented 1 year ago

Hi, @Suyash-Muley this week is the chinese new year. Our engineer will take a look at 1.28. Feel free to tell me if this issue is urgent. thanks.

Suyash-Muley commented 1 year ago

@penglongzhaochina It's okay if they have a look by 1.28. I'll look for the comments here.

pkkj commented 1 year ago

Hi @Suyash-Muley.

Would you like to provide more detail about how you implement this step: "Selected value should be displayed in the cell where the custom function was invoked".

The error you provide in the logs might be an internal false alarm in Office JavaScript library.

Thanks.

Suyash-Muley commented 1 year ago

Hi @pkkj So what I do is open a dialog box using displayDialogAsync which would open HTML page to display the necessary list from which the user can select the value to be displayed in the cell where the custom function was and using messageParent from the HTML I send the selected value back to Excel to display the value

Below is how I send the data back to Excel on the HTML page where the user will select the value

// select.value is the value that which user will select from a list in the Dialog box
        Office.initialize = function () {
          Office.context.ui.messageParent(select.value);
        };

Now this works perfectly fine on Excel on the Web but on the Excel Desktop application I cannot receive the data sent using messageParent which I could display in the cell and in the console of the Dialog page I get the error that I have mentioned above.

Also Adding how I'm opening the Dialog box and displaying the selected value in the cell -

var dialog;
//invoc is the cell address where the custom function is called
// arrLocalStorage is some data sent through query parameters which are displayed on the dialog box
    Office.context.ui.displayDialogAsync(
        `https://azure-storage-container-url.net/dialog.html?arrToSend=${arrLocalStorage}`, 
        { height: 50, width: 50, displayInIframe: true },
        function (asyncResult) {
          dialog = asyncResult.value;
          dialog.addEventHandler(Office.EventType.DialogMessageReceived, (arg) => {
            dialog.close();
            let msgFromDialog = arg.message;
            Excel.run(async (context) => {
              var ws = context.workbook.worksheets.getActiveWorksheet();
              var range = ws.getRange(invoc);  //invoc is cell address where custom function is called
              range.values = [[msgFromDialog]];
              await context.sync();
            });
          });
        }
      );

Thanks

pkkj commented 1 year ago

Based on my understanding, you should use the return value from custom function to set the cell where it's invoked, instead of using Rich API.

Here is my example for your scenario and I verify that it's working. I use async directly since it's supported in modern browser.

async function showdialogCF() {
    var url = "******************";
    var message = "";
    var promise = new Promise((resolve, reject) => {
        Office.context.ui.displayDialogAsync(url, { height: 50, width: 50, promptBeforeOpen: false }, function(asyncResult) {
            var _dialog;
            _dialog = asyncResult.value;
            _dialog.addEventHandler(Microsoft.Office.WebExtension.EventType.DialogMessageReceived, function(arg) {
                message = arg.message;
                _dialog.close();
                resolve();
            });
        });

    });
    await promise;
    return message;
}
Suyash-Muley commented 1 year ago

Still, I cannot get the value that I select from the dialog box to the Excel Desktop application. This works perfectly fine on the Web but not on the Desktop where I cannot get the value that is sent via the messageParent from the dialog box.

pkkj commented 1 year ago

I have a test add-in here Manifest. You can try to play with it if you wish, after you review its code. It does the same scenario as you mentioned.

This manifest contains a custom function called MultipleTabTest.SHOWDIALOGCF(). Once it's called, it prompts a dialog and you can input something in the text box and then click SendMessageToParent to send the data back to the cell.

Suyash-Muley commented 1 year ago

Does this work fine on Excel Desktop? Also, I sideloaded the manifest and tried it works on Web. But my main issue is will this work on the Desktop application.

Also, I looked at #1710 where the full name has been shortened to Office.EventType.DialogMessageReceived.

Do I need to add any additional references in the project?

pkkj commented 1 year ago

Yes. It works in Excel Desktop. I tested it in different machines.

Suyash-Muley commented 1 year ago

Okay, I'll refer to that and will try to get this working.

Suyash-Muley commented 1 year ago

I did try using the above manifest as the reference but again works fine on the web It seems that the value which is being sent from the dialog box is not watched on the Desktop application.

Internal false alarm which you mentioned in one of the above comment that's the only thing which I can see on Excel Desktop application

Suyash-Muley commented 1 year ago

Hi @pkkj is it possible to see what may be the issue here?

celluj34 commented 1 year ago

I am having a similar issue, although I do not have any problems sending data back to the taskpane. I am also using Office.onReady instead of Office.initialize.

Suyash-Muley commented 1 year ago

I'm using validation API as dialogue box was not working on Excel Desktop application. I don't have to send the data to taskpane but a cell.

rahulnethagani commented 1 year ago

Facing same issue @Suyash-Muley any resolution on this?

Suyash-Muley commented 1 year ago

No resolution yet @rahulnethagani I ended up using validation API and not dialog box for my requirement.

davecra commented 1 year ago

I am having the same exact issue on in Excel desktop. The problem does not happen with the same code in Outlook, but in Excel it fails on the desktop. Works in online. So here is what I am doing: 1) I load the dialog, perform some work and message parent. I get this message just fine. 2) I then perform a location.replace to load my next page needed in my process 3) in the replaced dialog I am loading the Office runtime and it is working, I am able to access Office.ready() in the redirected page just fine. console.log fires in the right places. 4) The dialog otherwise functions, but when I try to messageParent from this location.replaced code, it fails. This process works fine in Outlook, and in Excel online, but not in Excel desktop. I get the EXACT error reported above.

Suyash-Muley commented 1 year ago

Yeah, that was the same issue I had mesageParent not working in desktop and eventually I ended up using validation API for my requirement, but I guess you could try with the taskpane in Desktop it'll be a bit easier than Dialog to perform the process. As we don't have any exact solution from their side for this issue.

davecra commented 1 year ago

It is odd it works from Outlook just fine. I need the dialog because in Outlook my add-in dynamically loads my subscriber page just fine, but then in Excel, it will load it just fine as well in the dialog, I just cannot get it to return a result to my taskpane. It is infuriating because to rework that to work in the taskpane will require a rewrite of my Outlook add-in as well.

Actually, I am validating this more succinctly in my testing and it turns out Office.onReady() is not firing in Excel online for me at all in my location.replace scenario. It is firing in the desktop mode, but in neither case is Office.ui.messageParent working at all. It works before I do the location.replace, but not after.

And as I said it is infuriating because to release my Excel add-in I am going to have to go back and replace/update my Outlook add-in. And the refactoring is a nightmare.

I really need a solution for this.

davecra commented 1 year ago

I figured it out. Finally. I am debugging from one server but calling on my production server for live subscription data (a must at this stage). I did this:

(async ()=>await Office.onReady())();

And that was throwing an error that context could not be changed. Digging in I found it did not like the URL change. So, I published to a beta folder on my production server, and viola, everything worked. Outlook is definitely not this picky.

YasharHeydari commented 1 year ago

Hi Any updates on this error ? I'm calling the Dialog API to create a child Dialog and then I want to pass some data to my dialog using messageChild. My event handler is not called and I get the very same error 

excel-win32-16.01.js:25  Uncaught (in promise) TypeError: Cannot read properties of undefined (reading 'register')
    at t.register (excel-win32-16.01.js:25:338958)
    at excel-win32-16.01.js:25:1358356

Apparently richApiMessageManager is undefined in this scenario!

ramaguruprakash commented 11 months ago

I was getting the same error, I don't know if it is helpful but when I changed from Office.initialize to Office.onReady() in my dialog my messageChild started working.

I changed the dialog code from

Office.initialize = () => {
  const searchParams = new URLSearchParams(window.location.search);

  const action = searchParams.get("action") as MSAuthAction | null;
  console.log("start");
  switch (action) {
    case MSAuthAction.LOGIN: {
      handleLogin();
      return;
    }
    case MSAuthAction.LOGOUT: {
      handleLogout();
      return;
    }
    case MSAuthAction.LOGOUT_SUCCESS: {
      handleLogoutSuccess();
      return;
    }
    default: {
      handleLogin();
      return;
    }
  }
};

to

Office.onReady(() => {
  const searchParams = new URLSearchParams(window.location.search);

  const action = searchParams.get("action") as MSAuthAction | null;

  switch (action) {
    case MSAuthAction.LOGIN: {
      handleLogin();
      return;
    }
    case MSAuthAction.LOGOUT: {
      handleLogout();
      return;
    }
    case MSAuthAction.LOGOUT_SUCCESS: {
      handleLogoutSuccess();
      return;
    }
    default: {
      handleLogin();
      return;
    }
  }
});
bhattikashif commented 8 months ago

Any update on its status? I am facing similar problem randomly, not always.

zhenhuangMSFT commented 8 months ago

Hi @m-hellesen, could you help take a look at this issue? Thanks!