Print this page
In this topic
You can programmatically load Ringtail data into a Microsoft Excel spreadsheet using the Ringtail Connect API.
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 programmatically update the report every month to evaluate your license allocation.
●You want to perform calculations on Ringtail 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 Ringtail data in conjunction with data from other sources. For example, you can import Ringtail data into Excel, and then combine the data with data from other spreadsheets or third-party data sources.
To import Ringtail data into Excel, you query data from Ringtail 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 Ringtail 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 Ringtail data into Microsoft Excel.
1.In the Ringtail Connect API Explorer, create a query for the data that you want to import into Excel.
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 New Query > From Other Sources > From Web.
5.Select the Advanced option.
6.In the URL parts box, paste the query that you copied from Ringtail.
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 bearer yourAPIToken, 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.
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 Ringtail data is displayed in Excel.
12.Save the Excel spreadsheet.
After you import Ringtail 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 Ringtail data in Excel.
1.In Microsoft Excel, click the table of data that is imported from Ringtail.
2.On the Data tab, click Refresh All > Refresh.
The Ringtail data is updated in Excel.