LeanKit’s step-by-step guide on how to configure a connection to our reporting API via Microsoft Excel.
You can create a connection to the reporting API on either a PC or a Mac.
To create a live connection to the reporting API on a PC:
1. Open Excel 2016 and create a blank workbook.
2. Click on "Data" → "New Query" → "From Other Sources" → "From Web"
3. Ensure that the "Basic" radio button is selected in the dialog that appears, paste your generated URL from the Reporting API URL Generator into the "URL" field, and click "OK"
4. The "Access Web Content" dialog should appear, with the "Anonymous" tab selected. Click "Connect"
5. A "Connecting" dialog will appear while the request is made. Ensure that the data appears to have been parsed correctly on the resulting dialog, and if it is click the "Load" button
6. Your data should now be loaded into a new sheet in your Excel workbook.
7. You can then periodically click the "Refresh All" button to get updated data, as long as your token hasn't expired.
8. If the token does expire, simply edit/update the query URL.
To create a live connection to the reporting API on a Mac:
To create a live connection on the Mac version of Excel, you’ll have to have your query in a specifically formatted text file.
1. First, copy and paste your web query address in the first line of your preferred text editor.
2. Save the file as a Plain Text file.
3. If your text editor prompts you to specify how the file is encoded, select MS-DOS.
4. Once the file is created, change the file extension to “.iqy.”
5. To import the file into Excel, open a blank workbook, and click on "Data" → "Get External Data" → "Run Saved Query"
6. From there, select the file you want to import and click “Get Data.”
7. Your data should now be loaded into a new sheet in your Excel workbook. While the data is still loading you will see the following message. Once loaded you will see the full set of data.
8. All of the text may be condensed into one cell by default. To convert the text to fields, select the cell you’d like to convert and click Text to Columns at the top.
9. Text Wizard should determine that your text is delimited. Click Next.
10. Then check the box next to Comma, and then click Next.
11. Then click Finish to finish converting the text to columns.
12. You can then periodically click the "Refresh All" button to get updated data, as long as your token hasn't expired.
13. If your token expires, generate and copy a new url, paste it into the original word doc you created, and save.
Doc ID: 1193516608