Calling two different APIs with different authentication requirements

[CMAltium]

Hi,

I have set up OAuth 2 in a power query connector to work with refresh tokens, to access reports from an API and show them in a table. This works fine.

However, the URL to access the reports is too long, and gets truncated.

I have tried to implement tinyUrl's API in a separate function to shorten the URL.

This caused an authentication issue, the function does not run, and the connector will fail with "We couldn't authenticate with the credentials provided. Please try again."

I then created a new power connector for just URL shortening, and when I try to use the connector, I get the pop up:

"Credentials are required to connect to the tinyUrl source. (Source at tinyUrl.)".

I can set the credentials using the button "Power Query SDK" > "Set Credential" in the bottom left of Visual Studio.

I believe that this manual step is causing the error in my original code.

Is there a way to set these credentials programmatically, so when my original code reaches the function for URL shortening, the credentials can be set and the script continues to run.

Thank you,

Chung

[CMAltium]

Hi,

Can anyone help? Thanks.

[AmiraBedh]

Create a new function in Power Query to handle the TinyURL API authentication and URL shortening and define the authentication mechanism for the TinyURL API.

// Function to get TinyURL shortened URL
let
    TinyURL_API_Key = "YourTinyURLApiKey",
    BaseURL = "https://api.tinyurl.com/create",

    
    ShortenURL = (LongURL as text) as text =>
    let
      
        Body = "{""url"":""" & LongURL & """, ""domain"":""tiny.one""}",
        Options = [
            Headers = [#"Content-Type"="application/json", #"Authorization"="Bearer " & TinyURL_API_Key],
            Content = Text.ToBinary(Body)
        ],
        Response = Json.Document(Web.Contents(BaseURL, Options)),
        ShortenedURL = Response[short_url]
    in
        ShortenedURL
in
    ShortenURL

Then you call it in your main PQ :

let
    Source = Web.Contents("https://your-main-api.com/reports", [Headers = [Authorization = "Bearer " & AccessToken]]),
    Data = Json.Document(Source),
    Reports = Data[reports],
    
    LongURL = "https://your-main-api.com/reports/details?reportId=1234567890",

    ShortenedURL = ShortenURL(LongURL),

    FinalData = Table.AddColumn(Reports, "Shortened URL", each ShortenedURL)
in
    FinalData

Connecting to an OAuth API Like PayPal With Power Query • My Online Training Hub

How to configure OAuth 2.0 Authentication for Microsoft Power Platform Custom Connectors

Handling authentication for Power Query connectors - Power Query | Microsoft Learn

[CMAltium]

Hi,

I tried your example, but had the same issue as before.

I have written a function very similar to the one that you shared. 

However, when I try and run it within my script which uses an API with different credential requirements, it causes an issue in Power BI Desktop:

Here is an example snippet of my code:

//
// Data Source definition
//
[DataSource.Kind="Nexar_PKCESample", Publish="Nexar_PKCESample.Publish"]
shared Nexar_PKCESample.Contents = (url) =>
    let
        accessToken = Extension.CurrentCredential()[access_token],
        url = "https://api.nexardev.com/graphql",
        
        contents = GetReport(accessToken)
    in
        contents;

//
// Data Source Kind definition
//
Nexar_PKCESample = [
    Authentication = [
        OAuth = [
            //
            //Authentication for reporting API
            //
            StartLogin = StartLogin,
            FinishLogin = FinishLogin,
            Refresh = Refresh
        ]
    ],
    Label = Extension.LoadString("DataSourceLabel")
];


//TinyURL URL shortening
GetReport = (accessToken) =>
    let
        //...
        //Original script to get URL from a different API using the accessToken
        //...

        //Long URL from script above
        longUrl = "https://www.examplelongurl.com",
        shortUrl = shortenUrl(longUrl)
    in
        shortUrl;

//Script for URL shortening
shortenUrl = (longUrl) =>
    let
       
        tinyUrl = "https://api.tinyurl.com/create?api_token=<MYTOKEN>",
        json_data = Json.FromValue([url = longUrl]),
        response = Web.Contents(
            tinyUrl, 
            [    
                Content = json_data,
                Headers = [#"Content-Type" = "application/json"]
            ]
        ),
        #"JSON" = Json.Document(response),
        shortUrl = #"JSON"[data][tiny_url]
    in
        shortUrl;

Here is the script for the self contained connector to tinyUrl:

// This file contains your Data Connector logic
[Version = "1.0.0"]
section tinyUrl;

[DataSource.Kind="tinyUrl", Publish="tinyUrl.Publish"]
shared tinyUrl.Contents = (optional message as text) =>
    let
         longUrl="https://123testing.com",
        tinyUrl = "https://api.tinyurl.com/create?api_token=XcBFxMQDD4cmCQRavoSQdpiBjbdlkOElemeym5ObvwbvcAckIng7IyIejAE1",
        tinyUrlToken="XcBFxMQDD4cmCQRavoSQdpiBjbdlkOElemeym5ObvwbvcAckIng7IyIejAE1",
        json_data = Json.FromValue([url = longUrl]),
        response = Web.Contents(
            tinyUrl, 
            [    
                Content = json_data,
                Headers = [#"Content-Type" = "application/json"]
            ]
        ),
        #"JSON" = Json.Document(response),
        shortUrl = #"JSON"[data][tiny_url]
    in
        shortUrl;

// Data Source Kind description
tinyUrl = [
    Authentication = [
        // Key = [],
        // UsernamePassword = [],
        // Windows = [],
        Anonymous = []
    ],
    Label = "tinyUrl connector"
];

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

tinyUrl.Icons = [
    Icon16 = { Extension.Contents("tinyUrl16.png"), Extension.Contents("tinyUrl20.png"), Extension.Contents("tinyUrl24.png"), Extension.Contents("tinyUrl32.png") },
    Icon32 = { Extension.Contents("tinyUrl32.png"), Extension.Contents("tinyUrl40.png"), Extension.Contents("tinyUrl48.png"), Extension.Contents("tinyUrl64.png") }
];

When I run only that function in its own, self contained connector, I see this:

I can set the credentials using "Power Query SDK" > "Set Credentials" in the bottom left corner of Visual Studio.

Is there a way to do that programmatically, rather than manually?