Is it possible to set a formula cell's value, overwriting the computed one?

[aboodman]

I looked through the API and I don't see anything for this. I would like to be able to temporarily override the computed value for a cell that has a formula. I realize that volatile formulas will recompute when anything relevant changes.

I realize this may sound like an odd question, but I believe it makes sense for my use case:

I am building a collaborative spreadsheet based on hyperformula. It's easy enough to do this if HF runs only on the client-side. I can make the authoritative server data just be the formulas, no derived values. I just render the spreadsheet on each client.

However this has a few downsides:

1. Because of volatile formulas, or even non-deterministic custom functions, users can end up seeing different spreadsheets and they will never converge, even if they don't make any edits
2. Formulas have to run both on sender client and receiver. For expensive formulas it would be better to run the formulas on the server so that receivers don't have to.

So what I'd like ideally is a way to override the value for a computed cell and let it update normally on next dependency change.

Thanks!

[sequba]

@aboodman Thanks for reaching out to us.

I would like to be able to temporarily override the computed value for a cell that has a formula

I'm not sure what you mean by temporarily. A cell has either a formula or a value. A formula updates whenever its dependencies change (a volatile formula updates on every volatile action). A value remains untouched unless any CRUD operation overrides it (e.g. setCellContents, moveCells, setSheetContent etc.)

I am building a collaborative spreadsheet based on hyperformula. It's easy enough to do this if HF runs only on the client-side

Have you considered running HF only on the server? You can get all the computed values using the getAllSheetsValues method and send them to all the clients on every update.

[aboodman]

Thanks for the reply!

A cell has either a formula or a value.

I was assuming that internally a cell that has a formula also caches its last-computed value. Perhaps that is not the case?

Have you considered running HF only on the server?

That would not work for me because I want it to feel fast to edit the sheet locally too.

More information: I am a developer of https://replicache.dev/, a product that enables realtime collaboration for web apps. One of our users also uses Hyperformula and would like to make their application collaborative. I am trying to help with that. One of the requirements is that all edits feel instant on the client, without server delays. This rules out only running HF server-side.

I can actually meet most requirement today by running HF only on the clients. I sync the values and formulas through Replicache and drive HF as a read-only derivative of the synced state. When a user makes an edit, I edit Replicache, not HF, which immediately updates HF (locally). In the background the Replicache state syncs. On collaborators' clients, Replicache receives the update and those clients response by updating their HF instances too.

This works but has the downside that cells with volatile formulas will diverge from each other on different clients. I would like to avoid that if possible.

If HF did maintain a cached copy of last-computed value for each cell, and it were possible to modify that cache programmatically, then it seems like it would be possible to run HF on the server and treat the server-computed values as authoritative as you suggest. Then when the user edits locally, I can edit HF locally, let it recompute, but when authoritative result comes from server, override the locally computed value if necessary.

[sequba]

If I understand your application correctly, you want to use the server-side instance of HyperFormula as "master data" for data integrity and the client-side instances for speeding up the operations and creating a more responsive feel. In case of any divergence, the server-originated data wins.

cells with volatile formulas will diverge from each other on different clients. I would like to avoid that if possible.

One way of dealing with it, which comes into my mind, is to sync both formulas and the computed values (getAllSheetsSerialized and getAllSheetsValues). You can display the server-computed values in the browser without initializing HyperFormula yet. Then, if a user decides to modify the data, switch to the instance of HyperFormula populated with the formulas. And then, after background syncing is done, switch back to the values received from the server.

I was assuming that internally a cell that has a formula also caches its last-computed value. Perhaps that is not the case?

HyperFormula does not support the modification of the internal cached state of a cell. There is the FormulaCellVertex.cachedCellValue property, but it is not part of our public API. It is not intended to be modified from outside HyperFormula code; using it might cause unexpected bugs and issues with data integrity. Also, it might be changed anytime without introducing a major version of HyperFormula (it wouldn't be considered a breaking change). I do not recommend using it in the implementation of your solution.

However, we might expand our public API and create a feature that would meet your requirements through our consulting services. If you are interested, please contact our sales department.

[aboodman]

If I understand your application correctly, you want to use the server-side instance of HyperFormula as "master data" for data integrity and the client-side instances for speeding up the operations and creating a more responsive feel. In case of any divergence, the server-originated data wins.

Correct.

One way of dealing with it, which comes into my mind, is to sync both formulas and the computed values (getAllSheetsSerialized and getAllSheetsValues). You can display the server-computed values in the browser without initializing HyperFormula yet. Then, if a user decides to modify the data, switch to the instance of HyperFormula populated with the formulas. And then, after background syncing is done, switch back to the values received from the server.

Hm, this is an interesting idea too. Will consider it.

However, we might expand our public API and create a feature that would meet your requirements through our consulting services. If you are interested, please contact our sales department.

It's great to know that's an option. Thanks!

[wojciechczerniak]

⚠️ Not tested idea. Do it at home (but don't blame me).

What may work is to overwrite all volatile functions implementations, there are not that many of them.

Load those new implementation on clients, where only server will have the original real implementation. Our mock implementation for i.e. RAND(), would not generate the value, just read it from an external cache. Each time a volatile action is called it would return the same value, until a request to the server returned new (correct) value and updated it in cache. Simple re-render should then update the view. Or a re-calc will be needed 🤔 May not be the most efficient solution. But re-calc is always there if a new change comes from server, right?

From the user perspective we will avoid FOWC (Flash Of Wrong-calculated Content, I just named it 😆). One flash will be there anyway, wrong data will be displayed until the correct value will be sent from the server. But until then, wrong content is displayed, just without the FOWC. A more advanced mock could return a placeholder, i.e. custom error #LOADING, until this value was overwritten by server. Such error should be propagated to all cells that are depending on volatile value. Depends on the UX we prefer.

[aboodman]

Sorry I may have asked question to wrong person. Not sure if @wojciechczerniak is a developer on this project. Can anyone say whether an instance of non-determinism outside of volatile formula would be considered a bug?

[sequba]

AFAIK all the non-volatile functions are deterministic

@aboodman I just checked the source code. The only calls to Math.random() are in RAND() and RANDBETWEEN() functions.

Can anyone say whether an instance of non-determinism outside of volatile formula would be considered a bug?

Generally yes, but there might be some exceptions to that. One that I can think of right now is some randomized numerical algorithm. Although HyperFormula does not include such an algorithm at the moment I cannot guarantee that for the future.

Also, remember about functions returning the current time: NOW() and TODAY(). They also might cause some divergence between the clients.

[aboodman]

NOW() and TODAY()

I would have expected NOW to be listed under volatile functions, but it's not:

https://hyperformula.handsontable.com/guide/volatile-functions.html#volatile-functions-2

When does NOW recalculate?

[sequba]

I would have expected NOW to be listed under volatile functions, but it's not:

I am sorry. It looks like there is a bug in our documentation. NOW() and TODAY() are in fact volatile. The bug will be fixed in #1056

[wojciechczerniak]

@aboodman no, I'm not a developer for HyperFormula project. My ideas are my own and they may be wrong or deprecated. I was working with HF library a long time ago. Sorry if it looked otherwise.

Just wanted to help, you've got an interesting use case. I would love to read what worked for you, and how you solved it🤞

[sequba]

@aboodman We've just released the new version of our documentation with the updated list of volatile functions.