Option to show formulas in cells

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

  • Posted 27 December 2017, 8:53 pm EST

    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?
  • Replied 28 December 2017, 7:48 pm EST


    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.

    Deepak SharmaSpreadJSGetFormulaSetValue.zip
  • Replied 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?
  • Marked as Answer

    Replied 2 January 2018, 1:02 am EST


    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).

    Deepak Sharma
  • Replied 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 ?
  • Replied 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.

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels