How to: Connect to an XConnector service

This article walks through the process of connecting to a sample XConnector Spark service. In this guide we go through the following steps:

1. Review the assets and components needed to complete this how to, including the remote external service.

2. Call the remote Xconnector service.

3. Create a Spark Service with a sample XConnector Excel. Once uploaded to the Coherent Spark platform, it enables integration to this remote service.

4. Consume XConnector from Excel Use the model via Coherent Assistant in the Excel application or Microsoft 365.

Review the assets and components

Remote service	An Azure HTTP-triggered Function that allows anonymous access. It retrieves a filtered list of species based on the input criteria provided in the request. The function is hosted at https://func-xconnector-sample.azurewebsites.net/api/species. If you are interested in creating your own XConnector see: . The code for this XConnector is also attached below.
species.xlsx Excel workbook	The sample Spark model file that defines the inputs, outputs, service type (XConnector), and the URL to the remote service.
species-xcall.xlsx Excel workbook	A sample Excel workbook that consumes the species service directly via XConnector. It can be used in the Excel application or Microsoft 365.

22KB

species.xlsx

Open

13KB

species-xcall.xlsx

Open

14KB

Coherent.Spark.Demos.XConnector-master.zip

archive

Open

Call the remote XConnector service

In the species remote service, we provide a list of animal species Types and we will receive a list of matching animal names, type and countries.

1. Run the curl statement in your application of choice. The user guide includes instructions on using Postman. Note the request body follows the "table of inputs" format from Request format from Spark XConnector to Remote service.

   curl --location 'https://func-xconnector-sample.azurewebsites.net/api/species' \
   --header 'Content-Type: application/json' \
   --data '{
     "inputs": [
       ["species"],
       [
         [
           ["Type"],
           ["Reptile"]
         ]
       ]
     ]
   }'

2. You should receive this sample response. It conforms to the structure in Response format from the Remote service to Spark XConnector.

   curl --location 'https://func-xconnector-sample.azurewebsites.net/api/species' \
   --header 'Content-Type: application/json' \
   --data '{
     "inputs": [
       ["species"],
       [
         [
           ["Type"],
           ["Reptile"]
         ]
       ]
     ]
   }'

Create a Spark service with the remote service call

1. If you have not created a Spark service before, please review Get started in 5 minutes.

2. Open the species.xlsx file that is attached above. From the Named Ranges, in addition to Xinput_types and Xoutput_results, this file also includes important named ranges to identify this as a XConnector service.

   Named range	Description	Example value
   XPROPERTY_DefaultEngineType	The engine type for the model — must be set to XConnector.	XConnector This entry is case sensitive. The C is capitalized.
   XPROPERTY_XConnectorTargetUrl	The base URL of the remote service endpoint (must end with /).	https://func-xconnector-sample.azurewebsites.net/api/
   XPROPERTY_XConnectorServiceName	The specific service name (matches the endpoint name after the base URL).	species

3. In Spark, create a folder called xconnectors.

4. Upload this Excel into the xconnectors folder as a Spark service. Keep the name of the service as species.

5. If you upload this Spark and use the API Tester to make an API call, you should see that the response includes a list of matching animal names.

Consume the XConnector from Excel

This step requires Coherent Assistant to be installed and available in your Excel account.

1. Open the species-xcall.xlsx workbook that is attached above.

2. Open Coherent Assistant.

3. Log in to the Spark tenant where your XConnector service was deployed.

4. Coherent Assistant will automatically detect the XConnector model and allow you to run it directly within Excel.

5. The Xcall function is used to pull the data from the XConnector. Learn more about Xcall in Call Spark service APIs (Xcall).

   1. If you had instead uploaded the species to another folder more_xconnnectors, change the service reference in the CS.SPARK_XCALL() function to point to "more_xconnectors/species".

   2. Within the formula there are the parameters "!treat_as_table","type", this is needed because the animal types are in a single column. If single cell inputs or multi-column tables are used, then these parameters are not needed.

6. If you create a Spark service from this workbook by adding Xinput and Xoutput, then you can create additional logic and calculations around the species remote service that can be used in an integration, e.g. return the number of matching animals.