Description: Using below script works fine at the first usage =REGEX(WEBSERVICE("http://data.fixer.io/api/latest?access_key=74ba35e56db252bd4737c215d4b0bf62&base=&symbols=PLN"),"\d{1,2}[\,\.]{1}\d{1,5}") 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
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.
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.
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") Tried: 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)
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.
(In reply to roland from comment #4) > Also, I use the function twice in a sheet. Ever since LO 6.1, the result is cached, so two identical URLs will always show the same value. (But I realize that is slightly off topic since your URLs are different.) > Even if I select the field and use <F9> to recalculate it, it doesn't. Updating the results with F9 and Ctrl-Shift-F9 worked until the same 6.1 commit 93ea7cb6b5ab3c9b964b2b38e8f4a3bde71dbadf that started the caching mentioned above. Author: Caolán McNamara on Thu Jan 11 20:43:28 2018 +0000 handle ocWebservice similarly to ocDde