OfficeRuntime.storage in custom functions does not get right values of items set by taskpane (Excel for Windows)

[chengtie]

We are adding custom functions to our existing add-ins in Microsoft Office Store. The new functions work well in Excel Online, we just realized that they don't work well in Excel for Windows, because of OfficeRuntime.storage.

In short, clicking on a button in the page Pane sets a value for variableB, but we cannot get this value from the custom function TEST in Excel for Windows, though it works in Excel Online.

Here is FunctionsStart/index:

import React, { useEffect } from 'react';

declare var OfficeRuntime;

interface FunctionsStartProps {}

const FunctionsStart: React.FunctionComponent<FunctionsStartProps> = ({}) => {
  console.log("FunctionsStart");

  useEffect(() => {
    OfficeRuntime.storage.setItem("variableA", "value-initiated-by-FunctionsStart")
      .then(() => {
        console.log('Set variableA in OfficeRuntime.storage by FunctionsStart');
        return OfficeRuntime.storage.getItem("variableA");
      })
      .then(value => {
        console.log("Retrieved variableA from OfficeRuntime.storage to show it's well set:", value);
      })
      .catch((error) => {
        console.error('Error with OfficeRuntime.storage operation by FunctionsStart:', error);
      });
  }, []);

  return (
    <div></div>
  )
}

export default FunctionsStart;

Here is Pane/index.tsx:

import React from 'react';

declare var OfficeRuntime;

const Pane = () => {
  const setVariable = () => {
    OfficeRuntime.storage.setItem("variableB", "value-set-by-Pane")
      .then(() => {
        console.log('Set variableB in OfficeRuntime.storage by Pane');
        return OfficeRuntime.storage.getItem("variableB");
      })
      .then(value => {
        console.log("Retrieved variableB from OfficeRuntime.storage to show it's well set:", value);
      })
      .catch((error) => {
        console.error('Error with OfficeRuntime.storage operation by Pane:', error);
      });
  };

  return (
    <div>
      <button onClick={setVariable}>Set variableB in OfficeRuntime.storage</button>
    </div>
  );
};

export default Pane;

And here is a testing custom function:

/**
 * Test OfficeRuntime
 * @customfunction
 * @returns {string} Result
 **/
async function test() {
  try {
    const variableA = await OfficeRuntime.storage.getItem("variableA");
    const variableB = await OfficeRuntime.storage.getItem("variableB");
    const r = `variableA: ${variableA}, variableB: ${variableB}`;
    return r
  } catch (error) {
    console.error("error", error);
    return error.toString();
  }
}

And here is the XML manifest: https://pastebin.com/JTDHz8Vk (you could test on your side in Excel Online and Excel for Windows).

Here is the screenshot under Excel Online. After clicking on the button in the taskpane, re-runnning =SAI.TEST() returns a good value for variableB:

Here is the screenshot under Excel for Windows. After clicking on the button in the taskpane, re-running =SAI.TEST() still returns null for variableB:

So does anyone know how to make OfficeRuntime.storage work in Excel for Windows?

[zhenhuangMSFT]

Hi @chengtie, thanks for reporting the issue! Could you fill-in the template next time you raise the issue to help the bot triage? Thanks!

[MiaofeiWang]

Hi @chengtie, the reason OfficeRuntime.storage doesn't work for your add-in on Windows is because the custom functions are using a different runtime with your pane. In the manifest you shared, the custom function's page URL is different with the taskpane's url, while to share the runtime, they should be the same.

<Page>
    <SourceLocation resid="Taskpane.Url" />
</Page>

 <SourceLocation resid="Contoso.TaskpaneEditor.Url" />

I tried with changing the URL of custom function's page to Contoso.TaskpaneEditor.Url. Then I was able to get the variableB with =SAI.Test() (screen shot as below). But of course, variableA is missing because FunctionsStart is never called.

The reason why it works on Excel Online is that everything runs on the same browser process in which the user has opened Office on the web. So, the issue on Excel Online is hidden.

