How to set dynamic formula?

How to set dynamic formula?

Posted by: priyatama.bhadke on 5 September 2023, 6:27 pm EST

• 

  priyatama.bhadke

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 5 September 2023, 6:27 pm EST

  Hi ,

  How to set Formula dynamically?

  for Example- I want to set formula of column A like is will be sum of column A, column B and column c?

  so for all the rows column A would have sum of column A, column B and column c.

  How to do that?

  Regards,
  
  Priya B

• 

  chandan.rawat

  

  • Post Options:
  • Link
    Copy

  Posted 6 September 2023, 9:34 pm EST

  Hi Priya,

  As I can understand, you want to apply the formula in all the rows of a column which sums the values of other columns.

  You can get the column range on which you want to apply the formula with sheet.getRange() method and then use cellRange.formula() method. The cellRange.formula() method sets the formula to all the cells of a given cell range.

  You can use the formula “SUM(A:A, B:B)” to get a sum of all the values of column A and Column B.

  Please refer to the attached sample for more understanding.

  sample: https://jscodemine.grapecity.com/share/C3lhLtUoREOM5e3upzHNEw/?IsEmbed=false&Theme=Unset&PreviewDirection=0&IsEditorShow=true&IsExplorerShow=true&IsPreviewShow=true&IsConsoleShow=true&IsRunBTNShow=false&IsResetBTNShow=false&IsOpenInCodemineBTNShow=false&PanelWidth=20&PanelWidth=50&PanelWidth=30&defaultOpen={"OpenedFileName"%3A["%2Findex.html"%2C"%2Fsrc%2Fapp.js"]%2C"ActiveFile"%3A"%2Fsrc%2Fapp.js"}

  In your response, you mentioned applying the formula to the column cell and the formula itself refers to that particular column(e.g. settings formula SUM(A:A) in cell A1). This creates circular references and may cause performance issues and unintended results.

  Please let us know if the issue still persists.

  References:

  cellRange.formula(): https://www.grapecity.com/spreadjs/api/classes/GC.Spread.Sheets.CellRange#formula

  Regards,
  
  Chandan

• 

  priyatama.bhadke

  • Post Options:
  • Link
    Copy

  Posted 17 October 2023, 6:20 pm EST

  Hi Chandan,

  I tried below code-
  
  sheet5.suspendPaint();

  sheet5.getRange(2, 136, sheet5.getRowCount(), 1).formula('SUM(EH:EH, FU:FU)');
  sheet5.resumePaint();
  
  window.spread = spread;
  window.sheet = sheet5;
  

  Here I want to apply sum of column EH to FU to column EG. It should apply this formula to all the rows column EG.

  It gives me Uncaught RangeError: Maximum call stack size exceeded
  
  at M.evaluateFunction (gc.spread.sheets.all.16.2.3.min.js:13:3352087)

  Can you please help me to solve this error?

  Regards,
  
  Priya B

• 

  priyatama.bhadke

  • Post Options:
  • Link
    Copy

  Posted 17 October 2023, 6:32 pm EST - Updated 17 October 2023, 6:37 pm EST

  Hi,

  

  920×384 35.9 KB

  Please find attached image you can the formula is set incorrectly. Please find below code and let me know what is wrong with this code and why formula is not set correctly.

  sheet5.suspendPaint();

  sheet5.getRange(2, 136, sheet5.getRowCount(), 1).formula('SUM(EH:EH, FU:FU)');
  sheet5.resumePaint();
  
  window.spread = spread;
  window.sheet = sheet5;
  

  Regards,
  
  Priya B

• 

  chandan.rawat

  

  • Post Options:
  • Link
    Copy

  Posted 18 October 2023, 4:38 pm EST - Updated 18 October 2023, 4:44 pm EST

  Hi Priya,

  It seems that the issue related to the error “Uncaught RangeError: Maximum call stack size exceeded” has been resolved. However, if you are still experiencing the issue, please let us know and provide us with a sample. You may modify the sample attached below as well and share it with us.

  Referring to your last post related to an incorrect formula being applied, as per my understanding, you want to find the sum of columns from column “EH” to column “FU”. For this, you can set the formula “=SUM(EH:FU)” in the column “EG”.

  It is important to note that the formula “=SUM(EH:FU)” evaluates the sum of all cells in the columns from EH to FU. So, if you want to specify a particular range in the columns from EH to FU, you can do so as well. For instance, if you want to get the sum of cells from the cell range EH3 to FU10, you can use the formula "=SUM(EH3:FU10).

  Please refer to the attached sample.

  sample: https://jscodemine.grapecity.com/share/ysIHWYpr6k6iwq6XfMpuxQ/?IsEmbed=false&Theme=Unset&PreviewDirection=0&IsEditorShow=true&IsExplorerShow=true&IsPreviewShow=true&IsConsoleShow=true&IsRunBTNShow=false&IsResetBTNShow=false&IsOpenInCodemineBTNShow=false&PanelWidth=20&PanelWidth=50&PanelWidth=30&defaultOpen={"OpenedFileName"%3A["%2Findex.html"%2C"%2Fsrc%2Fapp.js"]%2C"ActiveFile"%3A"%2Fsrc%2Fapp.js"}

  You may refer to https://www.grapecity.com/spreadjs/docs/formulareference/formulaoverview/cellreferences#site_main_content-doc-content_title to learn more about cell references in SpreadJS.

  Additionally, you can also use the range selection feature of SpreadJS to get the references of cell range in the sheet. Please refer to the attached GIF.

  Regards

  

• 

  priyatama.bhadke

  • Post Options:
  • Link
    Copy

  Posted 18 October 2023, 6:13 pm EST

  Hi Chandan,

  This sets the formula but for all the rows same formula is being applied. It should work like
  
  ex- if user enter something on row 3 to then that formula should be applied to row 3 only not other rows.

  Regards,
  
  Priya B

• 

  priyatama.bhadke

  • Post Options:
  • Link
    Copy

  Posted 18 October 2023, 7:03 pm EST

  Hi Chandan,

  I have fixed the issue. Happy to close the ticket.

  Thanks

• 

  chandan.rawat

  

  • Post Options:
  • Link
    Copy

  Posted 18 October 2023, 11:54 pm EST

  Hi,

  We are glad that your issue has been fixed. If you experience the issue, please let us know.

  If the issue is related to the issue discussed here then you can post your query here otherwise you can create a separate thread for the different issue.

  Regards