Forum Discussion

adam3039's avatar
adam3039
New Member
9 years ago
Solved

Pull data from RESTful API with token authentication

Is there any way to retrieve a token (providing a user/pass/grant-type as a urlencodedform) from a RESTful API, and then provide that token with subsequent API calls? We have a large RESTful API that...
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi adam3039,

     

    According to your description, you want to get the token of restful api, right?
    You can write a power query function to get token,then use this function as the parameter to call the api.

     

    For detail information about get token, you can refer below article:
    REST API Token-based Authentication

     

    Call API Sample:

     

    let
    CallAPi= (
        URI as text,
        Username as text,
        Token as text,
        Paremeter as table,
        optional Timeout as number
    ) as any => 
let
    WebTimeout = if Timeout = null then #duration(0,0,0,100) else #duration(0,0,0,Timeout) , 

    WebServiceContent = function(Paremeter),//format parameters to content

    WebResponse = Web.Contents(Username&":"&Token&URI, 
        [Content = Text.ToBinary(WebServiceContent),
         Headers = [Authorization="xxxxx",
                    #"Content-Type"="application/json",
                    Accept="application/json"],
         Timeout = WebTimeout]),
    
    output = formatfunction(WebResponse) //format the response
in
    output 
in
    CallAPi

     

     

    Regards,
    Xiaoxin Sheng

gilbertendaya's avatar
gilbertendaya
Helper IV
6 years ago

Hi guys..

 

Need your help on how to make this work in Power Query.

 

let
url = #"TOKEN URL",
GetJson = Web.Contents(url,
[
Headers = [#"Accept"="application/json",
#"Content-Type"="application/x-www-form-urlencoded;charset=UTF-8"],
Content = Text.ToBinary("username=*******&password=******&grant_type=password")
]
),
FormatAsJson = Json.Document(GetJson),

// Gets token from the Json response
AccessToken = FormatAsJson[access_token],
AccessTokenHeader = "bearer " & AccessToken,
GetJsonQuery = Web.Contents(#"Search URL",
[
Headers = [#"Authorization"=AccessTokenHeader]
]
),
FormatAsJsonQuery = Json.Document(GetJsonQuery)
in
#"FormatAsJsonQuery"


I tried to open it in Power BI, Blank query then input the above (URL and credentials were change), but it doesn't give me the results that I needed. It is just displaying the above scripts when I close and apply the power query window. Please advise.