To prevent from page redirecting, you may use React router (https://v5.reactrouter.com/web/guides/quick-start), so that the runtime storage persists.

[chengtie]

@MiaofeiWang Thank you for your reply. I managed to share OfficeRuntime.storage between 2 taskpanes and custom functions with this XML manifest.

In this XML manifest, I need to use <Page><SourceLocation resid="Contoso.TaskpaneEditor.Url" /></Page> in custom functions where Contoso.TaskpaneEditor.Url is also in <Action xsi:type="ShowTaskpane"<TaskpaneId>Office.AutoShowTaskpaneWithDocument</TaskpaneId><SourceLocation resid="Contoso.TaskpaneEditor.Url" /></Action> of a button control Contoso.TpButtonEditor in the ribbon.

Now, my question is if it's possible to link runtimes of a taskpane and custom functions without displaying a button for the taskpane in the ribbon. Ideally, I would like to link runtime of custom functions to a "hidden" taskpane without displaying a button for this "hidden" taskpane in the ribbon; as a result, I assume, the runtime of custom functions will be linked to all the taskpanes.

[MiaofeiWang]

A button on the ribbon is not necessary for custom functions. Custom functions and the task panes don't have a linkable relationship. The link relationship is between custom functions and the page URL.

The issue here is if custom functions running on an URL_A, and the action <Action xsi:type="ShowTaskpane"> of the button on ribbon opens a new URL_B, the storages will not be the same. So our recommended best practice is 1) single URL in the manifest, 2) use <Action xsi:type="ExecuteFunction"> instead of <Action xsi:type="ShowTaskpane"> to open different task panes.

[chengtie]

@MiaofeiWang When I said custom functions are "linked" to taskpanes, I meant they share the same storage.

I'm still confused, because I have been using much less <Action xsi:type="ExecuteFunction">. It would be great to have somewhere an example manifest to enable this full scenario of sharing storage among mutiple taskpanes and custom functions. To make things clearer, could you guide me in modifying my current XML and writing an XML manifest such that:

1) clicking on the first button Contoso.TpButtonEditor opens the taskpane Contoso.TaskpaneEditor.Url (my current manifest does this) 2) clicking on the second button Contoso.TpButtonEditor2 opens the taskpane Contoso.TaskpaneEditor2.Url (my current manifest does this) 3) launching custom functions for the first time opens the page https://v10.10studio.tech/#/functions-start behind the screen. And all the taskpanes and custom functions will share the same storage.

Thank you in advance for your concrete guidance.

[MiaofeiWang]

Hi @chengtie , sorry for the late response. For your question, 1st, you don't need to use a separate page (the "functions-start" one) for custom function. It seems you are trying to do something (FunctionsStart) when add-in gets initialized. To do this, you may use Office.OnReady which is called after add-in initialization done (https://learn.microsoft.com/en-us/javascript/api/office?view=common-js-preview#office-office-onready-function(1)). So you can reduce the count of URLs by just setting custom function's page to use same page with the button (for example Contoso.TaskpaneEditor.Url), which we have discussed before.

The 2nd thing is multiple buttons on ribbon to open multiple pages which is the main obstacle for shared runtime. For the 2nd button Contoso.TpButtonEditor2, the manifest could be like <Action xsi:type="ExecuteFunction"> <FunctionName>openEditor2</FunctionName> </Action>. And then you need the JS code to make it work, including:

• associate the name with some function

  Office.actions.associate("openEditor2", async (event) => {
  (window as any).routerRef.current.navigate("/Editor2");
  await Office.addin.showAsTaskpane();
  event.completed();
  });

• Use router (https://v5.reactrouter.com/web/guides/quick-start I mentioned before) to maintain state when redirecting pages. I could put some sporadic code snippets (by myself, not officially recommended) here. But you can find many public discussions (for example on StackOverflow) around how to maintain the state.

  Office.onReady(async () => {
  const routerRef = React.createRef();
  (window as any).routerRef = routerRef;
  ReactDOM.render(
  <React.StrictMode>
    <FluentProvider theme={webLightTheme}>
      <Router ref={routerRef} />
    </FluentProvider>
  </React.StrictMode>,
  document.getElementById("container")
  );
  });

  
  const Router = forwardRef((_props, ref) => {
  const routerRef = useRef(null);
  useImperativeHandle(
  ref,
  () => {
    return {
      navigate(path: string) {
        router.navigate(path);
      },
    };
  },
  []
  );
  
  return (
  <div ref={routerRef}>
    <RouterProvider router={router} />
  </div>
  );
  });

const router = createBrowserRouter([ { path: "/Editor", element: , }, { path: "/Editor2", element: , }, ]);

[microsoft-github-policy-service[bot]]

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!

[microsoft-github-policy-service[bot]]

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