excel-formulaoffice-jsoffice-addinscustom-functionjavascript-api-for-office

How to get advanced function environment function in excel using officejs


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? Test function created by Advanced Formula Environment


Solution

  • 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...

    enter image description here

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

    enter image description here

    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();
     });