0

I am trying to incorporate custom functions in Excel from the preview feature of Office.js into an existing Excel add-in that I wrote. The Excel add-in is using Angular. Right now, both projects are separated in their folder and work great when run independently. I tried to combine the two by copying the files from the custom functions project into my existing add-in project. I followed the exact same architecture as recommended in the GitHub solution: OfficeDev/Excel-Custom-Functions

The only part where I'm struggling is with the manifest file. According to the documentation, the node < ExtensionPoint /> defining the custom functions need to be located within the node < AllFormFactors /> (AllFormFactors documentation) of the manifest file. For a regular add-in loaded through the taskpane (which is what I have), the node < ExtensionPoint /> is loaded within the node < DesktopFormFactor /> (DesktopFormFactor documentation).

In the manifest file I have, I copied the node < AllFormFactors /> containing the definition of my functions and put it before the node < DesktopFormFactor />. I made sure the manifest file was valid using the manifest-file-validator. I also copied the latest version of the manifest file to my shared folder location, and I also cleared the cache to make sure the latest version was loaded.

However, despite doing this, I still get an error saying that my custom functions could not be loaded, and I don't know where the problem is. I couldn't really find an answer online, so any help would be appreciated. Thanks!

For reference, here's the code contained in the manifest file.

<?xml version="1.0" encoding="UTF-8"?>
<OfficeApp
          xmlns="http://schemas.microsoft.com/office/appforoffice/1.1"
          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
          xmlns:bt="http://schemas.microsoft.com/office/officeappbasictypes/1.0"
          xmlns:ov="http://schemas.microsoft.com/office/taskpaneappversionoverrides"
          xsi:type="TaskPaneApp">
  <Id>08807c12-4cd2-4e47-927e-bce93ebeb036</Id>
  <Version>1.0.0.0</Version>
  <ProviderName>David Rubino</ProviderName>
  <DefaultLocale>en-US</DefaultLocale>
  <DisplayName DefaultValue="Angular Add-in" />
  <Description DefaultValue="Testing an add-in with Angular" />
  <IconUrl DefaultValue="https://localhost:3000/assets/images/data-builder-80.png" />
  <HighResolutionIconUrl DefaultValue="https://localhost:3000/assets/images/data-builder-80.png"/>
  <SupportUrl DefaultValue="http://contoso.com/support" />
  <AppDomains>
    <AppDomain>https://login.microsoftonline.com</AppDomain>
  </AppDomains>
  <Hosts>
    <Host Name="Workbook" />
  </Hosts>
  <DefaultSettings>
    <SourceLocation DefaultValue="https://localhost:3000/index.html" />
  </DefaultSettings>
  <Permissions>ReadWriteDocument</Permissions>
  <VersionOverrides xmlns="http://schemas.microsoft.com/office/taskpaneappversionoverrides" xsi:type="VersionOverridesV1_0">
    <Hosts>
      <Host xsi:type="Workbook">
        <AllFormFactors>
                    <ExtensionPoint xsi:type="CustomFunctions">
                        <Script>
                            <SourceLocation resid="JS-URL" />
                        </Script>
                        <Page>
                            <SourceLocation resid="HTML-URL"/>
                        </Page>
                        <Metadata>
                            <SourceLocation resid="JSON-URL" />
                        </Metadata>
                        <Namespace resid="namespace" />
                    </ExtensionPoint>
                </AllFormFactors>
        <DesktopFormFactor>
          <GetStarted>
            <Title resid="Contoso.GetStarted.Title"/>
            <Description resid="Contoso.GetStarted.Description"/>
            <LearnMoreUrl resid="Contoso.GetStarted.LearnMoreUrl"/>
          </GetStarted>
          <FunctionFile resid="Contoso.DesktopFunctionFile.Url" />
          <ExtensionPoint xsi:type="PrimaryCommandSurface">
            <CustomTab id="TabNavigator">
              <Group id="Contoso.Group1">
                <Label resid="Contoso.Group1Label" />
                <Icon>
                  <bt:Image size="16" resid="Navigator.data_builder_16x16" />
                  <bt:Image size="32" resid="Navigator.data_builder_32x32" />
                  <bt:Image size="80" resid="Navigator.data_builder_80x80" />
                </Icon>
                <Control xsi:type="Button" id="Navigator.Login">
                  <Label resid="Navigator.Login.Label" />
                  <Supertip>
                    <Title resid="Navigator.Login.Label" />
                    <Description resid="Navigator.Login.Tooltip" />
                  </Supertip>
                  <Icon>
                    <bt:Image size="16" resid="Navigator.login_16x16" />
                    <bt:Image size="32" resid="Navigator.login_32x32" />
                    <bt:Image size="80" resid="Navigator.login_80x80" />
                  </Icon>
                  <Action xsi:type="ShowTaskpane">
                    <TaskpaneId>ButtonId1</TaskpaneId>
                    <SourceLocation resid="Contoso.Taskpane.Url" />
                  </Action>
                </Control>
                <Control xsi:type="Button" id="Navigator.Refresh">
                  <Label resid="Navigator.Refresh.Label" />
                  <Supertip>
                    <Title resid="Navigator.Refresh.Label" />
                    <Description resid="Navigator.Refresh.Tooltip" />
                  </Supertip>
                  <Icon>
                    <bt:Image size="16" resid="Navigator.refresh_16x16" />
                    <bt:Image size="32" resid="Navigator.refresh_32x32" />
                    <bt:Image size="80" resid="Navigator.refresh_80x80" />
                  </Icon>
                  <Action xsi:type="ShowTaskpane">
                    <TaskpaneId>ButtonId1</TaskpaneId>
                    <SourceLocation resid="Contoso.Taskpane.Url" />
                  </Action>
                </Control>
                <Control xsi:type="Button" id="Contoso.TaskpaneButton">
                  <Label resid="Contoso.TaskpaneButton.Label" />
                  <Supertip>
                    <Title resid="Contoso.TaskpaneButton.Label" />
                    <Description resid="Contoso.TaskpaneButton.Tooltip" />
                  </Supertip>
                  <Icon>
                    <bt:Image size="16" resid="Navigator.data_builder_16x16" />
                    <bt:Image size="32" resid="Navigator.data_builder_32x32" />
                    <bt:Image size="80" resid="Navigator.data_builder_80x80" />
                  </Icon>
                  <Action xsi:type="ShowTaskpane">
                    <TaskpaneId>ButtonId1</TaskpaneId>
                    <SourceLocation resid="Contoso.Taskpane.Url" />
                  </Action>
                </Control>
              </Group>
              <Label resid="Contoso.Group1Label" />
            </CustomTab>
          </ExtensionPoint>
        </DesktopFormFactor>
      </Host>
    </Hosts>
    <Resources>
      <bt:Images>
        <bt:Image id="Navigator.data_builder_16x16" DefaultValue="https://localhost:3000/assets/images/data-builder-16.png" />
        <bt:Image id="Navigator.data_builder_32x32" DefaultValue="https://localhost:3000/assets/images/data-builder-32.png" />
        <bt:Image id="Navigator.data_builder_80x80" DefaultValue="https://localhost:3000/assets/images/data-builder-80.png" />
        <bt:Image id="Navigator.login_16x16" DefaultValue="https://localhost:3000/assets/images/login-16.png" />
        <bt:Image id="Navigator.login_32x32" DefaultValue="https://localhost:3000/assets/images/login-32.png" />
        <bt:Image id="Navigator.login_80x80" DefaultValue="https://localhost:3000/assets/images/login-80.png" />
        <bt:Image id="Navigator.refresh_16x16" DefaultValue="https://localhost:3000/assets/images/refresh-16.png" />
        <bt:Image id="Navigator.refresh_32x32" DefaultValue="https://localhost:3000/assets/images/refresh-32.png" />
        <bt:Image id="Navigator.refresh_80x80" DefaultValue="https://localhost:3000/assets/images/refresh-80.png" />
      </bt:Images>
      <bt:Urls>
        <bt:Url id="Contoso.Taskpane.Url" DefaultValue="https://localhost:3000/index.html" />
        <bt:Url id="Contoso.GetStarted.LearnMoreUrl" DefaultValue="https://go.microsoft.com/fwlink/?LinkId=276812" />
        <bt:Url id="Contoso.DesktopFunctionFile.Url" DefaultValue="https://localhost:3000/function-file/function-file.html" />
        <bt:Url id="JSON-URL" DefaultValue="https://localhost:3000/config/customfunctions.json" />
        <bt:Url id="JS-URL" DefaultValue="https://localhost:3000/src/customfunctions.js" />
        <bt:Url id="HTML-URL" DefaultValue="https://localhost:3000/src/customfunctions.html" />
      </bt:Urls>
      <bt:ShortStrings>
        <bt:String id="Contoso.TaskpaneButton.Label" DefaultValue="Data Builder" />
        <bt:String id="Contoso.Group1Label" DefaultValue="D&amp;P Navigator" />
        <bt:String id="Contoso.GetStarted.Title" DefaultValue="Get started with your sample add-in!" />
        <bt:String id="Navigator.Login.Label" DefaultValue="Login" />
        <bt:String id="Navigator.Refresh.Label" DefaultValue="Refresh" />
        <bt:String id="namespace" DefaultValue="CONTOSO" />
      </bt:ShortStrings>
      <bt:LongStrings>
        <bt:String id="Contoso.TaskpaneButton.Tooltip" DefaultValue="Click to Show a Taskpane" />
        <bt:String id="Contoso.GetStarted.Description" DefaultValue="Your sample add-in loaded succesfully. Go to the HOME tab and click the 'Show Taskpane' button to get started." />
        <bt:String id="Navigator.Login.Tooltip" DefaultValue="Login to your Duff &amp; Phelps account to get started." />
        <bt:String id="Navigator.Refresh.Tooltip" DefaultValue="Update all the connected Data Builder cells within the existing workbook." />
      </bt:LongStrings>
    </Resources>
  </VersionOverrides>
