Print this page
In this topic
You can pro-grammatically load the application data into a Microsoft Excel spreadsheet using the Ringtail Connect API.
Tip: The application SDK includes a toolkit with sample code and explanations of additional ways that you can interact with the Ringtail Connect API using third-party applications. To get access to the UI Extension SDK, contact ringtail_sales@nuix.com.
Using the Ringtail Connect API 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 pro-grammatically 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.
Note: The following procedures contain only the information required to use the Ringtail Connect API with the Get & Transform feature in Microsoft Excel 2016. For earlier versions of Excel, use the Microsoft Power Query add-in for Excel. The information provided is subject to change. See the Microsoft documentation for the latest information.
Before you start, do the following:
●Obtain your application API token and API key from your system administrator. Your token and key authenticate you. For more information, see Obtain an API token and key for a user.
Caution: Store your API token and key in a secure location, the same way that you protect your password. Anyone who has your token and key can access all of the data that you can access through the API.
Use the following procedure to import the application data into Microsoft Excel.
1.In the Ringtail Connect API Explorer, 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 Reporting 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:
a.Add a header for your API token:
i.In the list, select Authorization.
ii.In the box, type beareryourAPIToken, where yourAPIToken is the API token that you received from your administrator.
b.Add a header for your API key:
i.Click Add header.
ii.In the list, type ApiKey.
iii.In the box, enter the API key that you received from your administrator.
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:
a.Click Into Table.
b.Drill down into a list until the word Record appears. Click To Table.
c.If you see a column that contains the values Record, expand the column so that all column names appear.
d.If you see a column that contains the values List, expand the column to new rows.
e.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.
After you import the application data into Microsoft Excel using the Ringtail Connect API, you can programmatically refresh the data that appears in a spreadsheet, without having to modify the data source configuration.
Use the following procedure 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.
