Fetches data from a web service, with the returned data appearing as a text string in the formula cell. The web service is specified by the Uniform Resource Identifier (URI) argument and must be accessed via either HTTP (Hypertext Transfer Protocol) or HTTPS (Hypertext Transfer Protocol Secure).
In cases where the data returned by WEBSERVICE is in Extensible Markup Language (XML) format, you can use the FILTERXML function to parse the XML markup and extract data of interest.
Returns a text string containing the data fetched. In some cases, you may need to increase the size of the formula cell to view the result.
URI is a text string (in quotation marks), or a reference to a cell containing a text string, that specifies the URI of the web service to be accessed.
- If URI is an empty string, then WEBSERVICE reports a #VALUE! error.
- If WEBSERVICE is unable to receive content from the URI provided (for example, no such web service), then the function reports a #VALUE! error.
- If the URI argument does not specify a URI that uses either http or https, then WEBSERVICE reports a #VALUE! error.
If you open a spreadsheet containing calls to the WEBSERVICE function, Calc may present a warning message stating that the Automatic update of external links has been disabled. In such a case, an Allow Updating button is provided to allow you to continue, confirming that the external links in the file should be updated. You can select▸ to External Files to check the nature of relevant links before continuing.
Accessing XML data
Enter the following formula in cell A1:
This formula will cause an exceptionally long, multi-line string to appear in cell A1, providing information about recent changes to The Document Foundation's wiki. It may be helpful to increase the height of row 1 and the width of column A and to select▸ ▸ with cell A1 selected. Adjusting these options should make it easier to see the first few lines of information. The first line of the string declares the data to be in XML version 1.0 format and so FILTERXML could be used to extract any data of interest. The second line identifies that it is also in RSS (Really Simple Syndication) 2.0 format.
If you would like to understand more about the content of a web service that returns XML, you can enter its URI into your browser to inspect the structure of the returned data.
Accessing non-XML data
WEBSERVICE can return non-XML data. A simple example might be the following formula in cell A2:
This formula will cause a multi-line string to appear in cell A2. Make sure that you have increased the height of row 2 and the width of column A, and selected▸ ▸ with cell A2 selected. The first line of the string declares the data to be in HTML (HyperText Markup Language) format and so FILTERXML cannot be used to extract any data of interest in this case.
If you would like to understand more about the content of a web service that returns HTML, you can enter its URI into your browser and select to look at the page source (exact interactions vary from browser to browser).
The WEBSERVICE function returns the raw result from an HTTP/ HTTPS request. Hence, if the URI address corresponds to any type of text content (e.g. CSV, XML, TXT, MD, etc), the raw content will be returned. The following example will return the raw content of the file README.md stored at the given URI:
The file extension .md refers to a text file created with one of the variants of the Markdown language.
Related LibreOffice functions:
Related (or similar) Excel functions: