5

This used to work in libreoffice calc but can no longer get it to work.

I enter this in a cell: (my personal key is altered)

=WEBSERVICE("http://api.currencylayer.com/historical? access_key=123456&date=2021-03-08")

And I'm supposed to get back a JSON (works in browser) but in Calc I get a #VALUE! error.

I save the document and re-load it, to be presented with a banner saying "Automatic update of external links has been disabled". I click "Allow updating", the cell changes to "Err:540", which stands for "External content is disabled".

I tried changing security settings at Tool > Options > LibreOffice > Security > Macro Security > changed from "High" (the default) to "Medium" (confirmation required before executing macros from untrusted sources"

.. but to no success.

I'm on ArchLinux and I tried using libreoffice-still (7.0.4-2), libreoffice-still (7.0.5-2) and also libreoffice-fresh (7.1.2-2).

A workaround I found is to install a plugin that supplies a GET function as described here, but I would very much prefer the built-in method to work. Is this a bug?

haelix
  • 4,245
  • 4
  • 34
  • 56
  • Did you check any other webservice that provides json output? Is there a way to switch currencylayer output to XML? I've checked the RSS example from the documentation which seems to work. – tohuwawohu Apr 12 '21 at 16:54
  • 2
    Thanks for the pointer, I also found a link that works - `=WEBSERVICE("wiki.documentfoundation.org/api.php?hidebots=1&days=7&limit=50&action=feedrecentchanges&feedformat=rss")`. The difference being, this one returns XML whereas mine returns JSON. This is odd, I'm not asking `calc` to parse the output, I do it manually, so why picky? I am positive this worked at some point. – haelix Apr 15 '21 at 17:42
  • If you're working on linux, what about a small shell script (maybe containing a single curl command) to download the data, and call it using the shell() function? – tohuwawohu Apr 16 '21 at 05:35
  • The plug-in I found is better than that, right? I mentioned it in the question. FWIW I'll also answer it, from all I can tell, despite having been downvoted, the question is well researched and formulated and also useful. – haelix Apr 16 '21 at 09:14
  • 1
    The issue is still present in LibreOffice 7.2. – Jose Gómez Sep 22 '21 at 13:27

1 Answers1

4

It seems libreoffice doesn't like the fact that the result is JSON. Other webservices returning XML work fine as per the workflow in the question.

This is misleading on the part of libreoffice, because the error reported is 540, which means external content disabled (one can also notice "external content disabled" in the status bar). Pretty sure this is a bug, also JSON not working is a bug since libreoffice shouldn't care about the format returned - it is not asked to parse it.

Workaround is to use the GET function exposed by this plug-in: https://www.reddit.com/r/libreoffice/comments/ajqswc/reading_data_into_calc_from_a_web_based_source/ef0o7sw?utm_source=share&utm_medium=web2x&context=3

haelix
  • 4,245
  • 4
  • 34
  • 56