Use the WEBSERVICE function

The WEBSERVICE function returns data from an Internet or Intranet web service using machine-readable formats like XML or JSON. WEBSERVICE supports the HTTP and HTTPS protocols.

Syntax: =WEBSERVICE(URL)  where URL is a web address or service to call. 

For example: 
=WEBSERVICE("https://contoso.com/serviceEndpoint?searchString=Excel")

A #VALUE! error is returned if:

• The arguments result in a non-valid string or it contains more than 32767 characters.

• The URL string contains more than 2048 characters that are allowed for a GET request.

• Unsupported protocols such as ftp:// or file://.

For best results, encode the URL for WEBSERVICE using the ENCODEURL function before including in a function. For more info, see ENCODEURL function.