office-jsexcel-addinscustom-functions-excel

Excel JS Addin from existing web service


I am trying to develop an Excel add-in with custom functions and a task pane that interacts with an existing remote web application, to which I have added pages that load OfficeJS and my custom functions. The service uses cookie-based session authentication after login. What's the simplest way to get authentication working in the fetch requests used in my custom formulas? Do I have to use the Office Dialog API for auth? I can do local sign-in in the Taskpane directly - why would I need to use the Office Dialog API?

Once I have a valid authenticated browser session, can I use that session in a shared runtime to automatically authenticate the ajax requests made by my custom functions? I have tried to get a shared runtime going to test this, to no avail. My manifest is valid, the Add-in will install via sideload, and the task pane will load, but when I execute a custom function the shared runtime tries to start and then times out, with no error message in the runtime log. Addin Error Message


Solution

  • Per conversation in comments, to use cookie based authentication for Excel add-ins or custom functions, you need to use the Shared Runtime. You can configure by adding the Runtimes element into your manifest, and use the same resource ID/page for all the components you want shared, which in this case would be your taskpane and your custom function.

    <VersionOverrides xmlns="http://schemas.microsoft.com/office/taskpaneappversionoverrides" xsi:type="VersionOverridesV1_0">
      <Hosts>
        <Host xsi:type="Workbook">
          <Runtimes>
            <Runtime resid="ContosoAddin.Url" lifetime="long" />
          </Runtimes>
          ...
    
          <Page>
              <SourceLocation resid="ContosoAddin.Url"/>
          </Page>
    ...
    

    A detailed walk through can be found here: https://learn.microsoft.com/en-us/office/dev/add-ins/tutorials/share-data-and-events-between-custom-functions-and-the-task-pane-tutorial