You can use Excel Power Bi to automatically load query data which is kept up to date every time you open the excel file. 

Once you have built the query, you'll need to create an API key that has access to this query. On how to create an API key, please see: http://help.peoplehr.com/integrations/creating-an-api-key 

You can then link the query within Excel.

1. Create a new excel document

2. Select the 'Data' tab

3. Select 'From Web'

5. Select the 'Advanced' option

6. Copy the URL shown below and paste into the 'URL Parts' text box

https://api.peoplehr.net/Query

Once clicking outside of the text box, your screen should look like this:

7. Click 'OK'. This will bring up the Power Query Editor:

8. Click the 'Home' tab and from here select the 'Advanced Editor', as shown below:

9. You'll then be presented with this screen:

10. Remove all text in this text box. 

11. Copy and paste the following text into this text box (please note that text in bold needs replaces with the specified information):

let  url="https://api.peoplehr.net/Query", 

body="{""APIKey"": ""ENTER API KEY HERE"",""Action"": ""GetQueryResultByQueryName"",""QueryName"":""ENTER QUERY NAME HERE""}",

Source = Json.Document(Web.Contents(url,[

         Headers = [ #"Content-Type"="application/json"],

            Content = Text.ToBinary(body)

            ]

        ))

in Source


12. Click 'Done'.

13. If loaded successfully, the data will be available in the Result section.

14. Click the highlighted “List” link:

15. Select the “To Table” button:

16. From here, ensure the delimiter is set to 'None' and then click 'OK':

17. The loaded items are presented as a list of Records. To enable a more detailed view of the records, click the highlighted Split into Columns button:

18. Include the wanted columns from the presented list and uncheck the “Use original column name as prefix” setting:

19: Data is now loaded in a table:

Thanks,

Customer Services Team.

Did this answer your question?