Getting Formula Information from Dirty Rows

Getting Formula Information from Dirty Rows

Posted by: tadekunle on 31 May 2021, 6:57 pm EST

• 

  tadekunle

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 31 May 2021, 6:57 pm EST

  Hi,

  I would like to get the formula info for rows that have been changed by the user. Right now my understanding of the getDirtyRows() method is that it returns the original and updated values of the rows. What I want is for it to return the formula that produced the value (if one existed). So as an example, if I entered “=1+2” into cell B5 I would like to receive that formula after checking for dirty rows as opposed to receiving the value “3”

  To clarify, I want to be able to see which rows the user has changed and find out if a cell in one of the dirty rows has a formula and if it does have a formula, I want to get that formula.

  Thanks,

  Tolu

• 

  avinash.pathak

  • Post Options:
  • Link
    Copy

  Posted 1 June 2021, 7:30 am EST

  Hi Tolu,

  We are sorry but getDirtyRows only provide the result value. You may use the CellChanged event for the required functionality. please refer to the following code snippet and let us know if you face any issues.

  
    sheet.bind(GC.Spread.Sheets.Events.CellChanged, (e, args) => {
      if (args.propertyName === "formula") {
        console.log(args);
        console.log("RowChanged ", args.row);
        //for getting the value from formula
        var result = GC.Spread.Sheets.CalcEngine.evaluateFormula(
          sheet,
          args.newValue,
          0,
          0
        );
        console.log("formula  result==>" + result);
      }
    });
  
  

  API Refrences:

  CellChanged: https://www.grapecity.com/spreadjs/docs/v14/online/SpreadJS~GC.Spread.Sheets.Events~CellChanged_EV.html

  evaluateFormula: https://www.grapecity.com/spreadjs/docs/v14/online/SpreadJS~GC.Spread.Sheets.CalcEngine~evaluateFormula.html

  Regards
  
  Avinash

• 

  tadekunle

  • Post Options:
  • Link
    Copy

  Posted 1 June 2021, 6:31 pm EST

  Hi Avinash,

  I have been able to get the formulas from the cells and save them as an array, with the formulas intact. Now the problem is that I want to set the array as my data source and have those formulas evaluate to a number rather than just appear as a string in the cell.

  Is there any way that I can use the evaluateFormula method as I am setting the array of data as a data source for my sheet so that the formulas I saved will be evaluated or will I just have to iterate through every cell and evaluate the formulas individually.

  So to clarify again, I have an array of rows similar to the information gotten from the getDirtyRows method with formulas saved as the values for certain cells. Is there a way I can automatically evaluate these formulas when I set this array as a data source?

  Thanks,

  Tolu

• 

  avinash.pathak

  • Post Options:
  • Link
    Copy

  Posted 2 June 2021, 4:43 am EST

  Hi Tolu,

  If I understand correctly you want to set the formula array to the sheet. For this, you may use the set Array method. Please refer to the following code snippet and let us know if you face any issues.

  
  //set formula
  var array = [["=1+1","=2+2","=3+3"],["=4+4","=5+5"],["=6+6","=7+7","=8+8","=9+9"]];
  activeSheet.setArray(1, 2, array, true);
  
  

  API References:
  
  setArray: https://www.grapecity.com/spreadjs/docs/v14/online/SpreadJS~GC.Spread.Sheets.Worksheet~setArray.html

  Regards
  
  Avinash