Automatically update when referenced cells change for custom javascript functions

[MaPePeR]

I'm experimenting with jexcel and am very impressed so far, one thing that I found very hard to do was to write a jexcel-javascript function like in the footer example. I noticed, that this formula only works in the footer and not in the body of the table. It is not automatically updated, when the values in the column change. (Is this considered a bug?)

I think it would be great to have a helper-function that allows such custom javascript formulas to communicate their dependent cells. (Maybe that already exists? If I add them to instance.jexcel.formulas during the evaluation of the function I have no real way of cleaning them up, when the cell is changed? Or does that happen automatically?) I resorted to manually filling the instance.jexcel.formulas in my updateTable-Function for now, but that doesn't feel like a good solution.

One way around this, of course, is to write the functions in a way, that they accept cell-ranges, but that doesn't work, if you want to calculate the sum of a whole column when there might be new rows added, because the A3:A-syntax is not supported. (And also is not easily implemented, because the formulas-Array would also need to be updated, so one would have to keep track of all the open ranges that are in the spreadsheet?)

Edit: It would be great, if the getValue and getValueFromCoords would automatically be tracked as a dependency - for example.

[GBonnaire]

Hello,

In fact, you have cell linked only when it's used on formula direct reference (A1, A2:B3,etc). But if you use référence indirect like (COLUMN(1), VALUE(1,1), ...) is not linked.

for getValue, getValueFromCoords it's used in many case, it's not better solution. I have suggest to @paulhodel maybe to change formula VALUE(), COLUMN, ROW for add linked automaticaly but, you have an other problem, if coordonnate depend dyna value, it's not possible. It's an hard problem to solve.

But, all suggest to Paul can help for create this linked.

[MaPePeR]

I don't see how it would be a problem if the coordinate depends on the value?

Example, assuming VALUE would capture dependencies:

	A	B	C
1	2	=VALUE(COLUMN(), ROW() + A1)
2		1
3		2

At first the Cell B1 depends on A1 and B3. If A1 is changed B1 will be evaluated again and the new dependency will be automatically be calculated. If B3 is changed B1 will be evaluated again and the dependencies will not change.

Not sure if reusing getValueFromCoords for this "get value and catch dependencies" is a good idea, but there could easily be another parameter to control that behavior or a different method for that purpose. You just need to have some code that says "now we are capturing dependencies for cell XY". The current method of "only capture dependencies for explicitly named cells" could also just use that mechanic then.

[hodeware]

That is a limitation in regards to the formula chain. It is created based on actual variables, and a reference like COLUMN() is not tracked yet. But, that is a good suggestion we should consider for the next releases. I will tag it as a feature request.

Meanwhile, you need to use something more like =SUM(B2:B3); //

[Alex-Monahan]

This feature would also benefit us! I think our users will want to make formulas that are the same for an entire column, and these kinds of special formulas allow that to work much more smoothly.