OpenID connect OAUTH2 connector power query

[Macke Mot]

Hej

 

Jag  har försökt göra en connector för ett API som vi använder, det jag vill kunna göra är att läsa apidata i excel eller power bi. Jag har hittat en del guider på nätet men inget som verkligen lirar. Finns det någon som har möjlighet att hjälpa mig genom detta, jag kodar det i visualstudio 2019. Jag har pocat apit med cURL i linux och även powershell så jag vet att det funkar. Nedanför kommer den kod jag åstagkommit från en mall och en guied på nätet.

 

'

// This file contains your Data Connector logic
section Frida_connector;
// Frida connector OAUTH2 Values
client_id = Text.FromBinary(Extension.Contents("client_id.txt"));
client_secret = Text.FromBinary(Extension.Contents("client_secret.txt"));
redirect_uri = "oauth.powerbi.com/views/oauthredirect.html";
token_uri = Text.FromBinary(Extension.Contents("token_uri.txt"));
authorize_uri =Text.FromBinary(Extension.Contents("authorize_uri.txt"));
logout_uri = "https://login.microsoftonline.com/logout.srf";
username = Text.FromBinary(Extension.Contents("username.txt"));
password = Text.FromBinary(Extension.Contents("password.txt"));

// Login modal window dimensions
windowWidth = 720;
windowHeight = 1024;

// OAuth2 scope
scope_prefix = "";
scopes = {
    "fridaApi"};

[DataSource.Kind="Frida_connector", Publish="Frida_connector.Publish"]
shared Frida_connector.Contents = (url as text) =>
    let
       source = Json.Document (Web.Contents(url))
    in
        source;

// Data Source Kind description
Frida_connector = [
  TestConnection = (dataSourcePath) => { "Frida_connector.Contents", dataSourcePath },
    Authentication = [
        OAuth = [
         StartLogin=StartLogin,
            FinishLogin=FinishLogin,
            Refresh=Refresh,
            Logout=Logout
            ]
    ],
    Label = Extension.LoadString("DataSourceLabel")
];

// Data Source UI publishing description
Frida_connector.Publish = [
    Beta = true,
    Category = "Other",
    ButtonText = { Extension.LoadString("ButtonTitle"), Extension.LoadString("ButtonHelp") },
    LearnMoreUrl = "https://powerbi.microsoft.com/",
    SourceImage = Frida_connector.Icons,
    SourceTypeImage = Frida_connector.Icons
];

// Helper functions for OAuth2: StartLogin, FinishLogin, Refresh, Logout
StartLogin = (resourceUrl, state, display) =>
    let
        authorizeUrl = authorize_uri & "?" & Uri.BuildQueryString([
            response_type = "bearer",
            username = username,
            password = password,
            client_id = client_id,
            client_secret = client_secret,
            redirect_uri = redirect_uri,
            state = state,
            scope = "fridaApi"
        ])
    in
        [
            LoginUri = authorizeUrl,
            CallbackUri = redirect_uri,
            WindowHeight = 720,
            WindowWidth = 1024,
            Context = null
        ];

FinishLogin = (context, callbackUri, state) =>
    let
        // parse the full callbackUri, and extract the Query string
        parts = Uri.Parts(callbackUri)[Query],
        // if the query string contains an "error" field, raise an error
        // otherwise call TokenMethod to exchange our code for an access_token
        result = if (Record.HasFields(parts, {"error", "error_description"})) then 
                    error Error.Record(parts[error], parts[error_description], parts)
                 else
                    TokenMethod("Authorization=", "Bearer", parts

)

	    in

	        result;

Refresh = (resourceUrl, refresh_token) => TokenMethod("refresh_token", "refresh_token", refresh_token);

Logout = (token) => logout_uri;

// see "Exchange code for access token: POST /oauth/token" at https://cloud.ouraring.com/docs/authentication for details
TokenMethod = (grantType, tokenField, code) =>
    let
        queryString = [
            grant_type = "password",
            redirect_uri = redirect_uri,
            client_id = client_id,
            client_secret = client_secret,
            username = username,
            password = password,
            scope = "fridaApi"
        ],
        queryWithCode = Record.AddField(queryString, tokenField, code),

        tokenResponse = Web.Contents(token_uri, [
            Content = Text.ToBinary(Uri.BuildQueryString(queryWithCode)),
            Headers = [
                #"Content-type" = "application/x-www-form-urlencoded",
                #"Accept" = "application/json"
                           ],
            ManualStatusHandling = {400} 
        ]),
        body = Json.Document(tokenResponse),
        result = if (Record.HasFields(body, {"error", "error_description"})) then 
                    error Error.Record(body[error], body[error_description], body)
                 else
                    body
    in
        result;

Value.IfNull = (a, b) => if a <> null then a else b;

GetScopeString = (scopes as list, optional scopePrefix as text) as text =>
    let
        prefix = Value.IfNull(scopePrefix, ""),
        addPrefix = List.Transform(scopes, each prefix & _),
        asText = Text.Combine(addPrefix, " ")
    in
        asText;

Frida_connector.Icons = [
    Icon16 = { Extension.Contents("Frida_connector16.png"), Extension.Contents("Frida_connector20.png"), Extension.Contents("Frida_connector24.png"), Extension.Contents("Frida_connector32.png") },
    Icon32 = { Extension.Contents("Frida_connector32.png"), Extension.Contents("Frida_connector40.png"), Extension.Contents("Frida_connector48.png"), Extension.Contents("Frida_connector64.png") }
];
 

[Macke Mot]

Hej ja du det vet inte jag men det verkar så då denna kod fungerar men jag vill snygga till den och skapa en riktig connector för Power Bi och inte göra en ny anslutning för varje fråga. Koden nedan funkar i vart fall men önskar åstadkomma en connector med powerquery sdk extension i Visual studio

 

 

let
    token_url = "https://token.se",    
    api_base_url = "api.se",
    /frågevariabel?page=1&pagesize=1",
    body="grant_type=password&scope=<>i&client_id=<>&client_secret=<>&content-type=application%2Fx-www-form-urlencoded&password=<>25&username=<>",
   Source  = Json.Document(Web.Contents(token_url,
   [ 
     Headers = [#"Content-Type"="application/x-www-form-urlencoded"],
     Content=Text.ToBinary(body)
   ]
   )
   ),
    token = Source[access_token]
    ,

    counter = Json.Document(Web.Contents(api_base_url&qry_strcount,
      [ 
     Headers = [ #"Authorization"="Bearer "&token,#"Content-Type"="application/json"]     
      ]
    )
    ),
    totalcount=Number.ToText(counter[TotalCount])
,
   result= Json.Document(Web.Contents(api_base_url&qry_str,
    [Headers=[ #"Authorization"="Bearer "&token,#"Content-Type"="application/json"]]
    ))
in
   result