Some questions on sheets with lots of complex formulas and API

Tags: #<Tag:0x00007f8b1d28af78>
#1

we have a large project in handsontable that we are building in reactjs. Here’s a small code snippet - http://pastebin.com/fjbb59CF

This spreadsheet has a simple usecase :

  1. compute some values when you are editing/adding the sheet. Show changed rows in a red color (“changedValueRenderer”)
  2. sync the data to server
  3. when you get new data from server - dont compute, just take the data as it is . Server is authoritative.

Line number 147 shows how we are using the “afterChange” hook to apply formulas. Is this the right way to execute this ?
We thought about using ruleJS, but were just not able to get it to work in the right way … especially with npm and ES6. How are people building these things ? We really need some help on this.

#2

hi guys
any idea on what to do with this ? would really appreciate some help

#3

Hi @sss
we would be happy to help with this but the support services are a part of our commercial offer. The basic support includes help in all the issues connected with Handsontable (like preparing recipes for typical use cases, fixing bugs, etc.), but doesn’t cover development on-demand like custom integrations, implementations in specific systems etc.

Would you like me to share more details?

#4

hi alexsandra,
thanks for replying.
I am a little surprised to be honest - because the difference between my particular question and “preparing recipes for typical use cases” is not clear. As you might be aware, there is already a very vibrant google group (https://groups.google.com/forum/#!forum/handsontable) that will answer basic questions and howto.

My question is not around custom integrations - however if you feel that any question related to implementation needs commercial offer then please do share the details here. I would also request to make the distinction very clear on the web page because a lot of colleagues who use Handsontable are also interested in your Pro offering.

At the very end, I would like to request to utilize my Pro membership fee towards your commercial offer (and block my account after that). As you can see, I’m already an advanced user of Handsontable (and a long time member of the Google Group) and all basic questions are already answered so for me your Pro membership is not very useful.

You guys do awesome work on Handsontable and I would really love to contribute back - however my need is a very specific usecase and I woul like to apply any paid services towards that.
thanks!

#5

Hi Sandeep(@sss),

I think that @aleksandra_budnik wanted to turn your attention to the fact that not everything can be done within a basic forum support - sometimes we need to dig deeper and provide a custom development on the client’s side. Situations like this require from us to charge our regular rate of $60/hour. However, if you feel that our basic support is not sufficient for you and would like to revoke your Pro license then please write us directly at support@handsontable.com and I will personally make the return transfer right away. The ruleJS integration is fully open sourced so you don’t need be Pro to make use of that.

Anyway, I’m pretty sure that we can help you somehow. Referring to your first message, the ruleJS is our library computing functions on the front-end side therefore we don’t have much experience with implementing it on the back-end side. Also, it is still an experimental feature as we’ve stated here. We would like to make it better so any suggestions on adding new hooks/callbacks are appreciated.

Answering your question from the first message, currently there’re no recipes on how to implement ruleJS using ES6, ReactJS or your specific back-end side code. I only hope that somehow from this Forum, Google Group, Stackoverflow or GitHub will be able to help you better.

#6

hi @chris thanks for replying.
First - no , I am not looking for a refund. I really respect the work you guys are doing and would like to support you - whether in the form of a Pro subscription or (as I had wondered) as a consulting fee.

Second, I think there is a misunderstanding of what my question is and I think this is why people seem to be mistaking this as a custom development. I have no backend code based on handsontable. This is all front end code. Let me try to rephrase my question:

  1. We DONT use RuleJS. As you mentioned - it has proved to be very hard to work with.
  2. We have LOTS of formulas (about 80) in our Handsontable spreadsheet. this is actually our first question - what is the recommended way of implementing this ? Currently all the formulas are executed on afterChange hook. Is this the right way to do it?
  3. We update an API with all the rows that were added/changed. We do this by updating a List datastructure with all changed/rows and calling an API. This also happens in the same afterchange hook. This is my second question - is this the right way to do it ?
#7

Then yes, sorry, I might misunderstood you at first. I think that the afterChange hook is the one we’d use ourselves in this case. However, as you are already aware of, formula computing is a very complex process if we don’t want to end up waiting hours for the result in a relatively big spreadsheets. That’s why we want formula support to be a priority project in Q2 as stated in our roadmap: https://trello.com/b/PztR4hpj/handsontable-roadmap-2016. Stay tuned.

As for your third suggestion, I think that @jsiegel will be of help here. Can we make a quick of review of a method suggested by @sss on how to listen to changes and update the sheet, @jsiegel?

#8

@sss, I think using the afterChange hook is a good idea in most cases, as it’s the most versatile change-related hook. It fires, as it’s name states, after every change made to the cells.
Of course, it has it’s good and bad sides: The good side is, you’ll have full control of the changes made to the table, but it may also be a problem, when it fires even after changes irrelevant to the calculations you’re doing.

If I understand your setup correctly, I’d use the afterChange hook with a function which stops the callback execution every time the change is not relevant.
You may also find these hooks useful:

Side note: you can have more control over the afterChange hook by utilizing the source argument. It tells you more about the nature of the change, it’s possible values include:

alter, empty, edit, populateFromArray, loadData, autofill, paste.

Hope this helps!

#9

@jsiegel @chris thanks for your responses.

Couple of things to think about as you re-engineer formula support.

  1. The largest use case of a spreadsheet is to use formulas and change data. So the “afterCreate” triggers are not very important. The afterChange hook actually tells us about a creation event and most importantly - recompute formulas based on data changes.
  2. a true spreadsheet will only trigger computation for cells in the dependency graph . That is the #1 usecase you should plan for. Effectively (in my mind), it will mean afterChange hooks for each cell which only gets invoked if the dependencies are met.
  3. Please tackle ruleJs later. We can live with pure javascript functions (preferably using lodash) if the dependency graph works well!
1 Like
#10

@jsiegel just a theoretical question - we have been thinking about building visualizations using Handsontable and Crossfilter (Square’s high performance js based dataset library for filtering and aggregations https://github.com/crossfilter/crossfilter and https://github.com/esjewett/reductio).

The super spectacular dc.js library (https://dc-js.github.io/dc.js/) uses Crossfilter to build out these amazing graphs and visualization without having to write any code. Do you think it makes sense for you guys to look at Crossfilter as the underlying data structure for Handsontable ? This lets you get all the filtering and aggregation capabilities for free (and Square has used it for a million rows in memory)

1 Like
#11

@sss, Thanks for for the suggestions, we’ll take them into consideration when developing the formula support!

#13