LibreOffice Calc - External data source

Scenario: You want to repeatedly fetch data from an external web page or web service into a LibreOffice Calc spreadsheet.

Note that these options do not need LibreOffice Base installed. If you have Base then you might use SQL database connections and queries as another option.

Option 1 - External Links

This option reads <TABLE> elements off HTML pages and can refresh the data by itself. Calc asks upon opening the file if links should be updated or not. The downside is that Calc does not show where the data is put in the spreadsheet after the link has been made as of v.25.2.3.2.

1. Select a cell in the spreadsheet.

2. Go to Sheet -> External Links

3. In the dialog External data that appears fill in the URL to the page. Press ENTER - the dialog is missing a load button, how shameful.

4. A new dialog appears, Import options - change options if needed and press OK.

5. Back in External data you should have tables/areas listed under Available tables/areas provided that LibreOffice managed to parse the page. Select the matching table.

6. Below the list check Update after: option and set an interval if you want it to refresh by itself. Then press OK.

7. The data should now appear in the spreadsheet, starting from the selected cell.

Option 2 - WEBSERVICE function

This option reads text data from an URL. It should read XML and HTML but when I tested then it outputted the XML/HTML raw like text. So if you control the source, output just text, no XML/HTML.

The upside with this method is that the function is shown in the cell as a function, the downside is that a refresh interval cannot be set. It needs to be manually refreshed through the Edit -> External links... menu.

1. Select a cell in the spreadsheet.

2. Write the following in the value field for the cell above the spreadsheet and replace the URL with your source URL:
=WEBSERVICE("https://example.org/")

Replace WEBSERVICE with the name of the function in language version of Calc you are using. For example in Swedish it is WEBBTJÄNST.

3. Press Enter. Calc will immediately load the data.

Edit/Update external links

1. Go to Edit -> External links... If it is disabled and the file is not an .ODS file, then resave the file as an .ODS file and retry.

2. In the Edit links dialog select the link to edit and press Edit.

Note that if you used the WEBSERVICE method, then you will get an Open... dialog instead of options.

3. If you used the WEBSERVICE method, then you need to press the Update button to refresh the content.

Note that this dialog is somewhat broken, the Status column never changes regardless of selecting interval. There is also no option or display of where in the spreadsheet the data is written.

References

Pulling data from external website into Calc? - https://www.reddit.com/r/libreoffice/comments/7yrn00/pulling_data_from_external_website_into_calc/
Documentation/Calc Functions/WEBSERVICE - https://wiki.documentfoundation.org/Documentation/Calc_Functions/WEBSERVICE
Auto update webservice api links - https://ask.libreoffice.org/t/auto-update-webservice-api-links/63636