Volatile functions

8.03K viewsScripting

I’m writing a plugin function which is volatile – repeated calls to it return varying values – but I’m having trouble making this work.

As an example, consider the built-in rand() function. This returns a different value after each call (i.e. it has type “”IO double”” rather than “”double””, to borrow the Haskell convention).

Now if I write a Groovy function:

double groovy_rand() {
return new Random().nextDouble();

Then this behaves as I would expect, in that a 2D matrix with e.g. A1 = groovy_rand() produces a list of random numbers across category B.

But if I write a plugin function:

public class PluginRand extends AbstractQFunction {

public QValueList evaluate(QValueList[] args) {
return QValueFactory.createSingletonValueList(new Random().nextDouble());

public QType[] argumentTypes() {
return new QType[] {};

public QType returnType() {
return QType.cValueType;

then doing the same A1 = pluginrand() just produces a list across B of the same *first* random number.

How can I obtain the Groovy-like volatile behaviour from a plugin?

Many thanks.


Thanks Ben – but still not quite what I’m after. This leads to just one call of evaluate() for a whole block.

Whilst the calculationListener now forces evaluate() to be called again after each model change, each cell in the block is equal to the first random value.

It sounds like there is no way to mark a function as “IO double” (i.e. having side effects) for the optimisation engine to understand that it must call it more than once.

So I think the fix here is to add a dummy “trigger” parameter, which varies per cell: i.e.

A = plugin_rand(@A)

and ignore the trigger parameter.

Ideally I wanted to achieve the same effect via

A = ReturnFirstValueAndIgnoreRest(plugin_rand(), @A)

but the optimisation engine is seemingly very aggressive in that even though the entire formula does depend upon the cell, it “caches” the subexpression that does not.

Any other thoughts on how this might work?

For background on this, the actual project I’m working on is an implementation of the ObjectHandler framework as described by Christian Fries in “Comments on Handling Objects in Spreadsheets” and also implemented commercially by him (in Java) in “Obba” (obba.info).

You are viewing 1 out of 8 answers, click here to view all answers.

Latest Questions