Forum Discussion

mrgou's avatar
mrgou
Frequent Visitor
5 years ago
Solved

A generic REST API connector?

I need to query data from a REST API but couldn't find any appropriate connector, or even a generic connector. Such a connector should be able to handle requests in any HTTP method, manage authentica...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi mrgou,

    You can try to use the following M query code if they work on your side:

    let
    rooturl = "https://myserver.com/",
    //profile id
    oath_oidc_profile_id = "xxxxxx",
    //access token from your portal configurations
    token = "xxxxxxx",
    relativeURL =
        "auth/oauth/session/"
        & oath_oidc_profile_id,
    //connect to server
    GetJson =
        Web.Contents(
            rooturl,
            [
                Headers = [
                    #"Authorization" = "Bearer" & token,
                    #"Content-Type" = "application/x-www-form-urlencoded"
                ],
                RelativePath = relativeURL
            ]
        ),
    //'session Id' for advanced operation
    sessionId = Json.Document(GetJson)[sessionId],
    Source =
        Web.Contents(
            rooturl,
            [
                Headers = [
                    #"Authorization" = "Bearer" & token,
                    #"Content-Type" = "application/x-www-form-urlencoded"
                ],
                RelativePath = "xxxx/xxxxx/xxxxx",
                Content = Text.ToBinary("client_id=" & sessionId)
            ]
        )
in
    Source

    If the above not help, you can also take a look at the following link about pull data from the rest API with authentication:

    Pull data from a REST API Authentication 
    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi mrgou,

You can try to use the following M query code if they work on your side:

let
    rooturl = "https://myserver.com/",
    //profile id
    oath_oidc_profile_id = "xxxxxx",
    //access token from your portal configurations
    token = "xxxxxxx",
    relativeURL =
        "auth/oauth/session/"
        & oath_oidc_profile_id,
    //connect to server
    GetJson =
        Web.Contents(
            rooturl,
            [
                Headers = [
                    #"Authorization" = "Bearer" & token,
                    #"Content-Type" = "application/x-www-form-urlencoded"
                ],
                RelativePath = relativeURL
            ]
        ),
    //'session Id' for advanced operation
    sessionId = Json.Document(GetJson)[sessionId],
    Source =
        Web.Contents(
            rooturl,
            [
                Headers = [
                    #"Authorization" = "Bearer" & token,
                    #"Content-Type" = "application/x-www-form-urlencoded"
                ],
                RelativePath = "xxxx/xxxxx/xxxxx",
                Content = Text.ToBinary("client_id=" & sessionId)
            ]
        )
in
    Source

If the above not help, you can also take a look at the following link about pull data from the rest API with authentication:

Pull data from a REST API Authentication 
Regards,

Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi,

I have two questions,

First, It is my understanding this would only work locally and not in the service. As it will treat "Web.Contents(<url variable>" in this case "Web.Contents(rooturl" as a dynamic connector and thus will not refresh.  Changing it to Web.Contents("https://myserver.com/" .. would result in the service removing this error. The service might still show the "invalid credentials error".  According to Chris Webb this can be fixed in code, but i am not sure how. The easier option is just to turn off the credential check in the service. It does need more testing but it seems to work.


Last question, is around this line here:

 

sessionId = Json.Document(GetJson)[sessionId]

 

I had a similar code:

 

Json  = GetJson(Url),  //Getjson return a Json.Document.
Value = Json[#"tickets"]

 

Which I tried to parameterize.

 

//this works
Value = Json[tickets]

//but this does not:
var = "tickets"
Value = Json[var]

//ended up with:
var = "tickets"
Value = Record.FieldValues(Record.SelectFields(Json, var)){0}

 

There has got to be single or at least simplier method... right?