Forum Discussion

Holguinmora's avatar
Holguinmora
Helper I
6 years ago
Solved

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

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Jimmy801's avatar
    Jimmy801
    6 years ago

    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

5 Replies

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    6 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

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

       

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    6 years ago

    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

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    6 years ago

    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's avatar
      JustinAiken
      Frequent Visitor
      5 years ago

      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