</OfficeApp>
David Rubino
  • 5
  • 3

2 Answers2

0

According to my research, i found this article which some comments may provide you new idea for this. Such as "join the Office Insider program". For your reference, Add excel custom functions to an existing excel add-in - Javascript Api

Simon Li
  • 303
  • 2
  • 4
  • Thanks Simon, however I had already tried what that post suggested and it didn't solve my problem. I still couldn't get my custom functions to load. I am also part of the Office Insider program. You couldn't even test the custom functions separately if you weren't part of that program. – David Rubino Aug 22 '18 at 14:45
  • Hi @DavidRubino did you managed to achieve a solution? I'm having the same problem – José Salgado Jan 17 '19 at 18:21
  • Hi @JoséSalgado I ended up using a different approach than custom functions in my project due to the fact that they are still in preview mode. – David Rubino Jan 19 '19 at 05:01
0

looks like you're using https and I think, given your manifest passes validation (which is good), the issue could be Excel isn't able to access your functions.json.

To workaround this, you'll need to ensure your certificate is trusted before you load the custom functions. See here for more: https://github.com/OfficeDev/generator-office/blob/master/src/docs/ssl.md (we'll look to make this more visible in our docs).

We're also working on adding logging capabilities as part of our manifest runtime logging so we can give you better indications on what failed.

