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

Anonymous's avatar
Anonymous
Not applicable
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

  • Breticious's avatar
    Breticious
    Helper I
    8 years ago

    Anonymous Xiaoxin,

     

    Wonderful sample! I'm having a devil of a time modifying it to my situation, though. Would you mind helping out? I have worked with several developers (myself included) and this code language is just foreign enought to cause a serious amount of fried brain power that you can probably translate in no time. Here are the variables being passed into your example to authenticate for the token:

     

    Authenticates the user

    https://[...]/api/AuthService/v1.0/Authenticate
    Request
    JSON Example
    {
	"UserName":"String content",
	"UserSid":"String content",
	"Password":"String content",
	"Realm":"String content",
	"AdfsPilotLoginCode":"String content",
	"IsInternal":true,
	"IsServiceUser":true
}

     The Response in JSON:

    {
	"Token":{
		
	}
}

     

    Afterward, I need to send a simple call to another website to get the table I need:

    https://[...]/api/[...]/v1.0/[...]/History?returnId={RETURNID}

     
     
    The Request body is empty.
     
    How is this all combined into your template?

     

    • Breticious's avatar
      Breticious
      Helper I
      8 years ago

      I was able to figure it out. Nasty stuff.

      • icassiem's avatar
        icassiem
        Post Prodigy
        2 years ago

        Hi Breticious 

        Please share your solution, i am battling to get it going

        regards

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Anonymous,

    Could you explain this bit clearly...like how to apply the function as a parameter etc?

     

    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//Which parameter?

    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 //how to format?
in
    output 
in
    CallAPi

    Thanks you.

    • Breticious's avatar
      Breticious
      Helper I
      8 years ago

      Does the key change upon each refresh of the data or does it remain static for every session?

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        hi Breticious,

         

        Thank you for the reply,ofcourse the key will change every time(Whenever the user login new token will generate)