1

Would it be possible to have custom functions and a taskpane using the same html page? We would like to call and set custom functions on the worksheet based on certain actions/parameters coming from the taskpane.

geochr
  • 241
  • 1
  • 3
  • 15

1 Answers1

1

stay tuned, we're still working on the javascript runtime for custom functions. This will differ from the taskpanes which are running in a seperate IE process (on windows, at least).

In addition to the APIs called out here: https://learn.microsoft.com/en-us/office/dev/add-ins/excel/custom-functions-overview#known-issues, we're planning to have an API that allows you to share state with custom functions.

If you sign up at https://aka.ms/customfunctionscall, we can make sure we send you the info when it is available in a few weeks time. Will update this thread with details then.

In terms of setting custom functions you will be able to do that by entering a formula =CONTOSO.ADD42ASYNC(1,2), i.e:

async function setFormula() {
    try {
        await Excel.run(async (context) => {
            const sheet = context.workbook.worksheets.getItem("Sheet1");

            const range = sheet.getRange("A1");
            range.formulas = [[ "=CONTOSO.ADD42(1,2)" ]];
            
            await context.sync();
        });
    }
    catch (error) {
       console.log(error);
    }
}
Keyur Patel - MSFT
  • 796
  • 5
  • 7
  • Thanks for your response. As far as I understand custom functions are still experimental (i.e. we cannot roll them out to people that are not subscribed to the insider program) and need to be implemented in a separate "context/space" from the task pane app. So, in our case where we want the task pane app and custom functions to share common logic we would have to maintain it as a standalone library and then duplicate it when building the add-in to be deployed? And yes, then we would be able to set custom functions by entering their formulas in cells. Does this sound correct? – geochr Aug 04 '18 at 10:44
  • 1
    Good question, I believe in the case you could reference the functions.js file directly from your taskpane logic. This way, it would be available in both runtimes for your taskpane to use as helper utitilties. The functions.js file would be also used by Excel and be registered as custom functions. – Keyur Patel - MSFT Aug 07 '18 at 18:37
  • 1
    We'll look to document this as a best practice, as I think this could be a common scenario. – Keyur Patel - MSFT Aug 07 '18 at 18:37