onPaste does not work correctly for Excel modified data after 5.3.2

[tb-viktor]

Hello,

We've encountered a regression after updating from version 5.3.1-alpha1 to latest version of the library - onPaste is no longer parsing modified table data that is copied from desktop Excel correctly if a custom onPaste function is used.

This can be easily checked by doing:

onPaste={(target, values) => {
  console.log(target.values);
  return false;
}}

And copying multi-cell data from the glide table to Excel, modifying it and pasting it back (gif below).

What is returned in values are the original values of the cells instead of the new data that has been copied from Excel.

I've done some debugging and it seems like version 5.3.2 already introduced the breaking change.

When pasted into notepad it shows the correct new values, but when pasted into the table it pastes the original values even if the table was since reloaded or cleared. Very confusing, it's like there's invisible data that gets copied which is equal to the original values?

@jassmith could you take a look?

[tb-viktor]

To reproduce you have to:

1. Enter data similar to screenshotted above into glide table.
2. Select the data and copy it.
3. Paste it into Excel.
4. Change the number values in Excel and copy again.
5. Paste into glide table.

Gif to illustrate:

At the last step we paste the data back into glide, the onPaste values are still the unchanged data for some reason.

[tb-viktor]

I've investigated this further and it seems that desktop Excel adds \r\n at the end of the copied string which seems to cause the paste to not apply:

All of these are e.clipboardData.getData('Text') (values not important). The top one is from desktop Excel, second one is the data copied from glide, and last one is web Excel.

[BrianHung]

Does getting rid of the carriage return work? If so, we can just normalize the data .replace(/\r\n?/g, "\n"). Saw something similar with ProseMirror/prosemirror#1138

We could also maybe just strip trailing carriage return + new lines. Can you try patching this area with that fix and seeing if it works for you?

glide-data-grid/packages/core/src/data-editor/data-editor.tsx

Lines 3525 to 3538 in 5983dca

	if (onPaste === undefined) {
	const cellData = getMangledCellContent(target);
	const rawValue = text ?? data?.map(r => r.map(cb => cb.rawValue).join("\t")).join("\t") ?? "";
	const newVal = pasteToCell(cellData, target, rawValue, undefined);
	if (newVal !== undefined) {
	editList.push(newVal);
	}
	break;
	}
	if (data === undefined) {
	if (text === undefined) return;
	data = unquote(text);
	}

[BrianHung]

Tried reproducing with the desktop Excel app on MacOS; might be an OS thing but the onPasteInternal logic reads from navigator.clipboard instead of e.clipboardData.

[tb-viktor]

@BrianHung Thanks for looking into this! I am on Windows, and most of our clients are too, so maybe that makes a difference in what gets copied? I tried to get the repository running locally to test it, but it's throwing a bunch of errors.

I'll try to explain and show in more detail. In the gifs below I have two things in the console:

1. DataEditor's onPaste: (_, values) => console.log('onPaste values:', values);
2. container div onPaste={(e) => console.log('Event data:', JSON.stringify(e.clipboardData?.getData('Text')))} to show the raw text data

First weird thing, as above, is what happens if you type in some numbers into Glide and then copy them to Desktop Excel, modify them and copy them back. The numbers are unchanged in DataGrid's onPaste, but the raw text data shows the change.

Second even weirder thing is what happens when add some more rows of values in Excel. The first row is again unchanged, while the others are read properly. Even when copying multiple times in different positions, that first row still is "sticky" and does not update values, even though the raw text data is correct.

This makes no sense to me, it's like there's some sort of information in this first copied row that tells glide not to update it, but there is nothing like that in the raw data.