Query Nintex Insights OData API using Microsoft Excel

If you want to analyze your workflow data for specific insights, you can design your workflows using beacon actions in the following Nintex capabilities:

You can then use the Nintex Insights OData API to extract data using Microsoft Excel and create custom reports.

Step 1: Create a personal access token

Follow these steps to create the personal access token:

  1. On the upper-right of the Nintex Insights workspace home page, click Settings.
  2. Click Personal access token in the side navigation menu.
  3. In the Personal access token section, click Add token.
  4. Click Copy token and store the personal access token in a secure location.

    5. Caution: The personal access token cannot be viewed again after you close the dialog box. The personal access token is valid for one year.

  5. Click Close. The Personal access token is created and displayed in the list. The following details for the personal access token are displayed: Status, Created on, and Expires. The Add token button is disabled.

Step 2: Copy the OData API URL

  1. On the upper-right of your Nintex Insights workspace home page, click Insights.
  2. Click Workflows in the side navigation menu.
  3. Search for or select and open the workflow for which you want to retrieve the OData API URL from the Workflows list.

    4. Note: Only workflows with Beacon actions will display the OData button.

  4. In the Workflow details section, click OData. The Workflow OData dialog box displays the OData API URLs for the following endpoints.
  1. Click Copy URL next to the respective fields to copy the required URL.

Follow the steps described below to connect to the Nintex Insights OData API using Microsoft Excel.

Step 3: Connect to the Nintex Insights OData API

Note: 
  • You must complete these steps the first time you use a Microsoft Excel file to connect to the Nintex Insights OData API.
  • This topic uses steps from Microsoft Excel for Office 365. There might be slight differences in the user interface, terminology used, and steps depending on your Microsoft Excel version. See the Microsoft Excel Help for specific version details.

This video uses steps from Microsoft Excel 2016. There might be slight differences in the user interface, terminology used, and steps depending on your Microsoft Excel version. See the Microsoft Excel Help for specific version details.

  1. Open a new file in Microsoft Excel.
  2. Click Data > Get Data > Power Query Editor.
  3. Click Home > Manage Parameters > New Parameter. The Parameters dialog box displays. Follow the steps below to add the WorkflowInstances parameter for retrieving data from the WorkflowInstances endpoint.
    1. Type WorkflowInstances in the Name field.
    2. Type WorkflowInstances in the Description field.
    3. Ensure the Required check box is selected.
    4. From the Type drop-down list, select Text.
    5. Type Test in the Current Value field.
    6. Click OK. The parameter you added is displayed under Queries in the left pane.
  1. Select WorkflowInstances on the left pane, and then click Home > Advanced Editor.
  1. In the Advanced Editor window, delete the displayed text and copy-paste the following lines: 
    let
    AccessTokenHeader = "Bearer <Personal access token>",
    Headers = [Authorization=AccessTokenHeader],
    Source = OData.Feed("<Nintex Insights OData API URL>", Headers)
    in
    Source
    Important: 
  1. Click Done. If prompted, click Edit Credentials, select Anonymous access and click Connect. The data for the endpoint is retrieved and displayed. For more information about the columns, see the Properties tables in API Reference.

    2. Tip: To retrieve data for the ProcessStages and ProcessBeacons endpoints, repeat Step 3 to Step 6.

  1. To exit the Power Query editor and load data in the file, click Home > Close & Load > Close & Load To.
  2. In the Load To dialog box, select the Table option and click Load. The table data is loaded and displayed in a new worksheet for each query you add.

    3. Tip: If data is not displayed, click Data > Refresh All > Refresh All. The data is not refreshed in real-time and it can take up to three hours to fetch the updated details.

  1. Click Save.

You can now use this file to configure relationships, add PivotTables, and explore other Microsoft Excel options to customize and create the required reports. For more information, see Microsoft Excel help.