Bug 143225 - EDITING: External references to cells in other files disappear when copying sheet to another workbook, even when directly importing the relevant sheet into the other workbook.
Summary: EDITING: External references to cells in other files disappear when copying s...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.5.0 release
Hardware: Other Linux (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Files-Linked
  Show dependency treegraph
 
Reported: 2021-07-07 01:06 UTC by Nenad Antic
Modified: 2021-08-06 16:03 UTC (History)
1 user (show)

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


Attachments
The file that contains input data (16.96 KB, application/vnd.oasis.opendocument.spreadsheet)
2021-07-07 19:09 UTC, Nenad Antic
Details
A workbook that contains a sheet that imports the data from the input file (24.52 KB, application/vnd.oasis.opendocument.spreadsheet)
2021-07-07 19:12 UTC, Nenad Antic
Details
A workbook where I want the import sheet copied into (64.15 KB, application/vnd.oasis.opendocument.spreadsheet)
2021-07-07 19:15 UTC, Nenad Antic
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Nenad Antic 2021-07-07 01:06:04 UTC
Description:
I created a sheet that fetches data from a different workbook. The sheet does some manipulation and is made flexible so that it is of use in other workbooks when copied into those by changing certain text. However the file references disappear when copied (or moved) using the sheet's tab functionality. 

Trying from the other end to use 'Sheet > Insert Sheet from File...' yields the same bad result. Same thing when using 'Sheet > Insert Sheet...' and choosing the file option.

At one point the file references did not completely disappear, but instead they were all changed into the file path from which I inserted the relevant sheet rather than the original file path to where the information actually is located. But it only happened once and I have not been able to get that behavior again. Mostly the references just disappear altogether.

E.g.:
The sheet that does the import (that I want to use in many places) has a formula like this:
```
=IF(F6>0;'file:///home/nenant/HOST/Dropbox/ekonomi/AB/avtal/Export_GCal_to_GSheet.202106.ods'#$Sheet1.B5;"")
```
When received in the workbook where I want a copy the formula instead ends up like this:
```
=IF(F6>0;;"")
```

Except that one time when instead the file reference had been changed into:
```
=IF(F6>0;'file:///home/nenant/HOST/Dropbox/ekonomi/AB/avtal/kunder/ASIH-Vallentuna/Fakturaunderlag 2021.ASIH Vallentuna.ods'#$Sheet1.B5;"")
```
Which is completely wrong.

The import sheet that is supposed to be reused in other workbooks has MANY external references, ~ 240 (if that has anything to do with it?) 

I am running Libreoffice 7.1.3.2 on Linux (OpenSUSE 15.2) in VMWare Fusion on a Macbook Pro. 
On the Mac host I have Libreoffice 7.0.3.1 and the same set of files do not exhibit this problem.

Steps to Reproduce:
1. Create a sheet with (possibly many) external cell references to a different workbook.
2. Use tab menu to copy the sheet to another workbook, OR...
3. From a third workbook use 'Sheet > Insert Sheet from File...' to copy the the sheet with all the external references.

Actual Results:
External references disappear in the copied sheet

Expected Results:
All external references should remain intact and still point to the correct cells in the workbook containing the relevant data.


Reproducible: Always


User Profile Reset: No


OpenGL enabled: Yes

Additional Info:
I can provide the actual sheets to test the defunct behavior.
-
Version: 7.1.3.2 / LibreOffice Community
Build ID: 10(Build:2)
CPU threads: 5; OS: Linux 5.3; UI render: default; VCL: kf5
Locale: sv-SE (en_US.UTF-8); UI: en-US
Calc: threaded
Comment 1 Nenad Antic 2021-07-07 16:53:32 UTC
Actually it does have the same problem on the Mac host as well in Libreoffice 7.0.3.1.

So it's intemittent and difficult to reproduce every time. But it does occur most of time. Right now e.g. I am not able to copy that sheet with all the necessary references into a different workbook, 240 of them.
Comment 2 Nenad Antic 2021-07-07 19:09:43 UTC
Created attachment 173426 [details]
The file that contains input data

This is the file from where input data is fetched for further manipulation.
Comment 3 Nenad Antic 2021-07-07 19:12:33 UTC
Created attachment 173427 [details]
A workbook that contains a sheet that imports the data from the input file

This a one sheet workbook that only contains a sheet that imports the data from the input file. Normally it would be part of workbook with more sheets.
Comment 4 Nenad Antic 2021-07-07 19:15:21 UTC
Created attachment 173428 [details]
A workbook where I want the import sheet copied into

This is one of the workbooks where I want the import sheet copied into complete with all formulas unaltered and with references intact.
Comment 5 Nenad Antic 2021-07-07 19:25:10 UTC
I now uploaded sample worksheets to demonstrate the bug.

To reproduce:

1. Open the 'importer.q.ods' and make sure it is showing data from the file 'Export_GCal_to_GSheet.202106.redacted.ods'.

2. Open 'Tidsredovisning 2021.Camilla Norén.ods'. This is where I want a copy of the importer sheet.

3. In 'importer.q.ods', right click the sheet tab and choose 'Move or Copy Sheet... '. In the dialog choose to copy the sheet to the other document 'Tidsredovisning 2021.Camilla Norén.ods'. 

4. In 'Tidsredovisning 2021.Camilla Norén.ods' look at the formulas in columns A, B, C and S (as well as G, I, K and M). The reference to workbook 'Export_GCal_to_GSheet.202106.redacted.ods' is gone everywhere.
Comment 6 Buovjaga 2021-07-19 17:09:05 UTC
(In reply to Nenad Antic from comment #5)
> I now uploaded sample worksheets to demonstrate the bug.
> 
> To reproduce:
> 
> 1. Open the 'importer.q.ods' and make sure it is showing data from the file
> 'Export_GCal_to_GSheet.202106.redacted.ods'.
> 
> 2. Open 'Tidsredovisning 2021.Camilla Norén.ods'. This is where I want a
> copy of the importer sheet.
> 
> 3. In 'importer.q.ods', right click the sheet tab and choose 'Move or Copy
> Sheet... '. In the dialog choose to copy the sheet to the other document
> 'Tidsredovisning 2021.Camilla Norén.ods'. 
> 
> 4. In 'Tidsredovisning 2021.Camilla Norén.ods' look at the formulas in
> columns A, B, C and S (as well as G, I, K and M). The reference to workbook
> 'Export_GCal_to_GSheet.202106.redacted.ods' is gone everywhere.

Repro

NixOS
Version: 7.3.0.0.alpha0+ / LibreOffice Community
Build ID: b1df9c67349cf4cc5be4128d797aefb87f50e38f
CPU threads: 16; OS: Linux 5.13; UI render: default; VCL: x11
Locale: fi-FI (fi_FI.UTF-8); UI: en-US
Calc: threaded
Comment 7 Buovjaga 2021-08-06 16:03:55 UTC
Repro with files also in Linux 6.3 and 43all bibisect repos