Bug 144238 - External content disabled for WEBSERVICE call
Summary: External content disabled for WEBSERVICE call
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: framework (show other bugs)
(earliest affected) release
Hardware: x86-64 (AMD64) Windows (All)
: medium normal
Assignee: Not Assigned
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
Reported: 2021-09-01 11:13 UTC by Lukasz Ciesluk
Modified: 2023-08-03 12:58 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Note You need to log in before you can comment on or make changes to this bug.
Description Lukasz Ciesluk 2021-09-01 11:13:42 UTC
Using below script works fine at the first usage


but when spreadsheet is closed and opened once again I got error 540 - External content disabled.

After changing a setting of 

Tool > Options > LibreOffice > Security > Macro Security > changed from "High" (the default) to "Medium" (confirmation required before executing macros from untrusted sources"

and reopening spreadsheet , it asks "Automatic update of external links has been disabled". After clicking on "Allow updating", nothing is refreshed in the cell using above formula as it gives the same error of 540.

Steps to Reproduce:
1. =REGEX(WEBSERVICE("http://data.fixer.io/api/latest?access_key=74ba35e56db252bd4737c215d4b0bf62&base=&symbols=PLN"),"\d{1,2}[\,\.]{1}\d{1,5}")
2. Close spreadsheet and open it once again
3. Check if value is updated in the cell by above script

Actual Results:
Values is not updated in the cell by above script

Expected Results:
Values is updated in the cell by above script

Reproducible: Always

User Profile Reset: No

Additional Info:
Sometime ago I started using =com.github.binnarywolf.libreofficegetrestplugin.get("http://data.fixer.io/api/latest?access_key=74ba35e56db252bd4737c215d4b0bf62&base=EUR&symbols=GBP"), it used to work for a long time but I would say that starting from April/May 2021 it stopped working. The same thing applies to WEBSERVICE
Comment 1 Aron Budea 2021-09-03 01:34:38 UTC
For me the WEBSERVICE function doesn't work after entering it the first time, either (I removed the REGEX part to make sure that doesn't meddle with anything), it gives #VALUE! error instead of the expected JSON output. Since this bug report seems to be about something else, I opened bug 144276 on that.
Comment 2 Eike Rathke 2021-09-03 11:19:53 UTC
As mentioned in bug 144276 I believe that is a broken server implementation maybe tied with a timing problem.

curl -i 'http://data.fixer.io/api/latest?access_key=74ba35e56db252bd4737c215d4b0bf62&base=&symbols=PLN'

works and advertises
access-control-allow-methods: GET, HEAD, POST, PUT, PATCH, DELETE, OPTIONS

but already

curl -I 'http://data.fixer.io/api/latest?access_key=74ba35e56db252bd4737c215d4b0bf62&base=&symbols=PLN'

(doing a HEAD request) fails with 403 Forbidden status.
Comment 3 Tandem25 2021-09-04 10:51:08 UTC
I'm also getting Err:540 in cell and "External content disabled" on status bar under Ubuntu 20.04 using LibreOffice Calc 7.2 from the formulas such as:

=WEBSERVICE("https://maps.googleapis.com/maps/api/distancematrix/xml?origins="&SUBSTITUTE(G3," ","+")&"&destinations="&SUBSTITUTE(G4," ","+")&"+"&SUBSTITUTE(H4," ","+")&"&mode=bicycling&key=myGoogleAPIkey")

a) Options > LibreOffice > Security > Macro Security > Trusted Sources > Security Level (from High to Low)
b) Options > LibreOffice > Security > Macro Security > Trusted Sources > Trusted File Locations
c) Options > LibreOffice Calc > General > Update links when opening (Always selected)

The same spreadsheet with the above formula works perfectly under Windows!  In fact, the spreadsheet and formulas also worked under Ubuntu in older version of Calc (e.g. 5.x)
Comment 4 roland 2023-02-16 11:49:35 UTC
It has been a while, but this works perfectly from Excel if I paste the exact WEBSERVICE call into a cell there, so if the problem was the webserver, then Excel would get an error returned too, but it doesn't.

Also, I use the function twice in a sheet. The first retrieves the USD vs ZAR rate, whereas the second retrieves the EUR vs ZAR rate.  The first use works, the second doesn't.  Even if I select the field and use <F9> to recalculate it, it doesn't.