I used to have Lo 4.4.7 until yesterday. It worked great, but I was advised to upgrade to 5.1. I instantly found that it demanded I save the file even though I had not amended anything. It also gave #VALUE! errors for all the xmlfliter(webservice cells. But the formula wizard shows correct values for all cells. I threw that version in the bin and did another upgrade to 6.0.5.2 as it advertised somewhere it had fixed a shedload of bugs. Well not this one. The sheet is about 7MB, and it needs a "latest.xls" in the same directory. https://www.dropbox.com/s/puhkre5lqsdlbpf/FakeMembers.ods?dl=1 https://www.dropbox.com/s/tfejjho86z8laqw/latest.xls?dl=1 All names & addresses are fakes. It used to be that you could open the spreadsheet, and it would not need to update its link, as it would remember from the previous session. No longer. I been using this spreadsheet for a couple of years now, with no problem up until the upgrade.
Thank you for filing this. First of all: please always file one bug/request per issue. Here, you seem to mention two different issues: 1. A document being marked as modified on load; 2. Formulas with URI functions [1] incorrectly returning #VALUE. I cannot reproduce #2 using Version: 6.1.0.1 (x64) Build ID: 378e26bd4f22a135cef5fa17afd5d4171d8da21a CPU threads: 4; OS: Windows 10.0; UI render: default; Locale: ru-RU (ru_RU); Calc: CL. Or, rather, kind of reproduce: when opening the document, I do see those "VALUE"s in columns AW-AZ; but also I see the infobanner telling "Automatic update of external links has been disabled."; and pressing "Enable Content" there, I get the formulas recalculated and #VALUEs becoming normal results. I suspect that the behaviour change here is related to the recent fixes of [2], and this being the correct behaviour, not a bug. I do reproduce issue #1 opening the document. I don't confirm this yet, though, until you decide how to proceed here: either you let it handle the one reproducible; or split it to two; or something else. I set it to NEEDINFO until it is clear which issue to track here. Please set back to UNCONFIRMED when made requested changes. Thanks! [1] https://help.libreoffice.org/latest/en-US/text/scalc/01/func_webservice.html [2] https://www.libreoffice.org/about-us/security/advisories/cve-2018-6871/
Created attachment 143527 [details] The original document from comment 0 to avoid dependency on external storage
Created attachment 143528 [details] The original supplementary document from comment 0 to avoid dependency on external storage
I believe the two errors to be linked to the same bug. Now open it a second time. The info is already in the spreadsheet, so it should not come up value again, and the spreadsheet should not ask for save.
(In reply to Fitch from comment #4) > I believe the two errors to be linked to the same bug. It doesn't matter what your beliefs are. There is a strict rule: one bug per issue. It should be followed, or there won't be possible to track what is to fix, what is fixed, etc. If you don't want to comply, it's your right to ignore whatever rules you want. Just it's unlikely that such a bug will be handled. > Now open it a second time. It looks like you didn't read what I asked you. I asked you to set it to UNCONFIRMED. You "confirmed" it by setting to improper "REOPENED" (as if it was closed sometime!). It is not confirmed until someone of QA decides it's in a good shape to be NEW. > The info is already in the spreadsheet, so it should not come up value > again, and the spreadsheet should not ask for save. You are welcome to structure the two issues separately. You could not know that they are different; but now you have been told they are, so no matter how you feel about that, they are different, and must be tracked separately. It is still not in a shape to be confirmed, and still needs to be split. Setting to NEEDINFO again. Please don't set it to arbitrary statuses, however unclear to you could you deem the rules here. They exist for a reason. Thank you.
O.K. Forget the save bug. The #VALUE! bug is more concerning. Don't know how to edit this.
(In reply to Fitch from comment #4) > The info is already in the spreadsheet, so it should not come up value > again (In reply to Fitch from comment #6) > The #VALUE! bug is more concerning. The info is not already in the *original* spreadsheet. The XML inside the ODS has these records: > <table:table-cell table:style-name="ce202" table:formula="of:=IF([.AR2]="";"";COM.MICROSOFT.FILTERXML(COM.MICROSOFT.WEBSERVICE("http://www.geopostcode.org.uk/api/"&[.AR2]&"");"//osgb36/north"))" office:value-type="string" office:string-value="" calcext:value-type="error"><text:p>#VALUE!</text:p></table:table-cell> But I *do* reproduce if I enable content (allowing to fetch from WEB), and re-save the normal values. Then the file has this: > <table:table-cell table:style-name="ce202" table:formula="of:=IF([.AR2]="";"";COM.MICROSOFT.FILTERXML(COM.MICROSOFT.WEBSERVICE("http://www.geopostcode.org.uk/api/"&[.AR2]&"");"//osgb36/east"))" office:value-type="string" office:string-value="437871" calcext:value-type="string"><text:p>437871</text:p></table:table-cell> but still opening it in Calc brings the #VALUE!. So the problem is that Calc should show the cached values for the WEBSERVICE formulas, but shows #VALUE! instead. Setting to NEW.
Created attachment 143569 [details] Minimal reproducer The file contains a single formula in A1: > =WEBSERVICE("http://www.geopostcode.org.uk/api/") It contains the cached value in the content.xml, but opens with #VALUE! in the cell. By the way, this doesn't make the document modified on open.
Comment on attachment 143569 [details] Minimal reproducer Don't you need to put in a postcode? e.g. =WEBSERVICE("http://www.geopostcode.org.uk/api/EH9 1QG")
(In reply to Fitch from comment #9) > Comment on attachment 143569 [details] > Minimal reproducer > > Don't you need to put in a postcode? > e.g. =WEBSERVICE("http://www.geopostcode.org.uk/api/EH9 1QG") For the reproducer, it's irrelevant. The function just returns an HTML; and the address without the code returns some HTML as well. Both variants will serve good reproducers (just a side note: the URL with spaces is illegal, and it's pity the service is taking that; fortunately, it also takes properly encoded URL like > =WEBSERVICE("http://www.geopostcode.org.uk/api/"&ENCODEURL("EH9 1QG"))
Another note: saving the reproducer as DOCX does not gives #VALUE!, it correctly restores the cached value.
As expected, the behaviour changed in https://cgit.freedesktop.org/libreoffice/core/commit/?id=93ea7cb6b5ab3c9b964b2b38e8f4a3bde71dbadf @Eike, @Caolán, should Calc use the cached data if the user doesn't enable the content from external source ?
Whatever sets the formula cell dirty, maybe it shouldn't..
Actually this now works as it should.. Just hit the Enable Content button on the Info Bar when asked (after having inspected the links under Edit -> Links to External File... for documents of unknown source). To have WEBSERVICE() recalculated without #VALUE! error as it was before the version that started to question the origin, put the document in a trusted location, see Tools -> Options -> LibreOffice -> Security, Macro Security, Trusted Sources (you may have to add a directory if you didn't before); and then set Tools -> Options -> Calc -> General, Updating, Update links when opening, to "Always (from trusted locations)". However, I agree that the cached result could be used at least in some cases, where WEBSERVICE() and a literal argument is the only formula expression in a cell. Not dirty/recalculating is one thing, but then the URI isn't propagated to the link manager and thus can't be inspected either for the "Automatic update of external links has been disabled." message, so this will need more work.
(In reply to Eike Rathke from comment #14) > However, I agree that the cached result could be used at least in some > cases, where WEBSERVICE() and a literal argument is the only formula > expression in a cell. Doing so would not help in the attached sample document because the WEBSERVICE() calls there are embedded in a formula expression. So this specific case will not benefit from a change. Btw, duplicating the actual query for each record in columns AW and AX is a waste of resources, that should be done in one cell and then the different FILTERXML() calls reference that cell instead.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=d2e5283f0caeb87e316040e2db1aade8cc408f62 Related: tdf#118735 WEBSERVICE preserve the hybrid string result, if possible It will be available in 6.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=da9a6f426b61937d9cda17f5be3f82d2e15e6ecd Related: tdf#118735 introduce FormulaError::LinkFormulaNeedingCheck (Err:540) It will be available in 6.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
As already implied this fix was never going to work for an embedded formula, and, of course, it didn't. I deleted all the postcodes that came up in the Edit > Links window, pressed F9, and the data came up as it should, very quickly. The problem is, the postcodes reappeared in the Links window, set to manual, and on closing the file and re-opening, they had set themselves back to unavailable. Surely on the links window, only the file should be there, and not every postcode qeried?
(In reply to Fitch from comment #18) > The problem is, the postcodes reappeared in the Links window, set to manual, > and on closing the file and re-opening, they had set themselves back to > unavailable. If you want them to be recalculated without the error: see comment 14 and the trusted location, or hit Yes (6.0.6) to update or (6.1 and later) Enable Content button. > Surely on the links window, only the file should be there, and not every > postcode qeried? Every external resource or URI queried is expected to be listed there.
Hello! I used Libreoffice Calc 6.1.0.3 Portable (in Romanian language) for Windows. I confirm bug with menu Edit -> Links to External Files ...-> window Edit links -> Address External Data -> the list tables/range is EMPTY. Don't update the cells with the external data with URLs and don't work "Enable Content" for "Automatic update of external links has been disabled". In Libreoffice Calc 6.0.3.2 for Linux (in Romanian language), it's work External date with URLs. Please fix this bug. Thank you for understand. Best regards, Dorian Baciu
I tried the patch, but it was useless, as, as advertised, it would not work on calls embedded in a formula expression (whereas it previously did). I have now dropped LibreOffice, as it is no longer fit for purpose.
@Fitch: Read comment 19 and comment 14 about putting the document in a trusted location.
I did everything suggested in comments 14 & 19. I stopped the project because the people I send the spreadsheet to, for their own lists do not have the wherewithal, or the wish to delve into Libre Calc that deeply, and create "safe" folders.
And it still didn't work....
Dear Eike Rathke, This bug has been in ASSIGNED status for more than 3 months without any activity. Resetting it to NEW. Please assigned it back to yourself if you're still working on this.
Dear Fitch, To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the information from Help - About LibreOffice. If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice. Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from https://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to 'inherited from OOo'; 4b. If the bug was not present in 3.3 - add 'regression' to keyword Feel free to come ask questions or to say hello in our QA chat: https://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug