SheetSkill

WEBSERVICE function

Downloads text (often XML or JSON) from a web address straight into a cell.

Works in desktop Excel only

Syntax

=WEBSERVICE(url)

How to use WEBSERVICE in Excel, step by step

WEBSERVICE doesn’t run in a web practice sheet because it downloads data from the internet. Follow these steps in Excel itself.

You’ll need

  • Excel for Windows (not available in Excel for the web or Mac)
  • An internet connection
  • A web address that returns text and doesn’t need you to sign in
  1. Find a URL that returns data. For example a public API that returns XML. Paste it in your browser first to check it shows text.
  2. Put the URL in a cell. Type it in A1 so it’s easy to change, or build it with ENCODEURL for any search words: ="https://example.com/api?q="&ENCODEURL(B1).
  3. Download it. In A2 type =WEBSERVICE(A1). The cell fills with the response text (up to 32,767 characters).
  4. Pull out the value you need. For XML use FILTERXML: =FILTERXML(A2,"//price"). For JSON, use TEXTAFTER/TEXTBEFORE or Power Query (Data › From Web).
  5. Refresh. Press Ctrl+Alt+F9 to download again.

Example

=WEBSERVICE("https://example.com/rates.xml")

Result: The XML text of the page

#VALUE! means the address is too long, not reachable or returned more than 32,767 characters.
Power Query (Data › Get Data › From Web) is better for big or regular downloads.