This guide will show how to load a report (old Style) in Excel via API and convert the report to a table using the API v3..


Keyedin Projects: 

  1. In KeyedIn Projects first of all need to identify the report key so this key can be used to pull the report data via API. 

 

  1. If the report Key is not displaying then please click on the ‘Column Chooser’ and then move the ‘Key’ under displayed:

 

   

  

You also need to have ‘Integration User’ enabled against your login as this will then allow you to perform KeyedIn integration with other applications.

To find this setting:

  1. Select the Admin Menu and Select ‘Login’

 

 

  1. Open the Login record

 

 

  1. Select ‘System Roles’ tab and within here enable ‘Integration User’ Click on ‘Save and Close’  

 

When the changes are made, please log out and then log back into the system, you can now start to integrate with supported applications.

Microsoft Excel: 

**Please note the screenshots have been taken from Excel 2016 so screenshots may vary slightly 

  1. Open Excel  
  2. From the Excel ribbon bar click on the ‘Data’ tab  

 

  1. Under ‘Data’ click on ‘Get Data’ > ‘From Other Sources’ > ‘From Web’

 

  1. Click on ‘Advanced’

 

  1. On this screen you are required need to enter the API URL details and also specify the report key number

 

In the URL parts section input the following URL: https://api.keyedinprojects.co.uk/V3/api/report  

[Please note the URL will need amending depending on your site domain: e.g. if using the UK will use .co.uk, US will use .com and AUS will use.net in the URL] 

 

Second field input:

?key=91   [91 is the report key number]

 

The URL Preview box you will show the following: https://api.keyedinprojects.co.uk/v3/api/report?key=91 

 

The remaining fields are to be left unpopulated.

  1. At this stage authentication will be required, Select ‘Basic’ and input your  KeyedIn Project login details and then click on ‘Save’

 

  1. You will be presented with the following screen, click on ‘Load’

  1. This will then load the data into Excel as below:

 

 

 

 

The data can now be converted to a table to show the data in a table format.

  1. Select the table to populate further tools as shown below in the screenshot, click on the ‘Query’ tab and then click on ‘Edit’

 

 

This following will be presented and the data will be opened in a ‘Power Query Editor’ screen

 

 

 

 

 Excel – Power Query Editor 

  1. On the right hand column under ‘Applied Steps’ click on the Cog Icon

 

 

  1. This will show the following screen

 

From the ‘Open File as’ drop down list, select ‘JSON’ and then click on ‘OK’ This will then present the following screen:

 

  1. In the table where it says ‘Data’ click on ‘List’

 

This will present the following screen:

 

   

  1. Click on ‘To Table’

   

  1. Click on ‘OK’

 

 

 

 

 

 

 

 

You will be presented with the following screen:

 

 

                 

 

  1. Click on the following Icon

 

 

 

 

 

  1. On the following screen uncheck ‘Use Original Column name as prefix’   

 

 

Click on ‘OK’ 

 

 

 

The Data will now be presented in a table format as can be seen from the screenshot below:

 

 

  1. From the top left hand side click on ‘Close & Load’

 

 

 

 

The Report extracted via API has now been successfully converted to a table format in excel: