Read formula error information by JavaScript API

[chengtie]

Hello,

I would like to know if it's possible to read formula error information by JavaScript API. Here is an example of formula error:

Thank you

[qinliuMSFT]

Hello @chengtie , are you using Custom Function? If so, hope this article Handle and return errors from your custom function can help you.

[chengtie]

Hello @chengtie , are you using Custom Function? If so, hope this article Handle and return errors from your custom function can help you.

Hello, I'm not using Custom Function.

Given a cell (e.g., E4), is there a JavaScript API to check if its formula raises an alarm and return the alarm type (e.g., "Inconsistent Formula")?

[qinliuMSFT]

Hi @chengtie , the Excel JavaScript API provides methods to check if a cell contains a formula and if there is an error. You can use the Range object to access the cell and its properties. Specifically, you can use the formulas and values properties to determine if a cell contains a formula and if there is an error.

Please refer to the following code snippet:

  await Excel.run(async (context) => {
    // Get the active cell
    const range = context.workbook.getActiveCell();

    // Load the necessary properties
    range.load(["formulas", "values"]);
    await context.sync();

    console.log(`Cell formulas:  ${range.formulas[0][0]}`);
    console.log(`Cell value: ${range.values[0][0]}`);
  });

For example, you will get output like this:

Hope it's helpful.

[chengtie]

Hi @chengtie , the Excel JavaScript API provides methods to check if a cell contains a formula and if there is an error. You can use the Range object to access the cell and its properties. Specifically, you can use the formulas and values properties to determine if a cell contains a formula and if there is an error.

Please refer to the following code snippet:

  await Excel.run(async (context) => {
    // Get the active cell
    const range = context.workbook.getActiveCell();

    // Load the necessary properties
    range.load(["formulas", "values"]);
    await context.sync();

    console.log(`Cell formulas:  ${range.formulas[0][0]}`);
    console.log(`Cell value: ${range.values[0][0]}`);
  });

For example, you will get output like this:

Hope it's helpful.

Hello, I know these APIs you mentioned. But they don't return information like "Inconsistent Formula" shown in the screenshot of my initial question. My question is whether there is any API to return that information.

[qinliuMSFT]

@chengtie , thanks for the clarification, I understand your request now. I've put this case in our backlog #9455408 , and PG team will reply to you soon.