Custom Formula ignored in cell if imported from JSON

Custom Formula ignored in cell if imported from JSON

Posted by: jk on 31 July 2019, 9:04 pm EST

• 

  jk

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 31 July 2019, 9:04 pm EST

  So what I’m doing is:

  Step 1 - I create and add custom formula to spreadJS:
  
  spread.addCustomFunction(new ParamDT());

  Step 2 - I add manually a custom formula to a cell:
  
  =CustomFormula(1,2,3)

  Step 3 - I save the workbook to JSON
  
  let json = spread.toJSON();

  Step 4 - I load the json back to a new instance of spreadjs:
  
  spread.fromJSON(json);

  Step 5 - I’m not sure if required but I reload a custom function:
  
  spread.addCustomFunction(new ParamDT());

  As a result, all previously saved custom formulas in cells display the following error: #NAME?

  meaning the function is not defined. Whenever I run recalcAll() function, only custom functions added programmatically (sheet.setFormula(…)) work properly. Manually added custom functions loaded with JSON are ignored.

  Can you suggest how to get proper custom functions functionality no matter how I create them?

  Thanks!

• 

  jk

  • Post Options:
  • Link
    Copy

  Posted 31 July 2019, 11:21 pm EST

  CustomFormula should be CustomFunction
  
  formula should be function

• 

  sharad.tomer

  

  • Post Options:
  • Link
    Copy

  Posted 1 August 2019, 5:42 pm EST

  Hi,

  Please refer to the following sample which demonstrates the correct way to serialize custom items include customNames and let us know if you face any issues:
  
  https://www.grapecity.com/demos/spread/JS/TutorialSample/Features/Workbook/customItemSerialize/purejs

  Regards

• 

  karen.sellers

  • Post Options:
  • Link
    Copy

  Posted 12 June 2020, 2:59 am EST

  I am using Angular 9 and Typescript.
  
  I have looked at your sample, but this does not seem to help me.

  i have added the custom functions to the sheet after the sheet was loaded with fromJson. I run sheet.recalcAll(true) but see the “#NAME?” in the custom function referenced cell. Only when i go to the cell and modify a parameter does the custom function execute. Can you please help here? We have quite a few custom functions and I need this to work with SpreadJS v13.
  
  ALSO, I have tried adding the custom functions to the spread itself and that doesn’t work at all for me. Even when i modify the parameter of the formula in the cell to a different value, i still get the #NAME? which means it didn’t find the formula at all. Only when I add the custom function to the sheet is the formula found. But as i said before, the formula execution does NOT happen with the recalcAll(true) on the sheet. It’s as if because there is no change to the parameters, the sheet thinks it does not need to recalc, even though I explicitly asked it to recalc.
  
  Please let me know what I’m doing wrong.
  
  Thanks!

• 

  karen.sellers

  • Post Options:
  • Link
    Copy

  Posted 12 June 2020, 3:34 am EST

  what is going wrong here? https://stackblitz.com/edit/angular-ivy-zyg67b

• 

  sharad.tomer

  

  • Post Options:
  • Link
    Copy

  Posted 14 June 2020, 7:23 pm EST

  Hi Karen,

  We have posted an update on the support thread you created on our private portal. Please refer to the same.
  
  To sum up, we need the following 2 things to properly serialize Custom formulas:
  
  1). Assign a typeName to the customer formula
  
  2). Make the Formula class/function available on the window object so that SJS could find the right type. If you do want to expose the custom type on the window object then you may also override the getTypeFromString method to return the correct type based on the typeName. Refer to the following code snippet:

  var oldFun = GC.Spread.Sheets.getTypeFromString;
  // Private types can not be accessed from window, so override getTypeFromString method.
  GC.Spread.Sheets.getTypeFromString = function(typeString) {
    switch (typeString) {
      case "MyCustomFormula":
        return MyCustomFormula;
      default:
        return oldFun.apply(this, arguments);
    }
  };
  

  You may also refer to the following updated sample: https://stackblitz.com/edit/angular-ivy-uaat1x?file=src/app/app.component.ts

  Regards