Integrate Microsoft Excel with the Connect API

You can programmatically load the application data into a Microsoft Excel spreadsheet using the Connect API Explorer.

Using the Connect API Explorer to populate data in an Excel spreadsheet is useful in the following scenarios:

• You want to update a report with the most recent data on a recurring basis. For example, you can create a report of all currently enabled users and their last login date, and programmatically update the report every month to evaluate your license allocation.
• You want to perform calculations on the application data. For example, you can create a report of hosted data volumes by case, and use the functions in Excel to create custom invoices for your clients.
• You want to analyze the application data in conjunction with data from other sources. For example, you can import the application data into Excel, and then combine the data with data from other spreadsheets or third-party data sources.

To import the application data into Excel, you query data from the application using the Microsoft Excel Get & Transform feature.

Before you start

Before you start, do the following:

• Obtain your application API token. Your token authenticates you. For more information, see Obtain an API token.

  Warning

  Store your API token in a secure location, the same way that you protect your password. Anyone who has your token can access all of the data that you can access through the API.

Import Nuix Discover data into Excel

To import the application data into Microsoft Excel:

1. On the Connect API Explorer page, create a query for the data that you want to import into Excel. For information about how to create a query, see Request data: queries. For information about example queries that you can use as a starting point to create your own reports, see Data retrieval query examples.
2. To format the query, click the Format query button.
3. Click the Copy query to clipboard button.
4. In Microsoft Excel, on the Data tab, click From Web.
5. Select the Advanced option.
6. In the URL parts box, paste the query that you copied from the application.
7. In the HTTP request header parameters area, do the following:
   • Add a header for your API token:
     • In the list, select or type Authorization.

       Note

       Depending on the version of Microsoft Excel that you are using, if Authorization does not appear in the list, you may have to type Authorization. If so, the entry must be case sensitive.
     • In the box, type bearer yourAPIToken, where yourAPIToken is the API token you retrieved from your user account.

       Note

       You must include a space between bearer and yourAPItoken.
8. Click OK.
9. If an Access Web content dialog box appears, keep the default setting of Anonymous, and then click Connect.
10. In the Microsoft Query Editor window, depending on the structure of the query, do the following:
    • Click Into Table.
    • Drill down into a list until the word Record appears. Click To Table.
    • If you see a column that contains the values Record, expand the column so that all column names appear.
    • If you see a column that contains the values List, expand the column to new rows.
    • Repeat the previous steps until all data that you want to display in the spreadsheet appears. For more information about how to shape the data that you import, see the Microsoft Excel documentation.
11. When the data that you want to populate in the spreadsheet appears, click Close and Load. The application data is displayed in Excel.
12. Save the Excel spreadsheet.

Refresh Nuix Discover data in Excel

After you import the application data into Microsoft Excel using the Connect API, you can programmatically refresh the data that appears in a spreadsheet, without having to modify the data source configuration.

To refresh the application data in Excel:

1. In Microsoft Excel, click the table of data that is imported from the application.
2. On the Data tab, click Refresh All > Refresh. The application data is updated in Excel.