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?

Jakob Nielsen-MSFT · Accepted Answer · 2023-01-23 18:57:47Z

2

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

answered Jan 23, 2023 at 18:57

Jakob Nielsen-MSFT

5142 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Shiv Yadav Over a year ago

Thanks for your reply. Can you please tell me why there is two-time x, and y in the formula =LAMBDA(x,y,x+y).

Jakob Nielsen-MSFT Over a year ago

It is how the LAMBDA function works. First you list each of the arguments (in the example above x and y) and at last you provide the expression to be evaluated (in the example above x+y).

Collectives™ on Stack Overflow

How to get advanced function environment function in excel using officejs

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related