Forum Discussion

otravers's avatar
otravers
Community Champion
6 years ago

Struggling to refresh API with Oauth, paging

Hi,   Despite spending hours on this I'm still not getting a full solution that handles Oauth bearer tokens + Paging + refreshes that work in the service. I was able to write M code that works in P...
Need Help
otravers's avatar
otravers
Community Champion
6 years ago

v-eachen-msft thanks, this helped, I thought using RelativePath was enough but in this instance apparently not. I then got the dreaded "Please rebuild this data combination" error, which I solved by moving the code to get the Oauth token into the main query. And finally this refreshes in the service! I'll have to write a blog post about this because I see many people struggling with similar scenarios.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    You sir are a legend!

     

    Had the same issue, privacy settings did nothing, security settings did nothing... mix of "References other step / query" and "function.firewall" errors...

     

    Moved the function which generates the API key inside the queries which use the API, and it works now!

    bit of duplicated code, but refreshes in service so I'm happy

     

    Example instance: paginated API query to MS Graph, fetching all users

    let
        makeToken = (#"Azure Graph API Url" as any, #"Azure Tenant ID" as any, #"Azure Application ID" as any, #"Azure Application Client Secret" as any) => let
            loginURL = "https://login.microsoftonline.com/",
            TokenUri = #"Azure Tenant ID" & "/oauth2/token", // which domain is this a token for
            ResourceId = #"Azure Graph API Url", // where is this token for
            TokenResponse = Json.Document(
                Web.Contents(
                    loginURL, [
                        RelativePath = TokenUri, 
                        Content = Text.ToBinary(
                            Uri.BuildQueryString([
                                client_id = #"Azure Application ID", 
                                resource = ResourceId, 
                                grant_type = "client_credentials", 
                                client_secret = #"Azure Application Client Secret"
                                ])
                        )
                        , Headers = [Accept = "application/json"], ManualStatusHandling = {400}
                    ]
                ) // end web contents
            ), // end json
            AzureAccessTokenB = TokenResponse[access_token] // assign token value
        in
        AzureAccessTokenB,
        GetPages = (Path)=>
            let
                Host = #"Azure Graph API Url",
                Source = Json.Document(
                    Web.Contents(
                        #"Azure Graph API Url"
                        , [RelativePath = Path, Headers = [Authorization = "Bearer " & makeToken(
                            #"Azure Graph API Url"
                            , #"Azure Tenant ID"
                            , #"Azure Application ID"
                            , #"Azure Application Client Secret"
                        )]]
                    )
                ),
                LL= @Source[value],
                Next = Text.Replace(Source[#"@odata.nextLink"], Host, ""),
                result = try @LL & @GetPages(Next) otherwise @LL
            in
                result,
            Fullset = GetPages("beta/users?$")
        in
            Fullset