reading from hyperformula when evaluation is suspended

[BrianHung]

From the batching docs:

HyperFormula offers a built-in feature for doing batch operations. This allows you to put multiple CRUD and move operations into a single operation.

However, when trying to read from hyperformula, either through getSheetSerialized or getFillRangeData, I encountered an error

Error: Computations are suspended

  ensureEvaluationIsNotSuspended() {
    if (this._evaluationSuspended) {
      throw new EvaluationSuspendedError();
    }
  }

Is there a workaround for this? I have a general pattern of

writing to hyperformula
results = reading from hyperformula
writing to hyperformula some cells values based on results

[sequba]

@BrianHung Can you provide a code example that reproduces this issue?

[BrianHung]

https://codesandbox.io/s/billowing-monad-obgxg9?file=/src/hyperformulaConfig.js

// Create an empty HyperFormula instance.
const hf = HyperFormula.buildEmpty({
  precisionRounding: 10,
  licenseKey: "gpl-v3"
});

hf.suspendEvaluation();

// Add a new sheet and get its id.
const table1 = hf.addSheet("table1");

// Fill the HyperFormula sheet with data.
hf.setCellContents(
  {
    row: 0,
    col: 0,
    sheet: hf.getSheetId(table1)
  },
  [["='table2'!C1"]]
);

const source = {
  start: { sheet: 0, row: 0, col: 0 },
  end: { sheet: 0, row: 1, col: 1 }
};
const target = {
  start: { sheet: 0, row: 1, col: 1 },
  end: { sheet: 0, row: 3, col: 3 }
};

const fillRange = hf.getFillRangeData(source, target);

hf.setCellContents(source.start, fillRange);

hf.resumeEvaluation();

results in

Computations are suspended

[BrianHung]

Maybe there should be a method called hf.setFillRange that can work when hf is suspended.

[wojciechczerniak]

You should prepare (read data) before suspending evaluation:

// Create an empty HyperFormula instance.
const hf = HyperFormula.buildEmpty({
  precisionRounding: 10,
  licenseKey: "gpl-v3"
});

+const source = {
+  start: { sheet: 0, row: 0, col: 0 },
+  end: { sheet: 0, row: 1, col: 1 }
+};
+const target = {
+  start: { sheet: 0, row: 1, col: 1 },
+  end: { sheet: 0, row: 3, col: 3 }
+};
+
+const fillRange = hf.getFillRangeData(source, target);

hf.suspendEvaluation();

// Add a new sheet and get its id.
const table1 = hf.addSheet("table1");

// Fill the HyperFormula sheet with data.
hf.setCellContents(
  {
    row: 0,
    col: 0,
    sheet: hf.getSheetId(table1)
  },
  [["='table2'!C1"]]
);

-const source = {
-  start: { sheet: 0, row: 0, col: 0 },
-  end: { sheet: 0, row: 1, col: 1 }
-};
-const target = {
-  start: { sheet: 0, row: 1, col: 1 },
-  end: { sheet: 0, row: 3, col: 3 }
-};
-
-const fillRange = hf.getFillRangeData(source, target);

hf.setCellContents(source.start, fillRange);

hf.resumeEvaluation();

If your fillRangeData is based on changed data, then you have to resume evaluation anyway so HF can get fresh eval results.

[BrianHung]

Yeah, I think I'm just over-optimizing. There's no way to way do a cascading autofill (autofill that depends on the results from another autofill) in a single suspended evaluation right?

[wojciechczerniak]

Each HF method applies results immediately. If you have multiple changes, then those can be batched together. But not READ operation. Do you work with DOM? It's a similar idea to layout trashing.

const oldWidth = DOMElement.width;
const oldHeight = DOMElement.height;
const oldPosX = DOMElemenet.left;

DOMElement.width = oldWidht + 100;
DOMElement.height = oldHeight + 200;
DOMElemenet.left = oldPosX + 300;

is more performant than reading and writing at the same time:

const width = DOMElement.width;
DOMElement.width = oldWidht + 100;

const height = DOMElement.height;
DOMElement.height = oldHeight + 200;

const posX = DOMElemenet.left;
DOMElemenet.left = oldPosX + 300;

The moment you read something, you break the whole idea of batching. At its basics batching postpones all the operations that are required to read data (eval is equivalent to layout). The difference is that you have to "trash layout" manually 😉 -> hf.resumeEvaluation().

Yes, it could be possible to return outdated values (before changes are applied), but it would require to store a copy of everything. Which in most cases is not necessary and super easy to do on your own. And IMO would be very confusing to users who could expect that they will get an updated value. Returning an information that you can't read while the evaluation is suspended is rather self explanatory

// reading ops
const oldData = hf.getAllSheetsSerialized();
const fillDataBasedOnOldData = hf.getFillRangeData(source, target);

hf.suspendEvaluation();
// ... do your writing ops
hf.resumeEvaluation();

[BrianHung]

Makes sense; maybe we can change the docs to exclude reading operations from batching.

[sequba]

@wojciechczerniak Thank you for the thorough explanation.

@BrianHung

maybe we can change the docs to exclude reading operations from batching.

I created an issue for that: #1118

[sequba]

@BrianHung We've just released the new version of our documentation with an updated Batch operations guide.