Paste range feature

[MartinDawson]

Hi, sorry to post on this discussion board so much, but I think it's worth raising because currently I have no way around this issue. It's probably because I'm using every single feature of hyperformula..

So currently my spreadsheet has a paste range feature like Google Sheets & Excel.

One example of how it works is like this:

• User copies a single.
• User drags their range selection down and does Ctrl + V (pastes it).

The one cell is duplicated into the selected cells.

Here's a gif showing you what I mean from GSheets:

This is currently not possible in Hyperformula.

Here's the issue in my sheet which uses hyperformula:

The only way to do it is to loop through the destination cells and use hyperformula.paste(cellAddress) one by one.

However this messes up the internal history, so when I do CTRL + Z (Undo) it only undos the pastes individually, one by one.

What should happen is that it should undo the entire range all at once.

So a paste range feature would solve this or a way for me to turn off and on the internal history of hyperformula so that I can implement the paste range for my styles and cells correctly and just make hyperformula undo remove it all at once.

Thank you

[wojciechczerniak]

Batch would create a single Undo step, but Clipboard does not work with batching. So no, for multiple paste calls. You can try develop that has a new method getFillRangeData that will get you calculated content. Sorry for source code link, but docs for 1.0 are not published yet:

hyperformula/src/HyperFormula.ts

Lines 2395 to 2417 in 71677a4

	/**
	* Returns values to fill target range using source range, with properly extending the range using wrap-around heuristic.
	*
	* @param {SimpleCellRange} source of data
	* @param {SimpleCellRange} target range where data is intended to be put
	*
	* @throws [[SheetsNotEqual]] if both ranges are not from the same sheet
	* @throws [[EvaluationSuspendedError]] when the evaluation is suspended
	* @throws [[ExpectedValueOfTypeError]] if source or target are of wrong type
	* @throws [[SheetsNotEqual]] if range provided has distinct sheet numbers for start and end
	*
	* @example
	* ```js
	* const hfInstance = HyperFormula.buildFromArray([[1, '=A1'], ['=$A$1', '2']]);
	*
	* // should return [['2', '=$A$1', '2'], ['=A3', 1, '=C3'], ['2', '=$A$1', '2']]
	* hfInstance.getFillRangeData( {start: {sheet: 0, row: 0, col: 0}}, end: {sheet: 0, row: 1, col: 1}},
	* {start: {sheet: 0, row: 1, col: 1}, end: {sheet: 0, row: 3, col: 3}});
	* ```
	*
	* @category Ranges
	*/
	public getFillRangeData(source: SimpleCellRange, target: SimpleCellRange): RawCellContent[][] {

A sample implementation could look like:

onPaste(source: SimpleCellRange, target: SimpleCellRange) {
  if (hf.isItPossibleToSetCellContents(target)) {
    const data = hf.getFillRangeData(source, target)
    hf.setCellContents(target.start, data)
  }
}

[MartinDawson]

@wojciechczerniak That works perfect. thanks a lot!

It correctly handles formula references and undo/redo history is good

[MartinDawson]

Just saw a slight issue with it. I'm using the example exactly as provided above.

It seems like this is the expected behavior of getFillRangeData though so I'm not sure if we need another parameter or function.

When doing a copy/paste using it it's reversing the order of the copied cells if I'm on a row that isn't on the same nth number as the start of the copy selection.

It seems to be this line that returns the data in a different order than I was expecting:
const data = this.hyperformula.getFillRangeData(source, target);

Here's a visual gif:

So you can see it correctly copies and fills out the cells with wrap-around heuristics but I want to keep the top destination cells to be the same order as the top of the source cells and only wrap the bottom ones, i.e like Google Sheets does.

Because if a user is copy/pasting a range they don't want the order of their copy to be swapped around at the top level. It only should wrap at the bottom of it.

So if I do the copy/paste on the row below it works fine because it's on the same nth level as 54 is:

Here you can see that Google Sheets does wrap-around heuristics but not for the top level for the copy/paste, i.e the 2222 stays on the top level of the pasted values..

So maybe we could do with another parameter to this function that allows us to not wrap the top level?

Thanks

[MartinDawson]

#767 has fixed this issue.

Thanks

[wojciechczerniak]

Oh right, I forgot to reply what we're doing. As you already found out, we've implemented your suggestion. A very good idea @MartinDawson, thanks

[MartinDawson]

No problem, thanks for the fast fixes, really amazing speed and quality by the maintainers here

[MartinDawson]

I just ran into an issue with this @wojciechczerniak:

The docs say this: * @throws [[SheetsNotEqual]] if both ranges are not from the same sheet

So it does not work across different sheets unfortunately which basically means our copy/paste doesn't work across multiple sheets.

Is this just a current limitation with plans to allow it cross-sheets or is it not possible?

[wojciechczerniak]

True. It was created with a specific use case in mind. There are no plans to make it work between sheets, as no one yet mentioned such a use case. Feel free to report a feature request in issues.

@izulin Is it possible to alter the algorithm? Maybe if we get two different sheets we could copy the content first and then apply the same algorithm to fill the range? Preferably in batch to have a single undo action (as @izulin noted, we do not modify data with this method so it can't do that)

[wojciechczerniak]

@MartinDawson We've removed same sheet limitation in #836