Keyur Patel - MSFT
  • 796
  • 5
  • 7
  • Thanks Keyur. Can I have multiple certificates for my add-in? I was thinking of importing the ones from the custom functions project, but I already have trusted certificates in my add-in project (the ones from the regular task pane add-in). – David Rubino Aug 22 '18 at 19:22
  • Yes, I think that should work. Just need a certificate that unblocks your localhost. Previously, with taskpanes, there was UI that you could use to ask the browser to trust. Given custom functions are hidden (and will be running, soon, in the context of a pure Javascript Runtime), there is no UI available, so certs need to be pre-trusted. – Keyur Patel - MSFT Aug 23 '18 at 01:47
  • Likewise, you can also sideload your add-in on Office Online: https://learn.microsoft.com/en-us/office/dev/add-ins/testing/sideload-office-add-ins-for-testing. In that regard, since everything is running in the same browser, you could simply just pre-trust the localhost site, then sideload the add-in, It can also let you press F12 to use the browser debugging tools. – Keyur Patel - MSFT Aug 23 '18 at 01:49
  • So I imported the certificates from the custom functions project to my add-in project and added them as trusted certificates. It didn't work. Then I followed the directions of the website you referenced to side-load the add-in on Office Online. When I loaded the add-in, I didn't have an error showing up, but when I typed the name of my custom function, it wasn't recognized and just showed #NAME? in the cell. – David Rubino Aug 23 '18 at 20:39