CODEWITHSUNDEEP
web-servicesgetlibreoffice-calc
haelix
haelix

Reputation: 4555

How to use WEBSERVICE libreoffice calc function?

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?

Upvotes: 5

Views: 4029

Answers (1)

haelix
haelix

Reputation: 4555

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

Upvotes: 4

Related Questions