Option to show formulas in cells

Option to show formulas in cells

Posted by: ivanchenkodenis on 27 December 2017, 8:53 pm EST

• 

  ivanchenkodenis

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 27 December 2017, 8:53 pm EST

  Hello!
  
  Does spreadsheets have function (option/switch) to view formulas in cells.
  
  Like sheet.showFormulas(true) and all formulas in cells are appeared as tex, not as resulting value?
  
  Thanks!

• 

  deepak.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 28 December 2017, 7:48 pm EST

  Hello,

  You can get the formulas of all cells using getArray() method and set these formulas a cell value using setArray. Please refer to the sample application to know the implementation.

  Thanks,
  
  Deepak SharmaSpreadJSGetFormulaSetValue.zip

• 

  ivanchenkodenis

  • Post Options:
  • Link
    Copy

  Posted 29 December 2017, 6:41 am EST

  I understand, what you’re proposing (still this example not working https://monosnap.com/file/WxDlJCzwqSyYVx6WPj45Jp9rruemMU)
  
  But I think, after setting this formulas as text, we wont be able to edit them with coloring and helpers. Am I right?

• 

  deepak.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 2 January 2018, 1:02 am EST

  Hello,

  Here is the working code:

  
    $("#Button2").click(function () {
                  var rcount = activeSheet.getRowCount() - 1;
                  var ccount = activeSheet.getColumnCount() - 1;
                  var formulaArray = activeSheet.getArray(0, 0, rcount, ccount, true);
                  activeSheet.getRange(0, 0, rcount, ccount, GC.Spread.Sheets.SheetArea.viewport).clear(GC.Spread.Sheets.StorageType.data);
                  activeSheet.setArray(0, 0, formulaArray);  
              });
  
  

  You are right once we set the value of cells by overriding the formulas all formulas will be removed. You will still have the formulas in the formula array.
  
  The cell value will not be edited like formulas(with helpers and colors).

  Thanks,
  
  Deepak Sharma

• 

  hghanta

  • Post Options:
  • Link
    Copy

  Posted 16 October 2019, 3:33 am EST

  I was too looking for something like this. What we need is something like Formula Audit mode in excel. Is it possible to do something like that in Spread.Sheets ?

• 

  sharad.tomer

  

  • Post Options:
  • Link
    Copy

  Posted 16 October 2019, 5:11 pm EST

  Hi Harsha,

  Could you please confirm if you need just “ShowFormulas” feature of auditing or other features like Formula trace too?
  
  Auditing features are not supported in the current version of SpreadJS but they are already in our backlog to be supported in future releases.
  
  The formula tracing feature is expected to be released in the V13.
  
  For show formula, we are sorry but we do not have an ETA, I’ve added you as correspondence for the case(Internal tracking ID: SJS-2138) and will let you know about any updates regarding the same.

  Regards
  
  Sharad

• 

  GrapeCity

  • Post Options:
  • Link
    Copy

  Posted 28 February 2020, 10:06 am EST

  Upvote for formula auditing, like the show formulas feature in Excel. We currently have to export to Excel & do our show formulas in native excel but it would be very very nice & convenient if we didn’t need to do that.

• 

  sharad.tomer

  

  • Post Options:
  • Link
    Copy

  Posted 1 March 2020, 4:01 pm EST

  Hi,

  We have added you as correspondence for the case. I’ll let you know when this gets implemented.

  Regards

• 

  sharad.tomer

  

  • Post Options:
  • Link
    Copy

  Posted 23 April 2020, 10:44 pm EST

  Hi,

  This feature is now available in the latest build(13.1.0). In the latest build, we added an option ‘showFormulas’ to show formulas like the in MS Excel(using ctrl + `(backtick) shortcut). When we set the showFormula to true, the view changes to formula view, and then copy from the sheet causes the formulas to get copied on the clipboard instead of the selected values. You may refer to the following code snippet and the sample demonstrating the same:

  sheet.options.showFormulas = true;
  

  https://codesandbox.io/s/spread-js-starter-3flk1?file=/src/index.js

  Please update to the latest version and let us know if the issue persists for you. You may get the latest build from npm or download using the following link:
  
  http://cdn.grapecity.com/spreadjs/13.1.0/Files/SpreadJS.Release.13.1.0.zip

  Regards
  
  Sharad

• 

  GrapeCity

  • Post Options:
  • Link
    Copy

  Posted 24 April 2020, 12:28 am EST

  When will this be added as a button to the Designer like Excel? Thank you.

• 

  sharad.tomer

  

  • Post Options:
  • Link
    Copy

  Posted 24 April 2020, 1:25 am EST

  This is already added to the latest designer 13.1.0 too. You may check out the demo here:
  
  https://www.grapecity.com/spreadjs/designer/content/index.html
  
  Use ctrl+` shortcut or enable formulas view using the Formula->Formula Auding->Show Formulas option.

• 

  GrapeCity

  • Post Options:
  • Link
    Copy

  Posted 24 April 2020, 1:26 am EST

  Excellent! Thank you very much!