Can WEBSERVICE return JSON data directly into cells?

WEBSERVICE returns the raw response as a text string, regardless of whether the content is XML, JSON, CSV, or plain HTML. To parse JSON you must use a custom VBA function or Power Query, because Excel has no built‑in JSON parser.

Why does my WEBSERVICE formula return #VALUE! even though the URL looks correct?

If any part of the URL argument evaluates to a non‑text value—such as a number, an error, or an empty reference—Excel treats the whole argument as invalid and produces #VALUE!. Ensure every concatenated piece is a string, for example by using TEXT() or by placing the URL in a cell formatted as Text.

Is there a limit to how much data WEBSERVICE can retrieve?

WEBSERVICE is not designed for massive payloads; very large responses can cause performance slowdowns or trigger #VALUE! due to memory constraints. For bulk data, use Power Query’s web connector, which streams data and handles pagination more efficiently.

Can I use WEBSERVICE to call a secure HTTPS endpoint that requires authentication?

WEBSERVICE does not support authentication headers, tokens, or certificates. It can only call publicly accessible URLs. For authenticated APIs you must use Power Query, VBA, or a custom add‑in that can supply the necessary headers.

WEBSERVICE function

WEBSERVICE pulls the raw response from a given URL and returns it as a plain‑text string, letting you import live web data into Excel worksheets.

=WEBSERVICE(url)

Generate a WEBSERVICE formula

Describe what you need. The generator will reach for WEBSERVICE where WEBSERVICE is the right tool, and tell you when it is not.

Arguments

How WEBSERVICE reads its arguments

Argument	Required	Description
url	Required	url – a text string that must be a complete, properly formed web address; if the string is missing, empty, or not a valid URL, WEBSERVICE returns an error.

Returns

It returns a plain‑text string containing the full response body from the web request.

Availability

Excel: Windows only · Google Sheets: Not available

Worked examples

1. Lookup a sales rep’s commission rate

Order ID	Region	Rep	Units	Unit Price	Order Date
1023	West	Alice	12	19.99	2023-06-14

=WEBSERVICE("https://api.example.com/commission?rep="&ENCODEURL(A2))

Result: 0.075

The formula builds a request URL that asks the mock API for the commission rate of the rep named in cell A2 ("Alice"). ENCODEURL safely escapes the name for the query string. When the API receives the request, it returns the commission as a plain number (7.5%). WEBSERVICE captures that response and returns the text "0.075", which can be used directly in further calculations.

2. Get total sales for a region

Order ID	Region	Rep	Units	Unit Price	Order Date
1045	East	Bob	8	24.5	2023-07-02

=WEBSERVICE("https://api.example.com/regionSales?region="&ENCODEURL(B2))

Result: 45230

Cell B2 holds the region code "East". The formula concatenates that value onto the API endpoint, using ENCODEURL to protect any special characters. The web service calculates the sum of all sales in the East region and returns the total as a string. WEBSERVICE returns that string – "45230" – which represents $45,230 of sales and can be coerced to a number with VALUE() if needed.

3. Fetch the order date for a specific order ID

Order ID	Region	Rep	Units	Unit Price	Order Date
1089	South	Carol	5	31	2023-08-19

=WEBSERVICE("https://api.example.com/orderDate?id="&C2)

Result: 2023-07-15

Cell C2 contains the order identifier 1089. The formula sends that ID to the mock order‑date endpoint. The service looks up the order in its database and returns the ISO‑formatted date string "2023-07-15". WEBSERVICE passes that raw text back to the worksheet, where it can be wrapped in DATEVALUE() to become a true Excel date.

Common errors

Which WEBSERVICE error are you seeing?

Error	Why it happens	How to fix it
#VALUE!	The url argument is not a text string (e.g., it is a numeric value or a reference to an empty cell).	Wrap the argument in TEXT() or ensure the cell contains a proper URL string before calling WEBSERVICE.
#NAME?	WEBSERVICE is not available in the current environment, such as Google Sheets or older Excel versions.	Use a supported platform like Excel for Windows, or replace the call with Power Query’s web‑connector.
#N/A	The web request fails because the server returns a 404 Not Found or the domain cannot be reached.	Verify the URL is correct, test it in a browser, and ensure the target server is online and accessible.

Tips and when to use something else

Always wrap dynamic query parts with ENCODEURL to avoid malformed URLs when the data contains spaces or special characters.
Combine WEBSERVICE with FILTERXML to pull specific nodes out of an XML response without needing VBA.
For large or frequently refreshed data sets, consider Power Query (Get & Transform) instead of WEBSERVICE for better performance and error handling.
If you need real‑time streaming data, the RTD function is a more appropriate choice than repeatedly calling WEBSERVICE.

Related functions

FILTERXMLFILTERXML parses a text string containing XML and returns the values that match a supplied XPath query, spilling results into adjacent cells.ENCODEURLENCODEURL converts a text string into a percent-encoded URL component, escaping characters that are illegal in web addresses.XLOOKUPXLOOKUP returns the value from a specified return array that corresponds to a given lookup value, with optional handling for missing matches and search direction.LETLET lets you name a single intermediate result inside a formula, then return that result, making complex calculations clearer and easier to read.IFERRORIFERROR returns the result of a formula unless that formula generates an error, in which case it returns a user-specified alternative value.

Frequently asked questions

Can WEBSERVICE return JSON data directly into cells?: WEBSERVICE returns the raw response as a text string, regardless of whether the content is XML, JSON, CSV, or plain HTML. To parse JSON you must use a custom VBA function or Power Query, because Excel has no built‑in JSON parser.
Why does my WEBSERVICE formula return #VALUE! even though the URL looks correct?: If any part of the URL argument evaluates to a non‑text value—such as a number, an error, or an empty reference—Excel treats the whole argument as invalid and produces #VALUE!. Ensure every concatenated piece is a string, for example by using TEXT() or by placing the URL in a cell formatted as Text.
Is there a limit to how much data WEBSERVICE can retrieve?: WEBSERVICE is not designed for massive payloads; very large responses can cause performance slowdowns or trigger #VALUE! due to memory constraints. For bulk data, use Power Query’s web connector, which streams data and handles pagination more efficiently.
Can I use WEBSERVICE to call a secure HTTPS endpoint that requires authentication?: WEBSERVICE does not support authentication headers, tokens, or certificates. It can only call publicly accessible URLs. For authenticated APIs you must use Power Query, VBA, or a custom add‑in that can supply the necessary headers.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator