Bug 137541 - External links in Calc via INDIRECT() always require an update
Summary: External links in Calc via INDIRECT() always require an update
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: All All
: medium normal
Assignee: Not Assigned
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
Reported: 2020-10-16 17:49 UTC by Torge Riedel
Modified: 2020-12-07 00:14 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:

Test files (14.73 KB, application/gzip)
2020-10-16 17:51 UTC, Torge Riedel

Note You need to log in before you can comment on or make changes to this bug.
Description Torge Riedel 2020-10-16 17:49:57 UTC
As a regression to 6.2.x stable releases cells with function INDIRECT() and manual activation of external content is set in the options an "Error:540" is shown in the cell, instead of the last correct fetched value.

Steps to Reproduce:
1. Set option "Update external links on request" in Calc
2. Add the location "/tmp" as trusted location
3. Extract the attached archive to /tmp
4. Open file Test_02.ods in Calc
5. It shows you the "Error:450" in cells B3 and B4
6. The update of the external links is request by calc
7. Manual update the external links, now "Test" is shown in B3 and B4
8. Close and save the file
9. Reopen the file Test_02.ods, it says "Error:450" again

Actual Results:
Even after saving a calc file after updating the external links and correct values are displayed, there is an "Error:540" when reoping the file.

Expected Results:
The spreadsheet document must remember the last value of a successful update of an external link. This worked in 6.2.x

Reproducible: Always

User Profile Reset: Yes

OpenGL enabled: Yes

Additional Info:
Build ID: 00(Build:2)
CPU threads: 6; OS: Linux 5.4; UI render: default; VCL: gtk3
Locale: de-DE (de_DE.UTF-8); UI: de-DE
Ubuntu package version: 1:7.0.2_rc2-0ubuntu0.20.04.2
Calc: threaded
Comment 1 Torge Riedel 2020-10-16 17:51:36 UTC
Created attachment 166436 [details]
Test files
Comment 2 Xisco Faulí 2020-10-22 15:01:06 UTC
Thank you for reporting the bug. Please attach a sample document, as this makes it easier for us to verify the bug. 
I have set the bug's status to 'NEEDINFO'. Please change it back to 'UNCONFIRMED' once the requested document is provided.
(Please note that the attachment will be public, remove any sensitive information before attaching it. 
See https://wiki.documentfoundation.org/QA/FAQ#How_can_I_eliminate_confidential_data_from_a_sample_document.3F for help on how to do so.)
Comment 3 Torge Riedel 2020-10-23 06:33:41 UTC
I already attached sample documents on bug creation. Those are the ones I reference in "Steps to reproduce".
Comment 4 [REDACTED] 2020-10-25 10:20:52 UTC


540 -External content disabled - Happens if a function that requires (re)loading of external sources is encountered and the user hasn't confirmed reloading of external sources yet. 

I would read the "Actual Result" as intended behavior. And in contrary to the statement "This worked in 6.2.x" the behavior is the same on my system using:
Version:, Build ID: f82ddfca21ebc1e222a662a32b25c0c9d20169ee
CPU threads: 8; OS: Linux 5.3; UI render: default; VCL: kde5; 
Locale: en-US (en_US.UTF-8); UI-Language: en-US, Calc: threaded
Comment 5 Eike Rathke 2020-10-26 23:29:58 UTC
> 1. Set option "Update external links on request" in Calc
it behaves as set. The INDIRECT() function is a volatile function that needs to be recalculated, so until the infobar's "Allow updating" button is activated the result of obtaining uncached external data is Err:540.

This behaviour is a security measurement and was implemented for 6.2.6, which may explain the contradictory "it works / does not work" in 6.2.x

Data is uncached here because the INDIRECT() function, contrary to direct external references, does not add its results to cached data tables saved with the document.

If unconditional loading of external resources is wanted then instead of updating on request use the "Always (from trusted locations)" setting. The requesting document has to reside in a trusted location, so in this case Test_02.ods (and Test_03.ods).
Comment 6 Torge Riedel 2020-11-15 10:10:43 UTC
Thanks for your answers.

Let me explain my scenario a bit more:

I have several Calc files each of them contains data for a specific year (2020, 2019, 2018, ...). They are all linked, cause sometimes data needs to be adjusted in - let's say 2018 - so Calc files for 2019 and 2020 needs to updated. In this case I opened the files 2019 & 2020, clicked on "Refresh / Update" and the whole file is updated with the new data from the links. Afterwards I saved them.

If there were no changes in older files I was able to open the file for 2020 without the need to click on "Refresh / Update" and work with that data.

This was possible as long as I used a 6.2.x version and it could be that it was not the latest patch version, but the one before the changes were implemented.

Now, when I open the file for 2020 it is flooded with "540" errors and I always have to "Refresh / Update", otherwise it is impossible to work with the file. And the refresh takes a while. There are 10 files involved.

Several years ago I used the function DDE(), but switched to INDIRECT() cause it is (was) more flexible. Now it is unusable for me.

You mentioned "..., contrary to direct external references, ...": Is there another way to link data from the files including caching?

The challenge is: I have lots of external references in the file and when a new year starts I save 2020 file as 2021 and with INDIRECT() I only need to change the value of one cell in the table to switch links from 2019 to 2020. I fear this is not possible without INDIRECT().
Comment 7 m.a.riosv 2020-12-06 23:34:32 UTC
Hi @Eike, would be nice some way to avoid this. The better I think could be a nonvolatile INDIRECT, also it could help to make less slow spreadsheets.

It's an annoying situation, and it doesn't allow even to take a view to the spreadsheet without update the links, and when update take several minutes or linked files are not available, it's really annoying, and gives a very bad impression to persons who don't know the issue or don't have access to the linked files, making unusable this files without the linked files.

The only solution that I have found it's to disable Autocalculate before save.

Here is a macro to do it.
Sub DesactivarAutocalculo()
   End Sub
Adding it to Menu/Tools/Customize/Evens - Save file