Open martindemello opened 1 year ago
dsagal
commented
1 year ago Compared to how Grist works today (calculations happen in formulas, immediately after entering values), what are the differences of behavior you are going for? Is it mainly about calculations that have to happen on your server (because they use external data, for example), or is it mainly about delaying calculations until a "Calculate" button is clicked, or something else?
martindemello
commented
1 year ago it's mainly about calculations of the form f(sheet1, sheet2, sheet3) -> sheet4 as opposed to f(cells) -> cell, as well as wanting to depend on stuff outslde of the stdlib, and organise the code that does the calculations into python (or javascript if that's better supported) modules.
dsagal
commented
1 year ago It makes a lot of sense to support functions that return a table of data (rather than just a cell). We would like that too. We don't have a clear plan for how exactly it would work, but we are open to hearing proposals.
Meanwhile, one way to do everything in-process is using Custom Widgets (https://support.getgrist.com/widget-custom/). These allow you to create a static HTML + Javascript page that's loaded as a widget within a Grist page and can communicate with the rest of the document. That page can have a "Calculate" button, and when it's clicked, JS can fetch data, process it, and write data back to the document.
It's similar to what you are described with API calls (which can work too), but with a Custom Widget, there is no need for API keys, since the code in the Custom Widget can communicate with the document it's loaded into.
martindemello
commented
1 year ago custom widgets do sound like the way to go, thanks! i'll explore that option.
as for functions returning a table, one option might be to introduce the concept of an "output sheet", a read-only sheet whose entire contents are driven by a single formula. the formula would have to return data in the form [(row, column, value)], or perhaps (range, 2d matrix) and the sheet would update itself whenever the formula is triggered. (one subtlety would be to allow formatting edits on the sheet, but not value edits)
martindemello
commented
1 year ago I played about with custom widgets a bit, and they sort of worked but they are not quite what I need, I think. But having written some code I have a clearer idea of exactly what I would like out of grist. Let me describe the problem a bit more, in the hope that you can give me some suggestions for how to do this with grist (or let me know if it goes against the grain too much):
The app is a tournament manager; it has a form where users can enter the results of a match, and it calculates various statistics and displays the current state of the tournament and matchups for future rounds. There are three classes of table:
I would love to use grist as the admin interface to this - a tournament director should be able to use a grist-based interface to populate all the settings tables, and fix errors in the results table if necessary. I also want some sort of automatic dataflow where when a new result is entered, all the output tables get recalculated. This needs to happen on the server side, because the data should update whether or not someone has the dashboards open in a browser. And finally the aforementioned "calculate" button, which is a way for the admin to force a recalculation of all the output tables in case a trigger fails for whatever reason.
I was able to build the admin views with grist and it was a wonderful experience, I got a nice neat interface with practically no work, and I got collaborative editing out of the box. I then tried building a custom widget to recalculate standings whenever a new row got added to the results table, but it seemed quite fragile; javascript errors got swallowed silently on occasion, and every now and then a trigger would fail and I would have to reload the page to get the data to refresh. Also every time I opened a new view onto the standings widget it would recalculate everything from scratch, including a perceptible delay while it fetched the data from the settings and results sheets and repopulated the view.
I feel like if I can just get the calculations working on the server, I can write a second app that displays the output views by reading the grist sqlite tables, and not have to shoehorn that into grist if it's not suited for it.
dsagal
commented
1 year ago Interesting! Before we go too far down this path, let me share this tournament example:
https://public.getgrist.com/bBAoPmo8hc4X/NCAA-March-Madness-2022/m/fork
It was actually made as a cute application of conditional styles, but the reason I am sharing here is that it calculates the state of the bracket using regular formulas (although they do get someone tricky/unintuitive; you can see them all here).
Regular Grist formulas are a pretty good fit for your use-case because they update whenever there is any change to the data, so all calculations will be up-to-date. (All this is not to dismiss the need for table-valued functions, just that if you can avoid them for your use-case, things may work much smoother out of the box.)
I couldn't quite make out from the docs if this is a supported use case: I would like to build an app whose frontend is a set of collaborative spreadsheets, but with all calculations happening on the backend. Ideally I would like users to enter data in a sheet, click "calculate", and have new pages generated with the results of various calculations on that data.
One way I can see to do it is to have a second webapp that embeds a view from grist as part of its frontend, hosts the "calculate" button itself, and does the calculations and then makes api calls to create a second page in grist with the resultant data. I was hoping to be able to do everything in-process though.