Excel Function: WEBSERVICE
Purpose
The WEBSERVICE function is Excel’s means of calling a web service and capturing its response.
Invocations of the WEBSERVICE function take the form:
WEBSERVICE(url)Limitations
- The WEBSERVICE function does not work on either Apple Macs, or Excel for the web.
- No Excel functions exist capable of manipulating JSON-encoded information returned by a call to the WEBSERVICE function.
Arguments
| Argument | Argument type | Description |
|---|---|---|
| url | Mandatory | Represents the endpoint of the web service to be called. |
Related Functions
| Function | Example | Purpose |
|---|---|---|
| ENCODEURL | WEBSERVICE(ENCODEURL(url)) | Sanitise a URL then use it to call a web service. |
| FILTERXML | FILTERXML(WEBSERVICE(ENCODEURL(url)),xpath) | Sanitise a URL, use it to call a web service, then extract data from the response. |
Error Conditions
The WEBSERVICE function will respond with an error value if any of the following conditions are met.
| Error value | Condition type | Condition |
|---|---|---|
| #VALUE | Bad argument | The length of the value supplied to the url argument exceeded 2,048 characters. |
| #VALUE | Bad argument | The protocol used to form the value supplied to the url argument was neither HTTP nor HTTPS. |
| #VALUE | Bad argument | The value supplied to the url argument was not the endpoint of an accessible web service. |
| #VALUE | Bad argument | The value supplied to the url argument did not represent a well-formed URL. |
| #VALUE | Runtime | A web service was called successfully and its response’s length exceeded 32,767 characters. |
Example 1: Successfully Invoking a Web Service
Formula
=WEBSERVICE("https://api.eve-industry.org/system-cost-index.xml?name=Jita")Result
<?xml version="1.0" encoding="UTF-8"?>
<eve-industry-api version="1.0" method="system-cost-index">
<solarsystem id="30000142" name="Jita">
<activity id="1" name="Manufacturing">0.1344</activity>
<activity id="3" name="Researching Time Efficiency">0.0007</activity>
<activity id="4" name="Researching Material Efficiency">0.0007</activity>
<activity id="5" name="Copying">0.0007</activity>
<activity id="7" name="Reverse Engineering">0</activity>
<activity id="8" name="Invention">0.0007</activity>
<activity id="11" name="Reactions">0.001</activity>
</solarsystem>
</eve-industry-api>Example 2: Extracting Values from an XML Response
The FILTERXML function was applied to the result from example 1. It gets the value from the activity tag whose id attribute equals 3.
Formula
=FILTERXML(WEBSERVICE("https://api.eve-industry.org/system-cost-index.xml?name=Jita"),"//activity[@id='3']")Result
0.0007Example 3: Receiving a JSON response
It is possible to capture a JSON response provided by a web service. However, because Excel does not provide a function which can extract data from JSON, in the same way FILTERXML can from XML, JSON is difficult to parse with functions alone. In practice, one must either (1) construct a formula leveraging Excel’s text functions, (2) use VBA, or (3) use Power Query.
Formula
=WEBSERVICE("https://catfact.ninja/fact")Result
{"fact":"Phoenician cargo ships are thought to have brought the first domesticated cats to Europe in about 900 BC.","length":105}Example 4: Errors caused by ENCODEURL
Encoding instances of URL reserved characters will cause the WEBSERVICE function to fail. Because of this, only apply the ENCODEURL function to the portions of a URL which either (1) do not contain URL reserved characters or (2) contain URL reserved characters which must not be treated as such.
| Example | Formula | Result |
|---|---|---|
| 1 | =WEBSERVICE("https://catfact.ninja/fact") | {"fact":"Most cats adore sardines.","length":25} |
| 2 | =WEBSERVICE(ENCODEURL("https://catfact.ninja/fact")) | #VALUE |
| 3 | =WEBSERVICE("https://catfact.ninja/"&ENCODEURL("fact")) | {"fact":"Most cats adore sardines.","length":25} |
| 4 | =WEBSERVICE("https://"&ENCODEURL("catfact.ninja/fact")) | #VALUE |
| 5 | =WEBSERVICE("https://"&ENCODEURL("catfact.ninja")&"/"&ENCODEURL("fact")) | {"fact":"Most cats adore sardines.","length":25} |
| 6 | =WEBSERVICE(ENCODEURL("https:")&"//"&ENCODEURL("catfact.ninja")&"/"&ENCODEURL("fact")) | #VALUE |
| 7 | =WEBSERVICE(ENCODEURL("https")&"://"&ENCODEURL("catfact.ninja")&"/"&ENCODEURL("fact")) | {"fact":"Most cats adore sardines.","length":25} |
| 8 | =WEBSERVICE("https://api.agify.io"&ENCODEURL("?name=meelad")) | #VALUE |
| 9 | =WEBSERVICE("https://api.agify.io?name=meelad") | {"age":33,"count":21,"name":"meelad"} |