Excel integration

You can use various methods to establish a connection between Cinchy and Microsoft Excel, such as using Basic Auth, Personal Access Tokens, or Bearer Tokens.

Review each section below for further details.

Prerequisites

Excel connects to queries within Cinchy, so before you use any of the connection methods below you will need to create one that represents your dataset. Once created, you will need to copy down the REST API URL endpoint, located as a green button on the right-hand side of the Execute Query screen.

The structure of the URL endpoint is <your Cinchy instance URL>/API/<the name of your query>. You might optionally have querystring parameters at the end as well.

For example: http://your.cinchy.instance.domain/API/YourQueryDomain/API Test

caution

Note that for Basic Authentication with a result format of CSV we will use a slightly different URL endpoint.\

For Basic Auth: /API/ becomes /BasicAuthAPI/\

For CSV results you will add the querystring parameter of ResultFormat=CSV

Our example URL of a basic auth using CSV results would then become: http://your.cinchy.instance.domain/BasicAuthAPI/YourQueryDomain/API Test?ResultFormat=CSV

Use basic auth

1. Launch Excel and navigate to Data > Get Data > From Other Sources > Blank Query (Image 1).

2. In the expression box that appears, enter the below text to add in your query as your data source (Image 2):

=Csv.Document(Web.Contents("API ENDPOINT URL"))

Example:

=Csv.Document(Web.Contents("http://your.cinchy.instance.domain/BasicAuthAPI/YourQueryDomain/API Test?ResultFormat=CSV"))

3. Once you've entered that text either click the check mark to the left of the input box or click away and it will automatically attempt to run the expression.
4. The data may return in HTML format initially and not be what you're expecting. To correct this:
   1. Select the Data Source Settings.
   2. Select Basic and enter the credentials for a Cinchy User Account that has access to run this query.
   3. Select OK.
   4. Within the Edit Permissions dialogue, click OK.
   5. Within the Data Source Settings dialogue, click Close.
   6. Select Refresh Preview.
   7. Select Close & Load and your dataset will be displayed in the Excel worksheet.

Use a Personal Access Token (PAT)

1. If needed, follow the documentation here to generate a new PAT.

2. Launch Excel and navigate to Data > From Web.

3. Select Advanced and input the following values:

   1. URL Parts: This is the Query API URL that you created in the Prerequisites section.
   2. HTTP Request Header Parameters:
      1. In the first text box input Authorization
      2. In the second text box type Bearer + your PAT. For example: "Bearer BGFHFHOJDF76DFDFD777"

4. Select OK.

5. Select Load to use the query data in Excel.

Use a Bearer Token

1. If needed, follow the documentation here to generate a Bearer Token.

2. Launch Excel and navigate to Data > From Web.

3. Select Advanced and input the following values:

   1. URL Parts: This is the Query API URL that you created in the Prerequisites section.
   2. HTTP Request Header Parameters:
      1. In the first text box input Authorization
      2. In the second text box type Bearer + your token. For example: "Bearer eyUzI1NiIsImtpZCI6IkE4M0UwQTFEQTY1MzE0NkZENUQxOTFDMzRDNTQ0RDJDODYyMzMzMzkiLCJ0eXAiO"

4. Select OK.

5. Select Load to use the query data in Excel.