Bug 123974 - Paste into a protected Calc sheet should not paste the cell Protected status
Summary: Paste into a protected Calc sheet should not paste the cell Protected status
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
6.2.1.2 release
Hardware: All All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
: 132829 (view as bug list)
Depends on:
Blocks: Cell-Sheet-Protection
  Show dependency treegraph
 
Reported: 2019-03-09 22:04 UTC by jasonkres
Modified: 2021-09-06 09:20 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments
demo copy cell protected_sheet (7.90 KB, application/vnd.oasis.opendocument.spreadsheet)
2019-03-10 12:05 UTC, Oliver Brinzing
Details
demo copy cell protected sheet (xlsx) (9.13 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2019-03-10 12:11 UTC, Oliver Brinzing
Details

Note You need to log in before you can comment on or make changes to this bug.
Description jasonkres 2019-03-09 22:04:23 UTC
Description:
The interaction of copy and paste with protected cells is very awkward. The ability to copy a protected cell into a protected sheet while the sheet protection is on is important to my users. However, it is surprising that there is only one way to modify the pasted data after doing this -- Undo. If the user does something unintended then saves and re-opens the file, the only way to undo what the program let the user do is unprotect the sheet which the user may not be allowed to do.

See Expected Results for a suggestion on how to improve this without restricting the user.

Steps to Reproduce:
1. Create a new Calc sheet.
2. Put arbitrary data into cell A1.
3. Set cell B1 as NOT Protected through Format Cells > Cell Protection.
4. Tools > Protect Sheet with the default options.
5. Select cell A1 and copy it with Ctrl+C.
3. Select cell B1 and press Ctrl+V to paste.
5. Press Delete to attempt to clear B1.

Actual Results:
The clear of B1 using Delete (as well as by text edit, etc.) is blocked because the protection of cell A1 was copied to B1.

Expected Results:
Suggest that when the destination sheet protection is ON, any pasted cell should automatically be unprotected as part of the paste operation.
To see that the user has the right to do this, this is equivalent to copying the protected cell to a new unprotected sheet, clearing the protection there, and then using copy/paste from the new sheet to the protected sheet.

When the destination sheet protection is OFF, the current behavior to copy and paste the protected state makes sense.



Reproducible: Always


User Profile Reset: No



Additional Info:
Version: 6.2.1.2 (x64)
Build ID: 7bcb35dc3024a62dea0caee87020152d1ee96e71
CPU threads: 4; OS: Windows 10.0; UI render: GL; VCL: win; 
Locale: en-US (en_US); UI-Language: en-US
Calc: CL
Comment 1 Oliver Brinzing 2019-03-10 12:04:17 UTC
(In reply to jasonkres from comment #0)
> The clear of B1 using Delete (as well as by text edit, etc.) is blocked
> because the protection of cell A1 was copied to B1.

i can confirm this behaviour
current workaround: use paste special
 
> Expected Results:
> Suggest that when the destination sheet protection is ON, any pasted cell
> should automatically be unprotected as part of the paste operation.
> To see that the user has the right to do this, this is equivalent to copying
> the protected cell to a new unprotected sheet, clearing the protection
> there, and then using copy/paste from the new sheet to the protected sheet.

this seems to be the same as ms excel 2016 does.
makes sense for me.
Comment 2 Oliver Brinzing 2019-03-10 12:05:25 UTC
Created attachment 149857 [details]
demo copy cell protected_sheet
Comment 3 Oliver Brinzing 2019-03-10 12:11:13 UTC
Created attachment 149858 [details]
demo copy cell protected sheet (xlsx)
Comment 4 Heiko Tietze 2019-03-11 13:13:05 UTC
You set cell protection first via Edit > Cell Protection (applied to selection, on by default) and enable it subsequently per Tools > Protect Sheet... Admittedly a bit weird but the function itself is working well. Learn more at https://help.libreoffice.org/6.2/en-US/text/scalc/guide/cell_protect.html?DbPAR=CALC#bm_id3146119
Comment 5 Oliver Brinzing 2019-03-11 18:13:49 UTC
(In reply to Heiko Tietze from comment #4)
> You set cell protection first via Edit > Cell Protection (applied to
> selection, on by default) and enable it subsequently per Tools > Protect
> Sheet... Admittedly a bit weird but the function itself is working well.

i know, but the way excel does it in case of copy and paste protected cells
into an unprotected area (while the sheet is protected) is much more intuitiv,
cause the user is able to edit the copied range later.
Comment 6 Heiko Tietze 2019-03-11 20:57:56 UTC
(In reply to Oliver Brinzing from comment #5)
> ...the way excel does it in case of copy and paste protected cells
> into an unprotected area (while the sheet is protected) is much more
> intuitiv,

So the request changes to become more Excel-like? Sounds like a safety issue but please elaborate a bit.
Comment 7 jasonkres 2019-03-11 22:26:28 UTC
FYI, here's the actual case that led to this:

1. User is working on a sheet that is protected.
2. User sees a value in a protected cell that they would like to "reuse" in an unprotected cell -- including formatting, perhaps with some minor additional edits to the data.
3. So they Copy the protected cell and Paste it to an unprotected cell. At this point, the paste succeeds and it looks good onscreen.
4. But the user then finds unexpectedly that they cannot further edit (or even Delete) the destination because it has switched to be a protected cell. (Their option at this point is to Undo.)

The reason it is unexpected from the user's point of view is that they think the cell is one that are supposed to be allowed to edit, and they are surprised that they were able to alter protection of the cell from unprotected to protected even though the sheet is currently "locked".
Comment 8 Heiko Tietze 2019-03-12 07:37:07 UTC
(In reply to jasonkres from comment #7)
> FYI, here's the actual case that led to this...

So you expect the protection flag to not get copied when I paste into unprotected cells. Sounds reasonable but OTOH users might expect the number itself to be protected. What do you think, Eike?
Comment 9 Xisco Faulí 2019-03-20 18:34:11 UTC Comment hidden (obsolete)
Comment 10 Eike Rathke 2019-03-21 10:52:43 UTC
At first sight sounds reasonable. But then again, if a protected cell is copied to (or within) a temporarily unprotected sheet and that sheet then is protected again, isn't the cell expected to be protected? I guess whatever we do it will be odd for some users in some cases.

So, removing the protected state only if
a) the sheet is protected, and
b) the target cell is unprotected
looks sensible to me.
Comment 11 Heiko Tietze 2019-03-21 11:18:53 UTC
(In reply to Eike Rathke from comment #10)
> So, removing the protected state only if
> a) the sheet is protected, and
> b) the target cell is unprotected
> looks sensible to me.

Great, let's do it then.
Comment 12 Oliver Brinzing 2019-03-21 17:04:10 UTC
(In reply to Heiko Tietze from comment #11)
> (In reply to Eike Rathke from comment #10)
> > So, removing the protected state only if
> > a) the sheet is protected, and
> > b) the target cell is unprotected
> > looks sensible to me.
> 
> Great, let's do it then.

+1

as long as it only works with ui copy & paste.
IMO the copyRange() API method should not change cell attributes.
Comment 13 m.a.riosv 2020-05-08 21:43:41 UTC
*** Bug 132829 has been marked as a duplicate of this bug. ***