Description: Description: 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: Version: 7.0.2.2 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
Created attachment 166436 [details] Test files
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.)
I already attached sample documents on bug creation. Those are the ones I reference in "Steps to reproduce".
From https://help.libreoffice.org/latest/en-US/text/scalc/05/02140000.html: 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: 6.2.8.2, 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
With > 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).
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. @Eike: 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().
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() ThisComponent.enableAutomaticCalculation(False) End Sub Adding it to Menu/Tools/Customize/Evens - Save file
A new major release of LibreOffice is available since this bug was reported. Could you please try to reproduce it with the latest version of LibreOffice from https://www.libreoffice.org/download/libreoffice-fresh/ ? I have set the bug's status to 'NEEDINFO'. Please change it back to 'UNCONFIRMED' if the bug is still present in the latest version.
Still reproducible in 7.4.1.2
Using 7.3.4.2 I have made extensive use of INDIRECT() for external file links, including having the INDIRECT() within a Named Expression rather than directly in a cell formula. I have not encountered the error 540 problem outlined in this bug other than as would be expected when link updating has not yet been approved and linked files are not in a trusted location.
(In reply to Eike Rathke from comment #5) > 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 I can confirm this started with 0bdda083c5a7fca62942b3e9bef018d4c8135fe7 (build [4d0632c6583e0f47bd14a122844953f63410b04d] in linux-64-6.2 bibisect repo). Marking as "not a bug" as this was by design as a security measure. Eike, do you have a suggestion for Torge's use case in comment 6? Or if there isn't a satisfactory workaround, what would be a realistic enhancement request?
(sorry Eike, copying you in for my previous comment)
As said in comment 5: 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). i.e. under Tools -> LibreOffice -> Security, Macro Security, Trusted Sources, add a dedicated path to Trusted File Locations. Under Tools -> LibreOffice -> Calc -> General, Update links when opening, choose Always (from trusted locations). Place the _calling_ (the one that contains the INDIRECT() function) document in the trusted source path.
(In reply to Eike Rathke from comment #13) > As said in comment 5: > > 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). > > i.e. under Tools -> LibreOffice -> Security, Macro Security, Trusted > Sources, add a dedicated path to Trusted File Locations. > Under Tools -> LibreOffice -> Calc -> General, Update links when opening, > choose Always (from trusted locations). > Place the _calling_ (the one that contains the INDIRECT() function) document > in the trusted source path. Dears, thank you for taking a look here. As I replied in comment 6 the issue is not the trusty location (BTW: I added the file's location to the proper settings, but nothing improved for my issue). I will try to explain again: The issue is, that with 6.2.x release I was able to open such a file and there seems to be a kind of cached value used (I guess the latest value received when updating the external links the last time). So if I know that nothing was changed in the external file, I was able to work with the opened file. Now I always need to update from external links, otherwise the whole file is unusable. Scenario: 1. File 2024.ods links with INDIRECT() to a cell of file 2023.ods 2. 90% of the calcution in 2024.ods needs this value 3. Opening 2024.ods with current version show "Error: 504" in the cell of 2024.ods where the INDIRECT() function is used 4. Due to that whole calculation in 2024.ods shows errors 5. Updating external links solves the issue and - lets assume - the value 47.11 is received from 2023.ods, displayed in 2024.ods and the whole calculation is done correct 6. Make some changes to 2024.ods, save the file and close 7. Reopening the file 2024.ods, again "Error: 504" With Calc 6.2.x there difference is in step 7 where Calc was still displaying the value 47.11 in the cell where INDIRECT() is used and the calculation in the whole is still the same when saved. So my feeling is there was a kind of caching of the value or backup. Since there are a lot of Calc files involved, with several values retrieved via INDIRECT() updating takes time. Luckily with fast SSDs and CPU it is acceptable. But before the hardware it was a pain. So, please reopen, my issue is still not solved.