cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Connecting to an API

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!!!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User I
Super User I

Re: Connecting to an API

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

View solution in original post

3 REPLIES 3
Highlighted
Super User I
Super User I

Re: Connecting to an API

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

Highlighted
Super User I
Super User I

Re: Connecting to an API

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

View solution in original post

Highlighted
Super User I
Super User I

Re: Connecting to an API

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

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.