Adding custom functions to existing excel add-in (generated by visual studio)

Question

So I'm in the process of developing an add-in for excel. I started with the template generated by visual studio (not yeomon, since generating with VS made debugging setup easier). So far I've got some taskpane stuff set up to authenticate with a remote server, but now I've hit a problem trying to get custom functions running. The documentation is focused on yeomon-generated projects, and I've done my best to translate, but I seem to be missing something.

So what I've done so far:

For the sake of proof-of-concepting, I'm sticking with trying to get the samples provided in the OfficeDev repo working (https://github.com/OfficeDev/Excel-Custom-Functions). I've added the functions.ts from the sample (https://github.com/OfficeDev/Excel-Custom-Functions/blob/master/src/functions/functions.ts) to a MyAddInWeb/Custom directory in my project. I manually copied over the Functions.json and the Functions.html file generated by building the sample,and pointed the JSON-URL, JS-URL, and HTML-URL in my manifest to the respective file paths, as well as added an ExtensionPoint xsi:type="CustomFunctions tag and pointed script/page/metadata tags to the same files. The relevant portion of my manifest looks like this:

<?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">
...    
  <!--Begin TaskPane Mode integration. This section is used if there are no VersionOverrides or if the Office client version does not support add-in commands. -->
  <Hosts>
    <Host Name="Workbook" />
  </Hosts>
  <DefaultSettings>
    <SourceLocation DefaultValue="~remoteAppUrl/Home.html" />
  </DefaultSettings>
  <!-- End TaskPane Mode integration.  -->

  <Permissions>ReadWriteDocument</Permissions>

  <!-- Begin Add-in Commands Mode integration. -->
  <VersionOverrides xmlns="http://schemas.microsoft.com/office/taskpaneappversionoverrides" xsi:type="VersionOverridesV1_0">

    <!-- The Hosts node is required. -->
    <Hosts>
      <!-- Each host can have a different set of commands. -->
      <!-- Excel host is Workbook, Word host is Document, and PowerPoint host is Presentation. -->
      <!-- Make sure the hosts you override match the hosts declared in the top section of the manifest. -->
      <Host xsi:type="Workbook">
        <!-- Form factor. Currently only DesktopFormFactor is supported. -->
        <AllFormFactors>
          <ExtensionPoint xsi:type="CustomFunctions">
            <Script>
              <SourceLocation resid="Functions.Script.Url"/>
            </Script>
            <Page>
              <SourceLocation resid="Functions.Page.Url"/>
            </Page>
            <Metadata>
              <SourceLocation resid="Functions.Metadata.Url"/>
            </Metadata>
            <Namespace resid="Functions.Namespace"/>
          </ExtensionPoint>
        </AllFormFactors>
        <DesktopFormFactor>
...
        </DesktopFormFactor>
      </Host>
    </Hosts>

    <!-- You can use resources across hosts and form factors. -->
    <Resources>
      <bt:Images>
        <bt:Image id="Contoso.tpicon_16x16" DefaultValue="~remoteAppUrl/Images/Button16x16.png" />
        <bt:Image id="Contoso.tpicon_32x32" DefaultValue="~remoteAppUrl/Images/Button32x32.png" />
        <bt:Image id="Contoso.tpicon_80x80" DefaultValue="~remoteAppUrl/Images/Button80x80.png" />
      </bt:Images>
      <bt:Urls>

        <bt:Url id="JSON-URL" DefaultValue="~remoteAppUrl/Custom/Functions.json"/>
        <bt:Url id="JS-URL" DefaultValue="~remoteAppUrl/Custom/Functions.js"/>
        <bt:Url id="HTML-URL" DefaultValue="~remoteAppUrl/Custom/Functions.html"/>
        <bt:Url id="Functions.Script.Url" DefaultValue="~remoteAppUrl/Custom/Functions.js" />
        <bt:Url id="Functions.Metadata.Url" DefaultValue="~remoteAppUrl/Custom/Functions.json" />
        <bt:Url id="Functions.Page.Url" DefaultValue="~remoteAppUrl/Custom/Functions.html" />
        <bt:Url id="Contoso.DesktopFunctionFile.Url" DefaultValue="~remoteAppUrl/Functions/FunctionFile.html" />
        <bt:Url id="Contoso.Taskpane.Url" DefaultValue="~remoteAppUrl/Home.html" />
        <bt:Url id="Contoso.Callback.Url" DefaultValue="~remoteAppUrl/callback.html" />
        <bt:Url id="Contoso.GetStarted.LearnMoreUrl" DefaultValue="https://go.microsoft.com/fwlink/?LinkId=276812" />
      </bt:Urls>
      <!-- ShortStrings max characters==125. -->
      <bt:ShortStrings>
        <bt:String id="Functions.Namespace" DefaultValue="CONTOSO" />
        <bt:String id="Contoso.TaskpaneButton.Label" DefaultValue="Show Taskpane" />
        <bt:String id="Contoso.Group1Label" DefaultValue="Commands Group" />
        <bt:String id="Contoso.GetStarted.Title" DefaultValue="Get started with your sample add-in!" />
      </bt:ShortStrings>
      <!-- LongStrings max characters==250. -->
      <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:LongStrings>
    </Resources>
  </VersionOverrides>
  <!-- End Add-in Commands Mode integration. -->

</OfficeApp>

So here's where I hit the first hurdle. In Functions.ts, I got a typescript error complaining that CustomFunctions namespace is not defined. Not sure where that comes from in the sample. If I removed the type declaration (bad idea, I know), the add-in builds, and the custom functions show up are registered, but attempting to run any of them causes excel to hang on "We're starting the add-ins runtime, just a moment..." for a moment, and then I get an error in the task pane saying the add-in could not be started. Unfortunately, I'm not getting any usable error messages as far as I can see, so I'm not sure where the problem lies. If I had to guess, something isn't hooked up correctly in the manifest, but I'm not sure. Any help would be much appreciated. The documentation so far seems pretty lacking.

Answer 1

I believe I was having the same issue. For me turned out that the CustomFunctions interface was missing (it's automatically added if you select the custom functions template in the yo office-generator, but not if you don't use the Excel custom functions template option).

Comparing the project.json file from the Excel custom function template, I added @types/custom-functions-runtime and custom-functions-metadata-plugin packages which fixed the issues for me.

Can add with the lines below:

npm install --save-dev @types/custom-functions-runtime npm install --save-dev custom-functions-metadata-plugin

Answer 2

if you're building against Visual Studio, I'd recommended configuring your manifest to use a shared runtime. This will essentially configure your custom functions to run in the same web container as your taskpane, which will allow to you to debug your functions.

You can follow the steps here: https://learn.microsoft.com/en-us/office/dev/add-ins/excel/configure-your-add-in-to-use-a-shared-runtime

#1: Add a Runtimes element

<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>
    <AllFormFactors>

Where the resid points to your taskpane. #2 Ensure the taskpane html has a reference to your custom functions javascript file as well.

Note: the shared runtime feature just hit general availability and requires manifest schema changes that may not be yet in 2019 so you may get a build error in Visual Studio, but you could at least use the VS Debugger.