Tracing precedents/dependents within Formulas

Tracing precedents/dependents within Formulas

Posted by: davide.vago on 23 October 2020, 4:20 am EST

• 

  davide.vago

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 23 October 2020, 4:20 am EST

  Good morning/afternoon

  I’m currently trying to implement the following feature: https://corporatefinanceinstitute.com/resources/excel/study/trace-dependents/

  which permits to audit a formula within a spreadsheet.

  I found the documentation of SpreadJS right here:
  
  https://www.grapecity.com/spreadjs/docs/v13/online/formula-auditing.html?highlight=trace%2C but I have a 3 issues:

  1. There’s seems no way to add the arrows between cells which is quite fundamental to understand the flow of the formula.

  2. The code snippet provided is using setTimeout which unfortunately after a bunch of selections (navigating the sheet via keyboard) does bloat the DOM and the whole experience become laggy. I tweaked removing these setTimeout (clearTimeout) but the output seems to be quite the same.

  3. When the user enters in edit mode of a cell the dependencies show with a pre-existing style (the blue/purple/green outline) which somehow gets mixed up with the style given by the tracing function… ideally, I would expect those tracing styles to be overridden by the edit mode rather than mixing them up.

  Could you please have a look?
  
  Thank you in advance for your support

• 

  davide.vago

  • Post Options:
  • Link
    Copy

  Posted 23 October 2020, 4:45 am EST

  Sorry for bloating the post but I also found out that the current tracing system is not picking up ranges, so let’s say you have a formula with a =SUM(A1:A10) the precedents highlighting system get’s just the first cells (A1)

• 

  avinash.pathak

  

  • Post Options:
  • Link
    Copy

  Posted 26 October 2020, 1:44 am EST

  Hi Davide,

  We are working on this case. We shall provide you an update soon.

  Regards
  
  Avinash

• 

  avinash.pathak

  

  • Post Options:
  • Link
    Copy

  Posted 26 October 2020, 11:52 pm EST

  Hi Davide,

  1. You may add a connector shape between the cells. Please refer to the following code snippet and the attached sample that demonstrates the same.

  
  var shape2 = sheet.shapes.addConnector(
  "myShape2",
  GC.Spread.Sheets.Shapes.ConnectorType.straight,
  null,
  null,
  10,
  10
  );
  shape2.startRow(node.row);
  shape2.endRow(row);
  shape2.startColumn(node.col);
  shape2.endColumn(col);
  

  sample: https://codesandbox.io/s/ecstatic-pasteur-j7syy?file=/src/index.js

  2. We are not able to replicate the issue at our end. Please refer to the following attached gif and if the issue persists please share a small sample that replicates the issue so that we could investigate it further.

  3). You may use EditStarted eventa and clear styles when use enters the cell. Please refer to the followingcode snippet and attached sample that demonstrates the same.

  
  activeSheet.bind(GC.Spread.Sheets.Events.EditStarted, (e, args) => {
  clearStyles(args.sheet);
  });
  function clearStyles(sheet) {
  if (oldDependentCells) {
  oldDependentCells.forEach(function (node) {
  sheet.getCell(node.row, node.col).backColor(undefined);
  });
  }
  if (oldPrecedentCells) {
  oldPrecedentCells.forEach(function (node) {
  sheet.getCell(node.row, node.col).backColor(undefined);
  });
  }
  sheet.shapes.clear();
  }
  

  sample: https://codesandbox.io/s/ecstatic-pasteur-j7syy?file=/src/index.js:5801-6154

  precedents highlighting system gets just the first cells (A1)

  We are able to replicate the issue at our end hence we have forwarded it to the concerned team for further investigation. We will update you regarding this as soon as we get any information regarding the same. The internal tracking ID for this issue will be SJS-6217.

  Regards
  
  Avinash

• 

  avinash.pathak

  

  • Post Options:
  • Link
    Copy

  Posted 29 October 2020, 1:53 am EST

  Hi Davide,
  
  Regarding
  
  >>precedents highlighting system gets just the first cells (A1)

  The Devs has informed us that this is by design, getPrecendents and getDependents method is correctly returning an object, which in the case of the one method returning an object for the entire cell range.

  Regards
  
  Avinash

• 

  davide.vago

  • Post Options:
  • Link
    Copy

  Posted 2 November 2020, 1:27 am EST

  Thank you Avinash for clearify it.
  
  I will update this post as soon as I have validated the above

  Regards

• 

  davide.vago

  • Post Options:
  • Link
    Copy

  Posted 23 November 2020, 11:48 pm EST

  Dear Avinash,

  I started digging into this implementation and I noticed the usage of a plugin within your snippet related to the Shapes.

     <script src="https://unpkg.com/@grapecity/spread-sheets-shapes@13.2.3/dist/gc.spread.sheets.shapes.min.js"></script>
  

  I then installed the dependency from here:
  
  https://www.npmjs.com/package/@grapecity/spread-sheets-shapes

  However I’m not quite sure how to refer to it as if I import that library as:
  
  import Shapes from ‘@grapecity/spread-sheets-shapes’ or import * as Shapes from ‘@grapecity/spread-sheets-shapes’ the code complain.

  Also within your snippet you refer to sheet.shapes.clear() but shapes doesn’t exists within the sheet instance so it starts throwing errors.

  Would you mind showcasing the functionality using imports rather than loading the libs as external resources?

  Thanks for your support.

• 

  avinash.pathak

  

  • Post Options:
  • Link
    Copy

  Posted 24 November 2020, 9:01 pm EST

  Hi Davide,

  When you add the library using the script tag is shapes package is automatically added to the spread in this case you do not have to import the shapes.

  Further, if you install the shapes package using npm then you just need to add the import “@grapecit/spread-sheets-shapes” code wherever you want to shapes method. Please refer to the following code snippet and attached sample that demonstrates the same.

  
  import "@grapecity/spread-sheets-shapes";
  export class AppComponent {
    
  
    workbookInit(args) {
      let spread: GC.Spread.Sheets.Workbook = args.spread;
      this.spreadsheet = spread;
      let sheet = spread.getActiveSheet();
      sheet.shapes.add(
        "autoShape",
        GC.Spread.Sheets.Shapes.AutoShapeType.heart,
        100,
        50,
        100,
        150
      );
    }
  }
  
  

  sample: https://codesandbox.io/s/sleepy-tharp-p5h97?file=/src/app/app.component.ts

  Regards
  
  Avinash