Activenet API

[Anonymous]

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

[Anonymous]

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]

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]

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]

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"

[Anonymous · Accepted solution]

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