2

I am creating an Excel add-in and one of the features that we'd like to give our users is to let them create custom functions that are registered and added at runtime.

I understand how to create custom functions at design time by adding entries to your manifest and pointing to the javascript file that has the function and the json file that contains the definition of the function.

I see that Script Lab has the ability to add / register functions at runtime and was hoping that someone might be able to explain what needs to happen behind the scenes in talking with office.js or point me to a relevant tutorial on how that is completed.

John Putman
  • 89
  • 8

2 Answers2

1

It's not officially supported, but I've tried it, and it works like this:

  1. Create your custom functions metadata, manually like this or automatically with this

  2. Call Excel.CustomFunctionManager.register(myMetadataJsonString, '') like Script Lab does

  3. Call CustomFunctions.associate("ID_FROM_METADATA", myFunction) for each function

For example:

  // This might optional, but it seemed to only work once the taskpane had been opened
  await Office.addin.showAsTaskpane();

  await (Excel as any).CustomFunctionManager.register(
    JSON.stringify({
      functions: [
        {
          id: 'say_hi',
          name: 'say_hi',
          description: 'Returns a greeting',
          parameters: [],
          result: {
            type: 'string',
          },
        },
      ],
    }),
    ''
  );

  CustomFunctions.associate('say_hi', () => 'hi');
parched
  • 451
  • 6
  • 11
  • How were you running the code above? In your task pane or...? – T.J. Crowder Apr 05 '23 at 10:51
  • 1
    @T.J.Crowder, yes in the taskpane using a shared runtime. – parched Apr 05 '23 at 23:40
  • I put this in my functions json in case the shared runtime didn't start automatically, it gives the user a hint to force it. { "$schema": "https://developer.microsoft.com/en-us/json-schemas/office-js/custom-functions.schema.json", "functions": [ { "id": "please_open_taskpane", "name": "please_open_taskpane", "description": "Open the taskpane to load all custom functions", "parameters": [], "result": { "type": "string" } } ] } – parched Apr 05 '23 at 23:46
-1

Here is the script lab source codea link you can refer to. Hope can help.

Rita
  • 72
  • 3