[Bug] Spreadsheet using $ on cell references produces errors on file open

[hatkyinc2]

Is there an existing issue for this?

• I have searched the existing issues

Version

0.20 (Release)

Full version info

I have tested this in 0.20.1, 0.20.2, 0.21-weekly 33303-2023-05-25-conda-Windows-x86_64-py310.7z

[code]
OS: Windows 10 build 19045
Word size of FreeCAD: 64-bit
Version: 0.21.0.33303 (Git)
Build type: Release
Branch: master
Hash: 5b075a9938b78076fdde9034de4a05685e42690e
Python 3.10.11, Qt 5.15.8, Coin 4.0.0, Vtk 9.1.0, OCC 7.6.3
Locale: English/Australia (en_AU)
Installed mods: 
  * fasteners 0.4.56
[/code]

[code]
OS: Windows 10 Version 2009
Word size of FreeCAD: 64-bit
Version: 0.20.2.29603 (Git)
Build type: Release
Branch: releases/FreeCAD-0-20
Hash: 930dd9a76203a3260b1e6256c70c1c3cad8c5cb8
Python 3.8.10, Qt 5.15.2, Coin 4.0.1, Vtk 8.2.0, OCC 7.6.3
Locale: English/Australia (en_AU)
Installed mods: 
  * fasteners 0.4.56
[/code]

Subproject(s) affected?

Spreadsheet

Problem description

Using the $ sign on a cell, saving the file, and reloading the file produces an error.
Updating the cell with space and saving fixes it.

Recreate:

• New file
• New spreadsheet
• Cell A1 = 1 (anything)
• Cell B1 = =$A1 (also the same for =A$1 or =$A$1)
• Save the Spreadsheet
• Close file
• Open the file
  = Error

Why did I use the $ cell? I got accustomed to it from Excel/google sheets, etc., to move formulas around...

Might be related to #7645
Might or might not be related to #6395

Anything else?

Sample file:
test.zip

Code of Conduct

• I agree to follow this project's Code of Conduct

[benj5378]

So looked at this. The issue is, that a cell might rely on another cell. If this is the case and it doesn't compute the dependency cell first, then it throws an error. The error is thrown at

FreeCAD/src/App/ObjectIdentifier.cpp

Line 1831 in 6c72f80

access(rs,&pyvalue);

under the call

Sheet::recomputeCells ->
Sheet::updateProperty ->
Expression::eval ->
Expression::getPyValue ->
VariableExpression::_getPyValue ->
ObjectIdentifier::getPyValue ->
ObjectIdentifier::ResolveResults::ResolveResults ->
ObjectIdentifier::Resolve

When there is this many calls just to resolve an expression, then I get a bit worried. Many of them are also just shallow functions for some reason...

The solution to this bug is to implement a guard clause, that checks if the expression has any dependencies and if so computes these first. Unfortunately, I can't really figure out what happens in ObjectIdentifier::Resolve and where we might be able to check for dependencies. And even if it's possible to do a call all the way back to the spreadsheet, as such a guard clause would only be relevant for spreadsheet expressions?

Perhaps @wwmayer could give some guidance?