ajWebServiceREST function
Available since AlchemyJ v4.1.3
Description
The ajWebServiceREST function returns data from a REST web service on the internet or intranet. The response can span across multiple cells if it is longer than a cell can hold.
Syntax
ajWebServiceREST(url As String, [http_method], [body], [user_name], [password], [header], [content_type], [run_condition], [run_by_function_point_only])
| Argument Name | Argument Type | Description |
|---|---|---|
| url (required) | String | The URL of the web service call. |
| 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. 4 indicates a DELETE 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 (“”). |
| 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 (“”). |
| 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 (“”). |
| content_type (optional) | String | The content type of the HTTP request to the web service call. The default value is “application/json”. |
| 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
2) Return Type: Single Value / Multiple values (array formula)
Example
Below is an example of a GET request in Postman.
Below is an example of GET request in the AlchemyJ Excel workbook.
As you can see from the above example, the calling of the ajWebServiceREST function is successful and the result returned is the corresponding HTTP response body (same as Postman).
Click here to download the use case workbooks for further reference.
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. |