Error reading Excel workbook containing named formulas

[jackmansean64]

OS (e.g. Windows 10 or macOS Sierra)

Windows 10

Versions of xlwings, Excel and Python (e.g. 0.11.8, Office 365, Python 3.7)

xlwings 0.33.3, Office 365, Python 3.12

Describe your issue (incl. Traceback!)

A JavaScript error is thrown when calling xlwings.runPython(window.location.origin + "/end-point"); for my workbook. The error reads:

{
    "code": "InvalidOperation",
    "message": "This operation is not permitted for the current object.",
    "errorLocation": "NamedItem.getRange",
    "statement": "var range = name.getRange();",
    "surroundingStatements": [
        "var workbook = context.workbook;",
        "var names = workbook.names;",
        "var name = names.getItem(...) /* originally getItem(...) */;",
        "// >>>>>",
        "var range = name.getRange();",
        "// <<<<<",
        "// Instantiate {range}",
        "var worksheet = range.worksheet;",
        "worksheet.load([\"position\"]);",
        "var range1 = name.getRange();",
        "// Instantiate {range1}",
        "..."
    ],
    "fullStatements": []
}

Debugging the error in excel-win32.js leads me to believe that the cause is named references in my workbook.
The workbook contains numerous named references pointing to formulas like the following: =OFFSET(BalancesByMonth!$B$4,0,0,COUNTA(BalancesByMonth!$B:$B)-1,COUNTA(BalancesByMonth!$5:$5)).

I can reproduce the error outside of xlwings by running the following in ScriptLab:

  await Excel.run(async (context) => {
    const namedRanges = context.workbook.names.load();
    await context.sync();
    console.log("This workbook contains " + namedRanges.items.length + " named items.");
    for (let i = 0; i < namedRanges.items.length; i++) {
      console.log(JSON.stringify(namedRanges.items[i])) + "\n";
      console.log(namedRanges.items[i].getRange())
    }
    await context.sync();
  });

Which results in the same error. I've managed to find a couple stackoverflow posts similar issue such as https://stackoverflow.com/questions/78257772/excel-office-add-in-office-js-throws-error-when-trying-to-read-a-nameditems-r and https://stackoverflow.com/questions/70791127/evaluation-by-named-ranges-by-javascript-api-does-not-work-for-certain-formulas which seems to indicate that the operation .getRange() is not supported for certain named formulas.

The problem appears to be that xlwings.runPython automatically calls getRange() on all named references, which doesn't work when the named references contain certain formulas.

Is there a workaround or does this prevent me from using xlwings server until the issue is resolved?

[fzumstein]

You could probably fix it here: https://github.com/xlwings/xlwings-server/blob/main/app/static/js/core/xlwingsjs/xlwings.js#L282-L294 (book scope) or here: https://github.com/xlwings/xlwings-server/blob/main/app/static/js/core/xlwingsjs/xlwings.js#L347-L361 (sheet scope)

[jackmansean64]

Thanks for the quick response! Commenting out that loop worked for a short-term fix. Assuming the error originates there, wrapping it in a try-catch, skipping the problematic named items, and maybe warning the user somehow would probably be a better fix. I found debugging the office.js code a bit tough, there's very little information provided about the object being parsed.

[fzumstein]

If you have an example of a named range that causes the failure, that would help to reproduce, thanks!

[jackmansean64]

Here is the named range I mentioned previously:

[fzumstein]

To replicate: Go to the Name Manager and add the following Name:

Name: name1
Scope: Sheet
Refers to: =100 + 200