Connecting to an API

[Holguinmora]

Hi

I´m trying to connect to the next API using Blank Query but it’s been more complex that I thought. Is it possible for anyone to give me a hand or contact me to so I can Pay an be able to get the code code to create this connection?

On the next lines the info from the API, any help will be more than apreciate!!!

 On the next lines the API info:

Method: GET Path: https://api.repsly.com/v3/export/clients/{lastTimestamp}

Accept: application/json, application/xml

Content-Type: application/json, application/xml

User: 804144F2-78E3-47DE-9CCB-9B226634CD91

Password: 3B5A34D3-AD6D-496E-9125-4CAD9F8FXXXX

In each call the Api sends 50 lines - data base is no less than 600 lines.

To get the first set of data I change the {lastTimestamp} for a 0 as the next example https://api.repsly.com/v3/export/clients/0

On the next lines a short description on how I the Metadata must be implemented:

In every response you will receive meta variable LastTimeStamp of the last changed record in the list. Save it for future requests so you can use it as a parameter {lastTimeStamp} for every subsequent request until the response meta variable TotalCount is equal to 0, which means you have received all updates.

To get the complete list of clients, start your request with parameter {lastTimeStamp} = 0.

In this link the API manual: https://cdn2.hubspot.net/hubfs/391043/APIv3DeveloperDocumentation%20(4).pdf

In this link the field description:  https://api.repsly.com/v3/help/

Any help will be more than apreciated!!!

[Jimmy801]

Hello @Holguinmora 

were you able to solve the problem with any reply given?

If so, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

All the best

Jimmy

[JustinAiken]

If you use a scheduled refresh for this API, you may need to make the following alteration. Credit to Chris Webb.

Final = List.Generate
(
()=>
[
Result= Json.Document(Web.Contents("https://api.repsly.com/v3/export/clients/", [RelativePath="0", Headers=[Accept="application/json"]])),
TotalCount = 1
],
each [TotalCount]>0,
each [
Result= Json.Document(Web.Contents("https://api.repsly.com/v3/export/clients/", [RelativePath= Text.From([Result][MetaCollectionResult][LastTimeStamp]), Headers=[Accept="application/json"]])),
TotalCount = [Result][MetaCollectionResult][TotalCount]
],
each [Result][Clients]
)

Thank you @Jimmy801 

[Jimmy801 · Accepted solution]

Hello @Holguinmora 

as you passed me your credentials, I was able to develop something for your. I've tested it and it should work. Here the code

let
    Final = List.Generate
    (
        ()=>
        [
            Result= Json.Document(Web.Contents("https://api.repsly.com/v3/export/clients/0", [Headers=[Accept="application/json"]])),
            TotalCount = 1
    

        ],
        each [TotalCount]>0,
        each 
        [
            
            Result= Json.Document(Web.Contents("https://api.repsly.com/v3/export/clients/" & Text.From([Result][MetaCollectionResult][LastTimeStamp]), [Headers=[Accept="application/json"]])) ,
            TotalCount = [Result][MetaCollectionResult][TotalCount]
             

        ],

        each [Result][Clients]
    ),
    FinalTable = Table.FromRecords(List.Combine(Final))
in
    FinalTable

Copy paste this code to the advanced editor to see how the solution works

Give it a test and let us know

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

[Rolo_NewBi]

Oh joy! This works perfectly!!!! Thank you so much!!!! 

[Jimmy801]

Hello @Holguinmora 

we can have a try.

For sure List.Generate is needed to loop through all timestamps till TotalCount = 0.

I'm not able to test it, because I don't have access.

You could contact me with a private message

All the best

Jimmy