How to get advanced function environment function in excel using officejs

Question

I am new to the Advanced Formula Environment. I have created the one testing function using Advanced Formula Environment. I want to get that formula name using office JS. I have tried the following code but I failed to get the formula name.

async function getFormulas() {
    try {
        await Excel.run(async (context) => {
            const sheet = context.workbook;
      
            sheet.load("formulas");

            await context.sync();
            console.log(JSON.stringify(sheet.formulas, null, 4));
        });
    }
    catch (error) {
        console.log(error)
    }
}

Can anyone guide me on How to get the formula name which is created by Advanced formula environment?

Answer 1

The functions you define in the Advanced Formula Environment add-in are stored as named items in the Excel workbook.

For example, a function called Add...

... will be stored as a named item Add in the workbook:

An add-in can get the names in the workbook using the workbook.names collection.

Use this code to list the named items for a workbook:

  await Excel.run(async (context) => {
    const namedItems = context.workbook.names.load();
    await context.sync();

    console.log("This workbook contains " + namedItems.items.length + " named items.");

    for (let i = 0; i < namedItems.items.length; i++) {
      console.log(JSON.stringify(namedItems.items[i])) + "\n";
    }

    await context.sync();
 });