=WEBSERVICE() neither updated automatically nor updated manually

User Support Calc
1

In my environment where I am running the latest collabora docker container, the WEBSERVIC() seems to neither be updated when loading the spreadsheet, nor are you able to update it manually. If clicking on a field containing:

=NUMBERVALUE(FILTERXML(WEBSERVICE(“https://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml"),"//*[local-name()=‘Cube’ and @currency='” & SEK & “']/@rate”),“.”,“'”)

it will give “Error: 540”. However If you load the same file into LibreOffice and doing “Edit → Links to external files → Update” it will update (after accepting the security warning).

How am I to get the WEBSERVICE() function to also work in Collabora?

2

Hey @dec0de,

Welcome to the forum + thanks for posting! :slight_smile:

In my environment where I am running the latest collabora docker containe

“Latest collabora” could mean lots of things!

Could you give us a little more info?

  • Exact OS
  • Exact Collabora version
    • (Help > About)

and since you have it working in LO, then that info too:

  • Help > About LibreOffice

=NUMBERVALUE(FILTERXML(WEBSERVICE(“https://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml"),"//*[local-name()=‘Cube’ and @currency=‘” & SEK & “’]/@rate”),“.”,“'”)

Hmmm…

  • Could you attach a sample ODS document that’s working in LibreOffice?

I think that might make it easier to debug this.

(Right now, I’m getting ERR:501 in LibreOffice 7.5.3. Maybe it’s a copy/paste + forum markdown issue.)

Can you explain a little what this equation + NUMBERVALUE + FILTERXML is trying to accomplish?

Maybe there’s an alternate way of tackling this problem too.

Side Note: Looking up Error Codes in LibreOffice Calc:

it also lists

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.

And I got a:

501 Invalid character Character in a formula is not valid.

which makes me think I may have copied/pasted something wrong.

It seems like in LibreOffice, you may need to “Enable” this after opening a document with such a formula inside:

Unsure how Collabora handles it (or if the Docker container makes this popup never appear).

Once we get a good sample ODS though, I think that’ll help figure this out though. :slight_smile:

3

First, sorry missing out on informaion and for the formula not being correct, this is a working one in libre office:

=NUMBERVALUE(FILTERXML(WEBSERVICE(“https://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml");"//*[local-name()=‘Cube’ and @currency='” & “SEK” & “']/@rate”);“.”;“'”)

This is the information on the collabora docker container

COOLWSD version:

22.05.14.3 git hash: [c360011]

LOKit version:

built on [a great technology base]
Collabora Office 22.05.14.3 git hash: [8e4f947]

Served by: Ubuntu 18.04.6 LTS [cf04e6ee]

1 Like
4

link to a libreoffice file with a working formula. In Collabora Online you will probably get a Error 540 as it doesnt allow you to fetch the data, but LibreOffice will give you an option to override the secuity limit and do so.

1 Like
5

@dec0de Thanks for the test ODS.

Because of your info… I think I found this exact same issue mentioned back in March 2021:

Looks like @bearon + @mmeeks mentioned a few advanced config settings/options, but I don’t see any final update/solution mentioned in there.

A few other users were trying to:

Side Note: For future knowledge, I’m keeping my initial debugging + post below.

First, sorry missing out on informaion and for the formula not being correct, this is a working one in libre office:

Thanks!

Here’s the full formula with a little better formatting (hopefully that makes it easier to copy/paste):

  • =NUMBERVALUE(FILTERXML(WEBSERVICE("https://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml"),"//*[local-name()='Cube' and @currency='" & "SEK" & "']/@rate"),".","'")

Okay, in LibreOffice 7.5.3, I get a:

  • ERR:540

and a popup saying:

  • Security Warning: Automatic update of external links has been disabled.

After enabling, the cell turns into:

  • 11.6255

In Collabora Online, if I:

  1. Open the ODS:
  • ERR:540

There is no popup, but… in the Status Bar, I see a:

  • “External content disabled: undefined”

If you instead:

  1. Create a new Spreadsheet.
  • Copy/Paste the formula into it.

The formula works.

If you Refresh the page though, back to Err:540.