How to use the WEBSERVICE function

The WEBSERVICE function gets data from a web service on the Internet or Intranet based on a given URL.

1. Introduction

What is a URL?

A URL stands for Uniform Resource Locator which is used to navigate to a specific website or asset on the Internet. A DNS server which stands for Domain Name System translates a URL to a numeric IP address that servers use to communicate.
URL - wikipedia

What is a web service?

A web service is a software system that provides a functionality or a resource over the internet by other applications (like Excel) or systems.
Web service - Wikipedia

Many websites uses RSS which stands for (Really Simple Syndication that lets you read for example news without having to go to the news web site. RSS - wikipedia

A RSS reader lets you subscribe to RSS feeds, however, you can use Excel to import RSS feeds. A RSS feed consists of XML data.

What is XML?

XML is an abbreviation for eXtensible Markup Language, it is a text format for storing and transporting data.
Introduction to XML - w3schools

Excel has a function named FILTERXML that lets you extract data from a RSS feed containing XML data.

The function returns a #VALUE error if the returned value is above the cell limit of 32767 characters.

Formula in cell B2:

2. Syntax

The WEBSERVICE function has only one argument named url.

WEBSERVICE(url)

url	Required.  An URL (Uniform Resource allocator) to a webpage on the internet or the intranet.

3. Example

The WEBSERVICE function gets data from a web service on the Internet specified in the url argument.

Formula in cell B2:

The basic example above demonstrates how to get the contents of a webpage located at www.google.com (url) with the WEBSERVICE function from a web server.

What is a web server? A web server is a web service that distributes html pages to web browsers on the Internet. You can use Excel to get the html code to a worksheet with the WEBSERVICE function.

4. WEBSERVICE function not working

HTTP:// and HTTPS:// are supported.

• What is HTTPS://? The HTTPS protocol is a secure version of HTTP. HTTPS uses encryption (typically TLS or SSL) to protect data transmitted between the client and server, ensuring confidentiality, integrity, and authenticity. HTTPS is commonly used for secure web browsing, online transactions, and sensitive data exchange.
• What is HTTP://? The HTTP protocol is an insecure version of HTTPS. HTTP does not use encryption making it vulnerable to eavesdropping, tampering, and man-in-the-middle attacks. HTTP is still widely used for non-sensitive data exchange such as accessing public websites, but it's being gradually replaced by HTTPS.

An URL larger than 2048 returns #VALUE error.

Protocols like ftp:// and file:// are not supported, the WEBSERVICE function returns #VALUE error if these protocols are used.

• What is ftp://? ftp:// indicates that the resource is being accessed using the FTP protocol which is used for transferring files over the internet. FTP is a stateful protocol meaning it establishes a connection with the server and maintains it throughout the file transfer process. FTP is commonly used for uploading and geting files to/from servers, but it's being replaced by more secure alternatives like SFTP (Secure File Transfer Protocol) and HTTPS.
• What is file://? file:// indicates that the resource is a local file on the client's system. The file:// scheme is used to access files on the local file system,such as documents, images, or executables. When a file:// URL is used the client's operating system or browser will attempt to open the file using the associated application or handler.

5. WEBSERVICE function and ENCODEURL function

The WEBSERVICE function requires a valid url to work properly, the ENCODEURL function lets you replace certain characters to a percentage and a hexadecimal code.

You can then use the url in the WEBSERVICE function to get the data you want.

Formula in cell C3:

The file name Budget 2025.xlsx contains a character that needs to be replaced with the corresponding hexadecimal code in order for the url to function properly. This formula is constructing a URL by combining a fixed base URL with an encoded version of the contents of cell C2.

1. "https://www.example.com/":
   • This is a fixed string representing the base URL.
2. & (ampersand):
   • This is the concatenation operator in Excel. It's used to join text strings together.
3. ENCODEURL(C2):
   • ENCODEURL is a function that encodes text to be used in a URL.
   • It takes the content of cell C2 as its argument.
   • In this case, C2 contains "Budget 2025.xlsx"
4. The ENCODEURL function will convert "Budget 2025.xlsx" to a URL-safe format:
   • Spaces are replaced with "%20"
   • Special characters are converted to their URL-encoded equivalents

So, if C2 contains "Budget 2025.xlsx", the ENCODEURL function will convert it to: "Budget%202025.xlsx"
The final result of the entire formula would be: "https://www.example.com/Budget%202025.xlsx"

This creates a full URL that could be used to link to the "Budget 2025.xlsx" file on the example.com website. The encoding ensures that the filename is properly formatted for use in a URL preventing issues that could arise from spaces or special characters in the filename.

Explaining formula in cell C3

Step 1 - Create a text string in the formula

The double quotes lets you use strings in a Excel formula, the string must start and end with a double quote.

"https://www.example.com/"

Step 2 - Encode url

The ENCODEURL function returns a URL-encoded string.

Function syntax: ENCODEURL(text)

ENCODEURL(C2)

becomes

ENCODEURL("Budget 2025.xlsx")

and returns

"Budget%202025.xlsx"

Step 3 - Concatenate website and file name

The ampersand lets you concatenate text strings in a Excel formula.

"https://www.example.com/"&ENCODEURL(C2)

becomes

"https://www.example.com/"&"Budget%202025.xlsx"

and returns

"https://www.example.com/Budget%202025.xlsx"

Back to top

Useful resources

Functions in 'Web' category

The WEBSERVICE function function is one of 4 functions in the 'Web' category.

Excel function categories

Excel categories

Leave a Comment