ajWebService function

Available since AlchemyJ v4.1.3

Description

The ajWebService function returns data from a web service on the internet or intranet. The response can span across multiple cells if it is longer than a cell can hold.

Syntax

ajWebService(url As String, [http_method], [body],[header],[cookie] [user_name], [password], [content_type], [response_type],[include_header],[check_ssl_cert],[options],[run_condition], [run_by_function_point_only])

Argument Name	Argument Type	Description
url (required)	String	The URL of the web service call. When there has some special characters in the url, you can also use the ENCODEURL function in conjunction with the ajWebService function. For example, =ajWebService("http://127.0.0.1:8080/stockorder/stock?name="&ENCODEURL("C1")), where you have a stock ticker in C1. If the value of C1 is "Ticker 01", the url will become http://127.0.0.1:8080/stockorder/stock?name=Ticker%2001
http_method (optional)	Double	The HTTP method to call the web service. 0 indicates a POST request. 1 indicates a GET request. 2 indicates a PUT request. 3 indicates a PATCH request. 5 indicates a OPTIONS request. 6 indicates a HEAD request. 7 indicates a TRACE request. The default value is 0.
body (optional)	Range	The body for HTTP calls from a range of cells. The range of cells can span across multiple rows and columns. All the cells will be concatenated. The default value is an empty string (“”).
header (optional)	Range	The HTTP header from a range of cells. The first column should contain a header field name. The second column should contain a header field value. The default value is an empty string ("").
cookie (optional)	Range	The HTTP cookie from a range of cells. The first column should contain a cookie field name. The second column should contain a cookie field value The default value is an empty string ("").
user_name (optional)	String	The username to access the web service call if applicable. The default value is an empty string (“”).
password (optional)	String	The password of the username to access the web service call if applicable. The default value is an empty string (“”).
content_type (optional)	String	The content type of the HTTP request to the web service call. The default value is “application/json”.
response_type (optional)	Integer	It is a number value indicate which response type to be included. 0 - all body, header and cookie 1 - body only 2 - header only 3 - cookie only The default value is 1.
include_header (optional)	Boolean	If it equals TRUE, the return value will include column headers. If it equals FALSE, the return value will not include column headers. The default value is FALSE.
check_ssl_cert (optional)	Boolean	If it equals TRUE, the return value will include column headers. If it equals FALSE, the return value will not include column headers. The default value is FALSE.
run_condition (optional)	Boolean	The function will run when the value is TRUE. Otherwise, it will not run. The default value is TRUE.
run_by_function_point_only (optional)	Boolean	If it equals FALSE, the function can be executed through ‘Excel Calculation’ (can be either Automatic or Manual, Calculate Now or Calculate Sheet) or Preview Function Point. If it equals TRUE, the function can be executed with Preview Function Point (AlchemyJ ribbon \ Preview Function Point) only. The default value is TRUE.

The function will return:

1) Return Value: HTTP response body/header/cookie

2) Return Type: Single Value / Multiple values (array formula)

Example

By comparing ajWebServiceREST and ajWebServiceSOAP, ajWebService adding new parameters to support passing information via header or cookie during the API call. Both ajWebServiceREST and ajWebServiceSOAP can only get the response body from the API call while ajWebService can get all the information returned from API in body, header, and cookie.

Example 1

It is a sample SOAP API, here is the result we call from SOAPUI.

We use ajWebservcie in an AlchemyJ workbook, select B17 to B19 as a result range, and input the formula

=ajWebService(B1,B2,B3,,,,,,B9,,,,,B14)

The response type is 0 means it will get all body, header, and cookie. The result returned in B17 and B18 respectively.

Example 2

It is a sample REST API, it will get all header information and return them in the response body. First, we try this API using postman. What the input in the header will be shown in the response as the screen capture.

Then we try the ajWebService function, input the formula

=ajWebService(B22,,,B25:C26,,,,,B31,,,,,B36)

B25 to C26 is the header range we pass to the API and you can see the return result is also including the provided header information.

Error Scenarios

It will return #VALUE! when missing any required parameter or mismatch parameter type. Besides, system will raise error for below scenario(s).

Error Scenario
The API throw error.
Failed to call the API.
Invalid http method.
Invaid reponse type.