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

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        I tried following both the options- getting error message - "method not allowed".

        Here's how far I get when I try to do it on my own. Entering the url as source returns a table with header and body. 

        Expanding the header returns a table with page info.

         

        let
        Source = Json.Document(Web.Contents("https://api.amp.active.com/anet-systemapi/****/api/v1/membershipusages?usage_date=2020-01-01&api_key=****")),
        headers = Source[headers],
        page_info = headers[page_info],
        #"Converted to Table" = Record.ToTable(page_info)
        in
        #"Converted to Table"

         
         
         
         

        On clicking the body, I get the actual data. How do I iterate the info from header to get the data?

        The odata method didn't work coz active doesn't use odata, I guess.

         

        let
        Source = Json.Document(Web.Contents("https://api.amp.active.com/anet-systemapi/****/api/v1/membershipusages?usage_date=2020-01-01&api_key=****")),
        body = Source[body],
        #"Converted to Table" = Table.FromList(body, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"customer_id", "customer_first_name", "customer_last_name"}, {"customer_id", "customer_first_name", "customer_last_name"})
        in
        #"Expanded Column1"