Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Activenet API

Has anyone successfully connected to get data from Activenet using API? I could use some help and directions here.

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous ,

    Maybe you can try to use the following custom function to iterator all pages and combine then to one:

     

    let 
    looping=(rootpath as text,apikey as text,QueryString as text, optional tb as table)=>
    let	
        //QueryString="usage_date=2020-01-01",//query string filters and parameters
        //apikey="sqq35zvx6a8rgmxhy9csm8qj",//apikey used to verify api, this is a sample key from document
        //rootpath="http://api.amp.active.com",
        Source=Web.Contents(rootpath, 
        [RelativePath="/api/v1/membershipusages?"&QueryString&"&api_key="&apikey]
        ),
        page_info = Source[headers][page_info],
        cPage=Table.SelectRows(page_info,each [Name]="page_number"){0}[Value],
        tPage=Table.SelectRows(page_info,each [Name]="total_page"){0}[Value],
        Result=
            if cPage<=tPage 
            then 
                if tb <>null 
                then @looping(rootpath,apikey,QueryString&"&page_number="&Text.From(cPage+1),Table.Combine({tb,Json.Document(Source)})) 
                else @looping(rootpath,apikey,QueryString&"&page_number="&Text.From(cPage+1),Json.Document(Source))
            else tb
    in
	    Result
in
    looping

     

    Notice: I'm not so clear for the response table data structure, so you need to do some custom on 'Table.Combine' and 'Json.Document' functions to extract needed fields and records.

    Regards,

    Xiaoxin Sheng

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

    You can try to use following query formula if they suitable for your requirement:

    let 	
	QueryString="query=running&category=event&start_date=2013-07-04..&near=San%20Diego,CA,US&radius=50",//query string filters and parameters
	apikey="sqq35zvx6a8rgmxhy9csm8qj",//apikey used to verify api, this is a sample key from document
	Source=Web.Contents("http://api.amp.active.com", 
	[RelativePath="/v2/search?"&QueryString&"&kids=true&api_key="&apikey]
	),
	Result=Json.Document(Source)
in
	Result

    ACTIVITY SEARCH API V2 

    Regards,

    Xiaoxin Sheng

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks Anonymous . This is the first step. In my scenario, the response header returns total_results, items_per_page, total_pages. How do I extract this information from the header and put it in an iteration to get all the data? 

       

      Additionally, is there a way to increase items_per_page to reduce the number of calls?

       
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi Anonymous ,

        Maybe you can try to add them into the content parameter with detail API response settings:
        Specifying JSON Query in Power Query – Example Statistics Sweden 

        let 	
	QueryString="query=running&category=event&start_date=2013-07-04..&near=San%20Diego,CA,US&radius=50",//query string filters and parameters
	apikey="sqq35zvx6a8rgmxhy9csm8qj",//apikey used to verify api, this is a sample key from document
	bodypara="{}",//total_results,items_per_page,total_pages
	Source=Web.Contents("http://api.amp.active.com", 
	[RelativePath="/v2/search?"&QueryString&"&kids=true&api_key="&apikey,
	Content = Text.ToBinary(bodypara)]
	),
	Result=Json.Document(Source)
in
	Result

        For getting total records from pagination rest API, you can refer to the following blog:

        How To Do Pagination In Power Query 

        Regards,

        Xiaoxin Sheng