how to retain formula?

I am working on multiple sheets, here all my sheets are dynamic. Everything on sheet is coming from database. Now my problem starts here. I am saving formula result into database. when I save my sheet and reload , it brings result of the formula to cell, i want formula there as it is.Is there anyway I can do it?



  • edited July 29

    As far as I know, there is no method to disable the native formula behaviour, that is, it will always count the value of the cell if it has a = sign at the start. However, you could alter the cell string to make a formula "invalid", so it would just display the formula as a string.

    For example, you could add a space before the = sign to retain formula:

    Another way to do this would be to alter the string by removing the = entirely and replacing it with = in the cell template of the given cell range:

    Either way, please don't forget to modify your data back to the original state if you do need it.

  • Actually, this not what i am looking for. Can we get whole spreadsheet data as a string?

  • You can get all of the spreadsheet data as a string this way - JSON.stringify(spreadsheet.serialize({sheets:true})). Here's an example:

  • yes this works for me, and how this json object we can use as a spreadsheet data again. I mean I am saving this json data into database, Now I want to show this to spreadsheet again

  • edited July 31

    You can simply use load method, which will send the request and parse the data into your spreadsheet, the parser works just fine with JSON strings and should have no issues just using the load method. Here's an example where I've hosted some sample JSON data and tried to load it back into the spreadsheet:

    You can read more about spreadsheet dynamic data loading here -, the article also lists some additional methods you could use.

  • I am using same API to load data, It returns me blank spreadsheet.

  • If I change any cell value then it should be updated in json JSON.stringify(spreadsheet.serialize({sheets:true})) , but it gives previous value not the updated one.

  • edited August 7

    Works just fine for me, try changing any values: You have to call the method each time you need to get the updated data.

Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!