question

JanisFK avatar image
0 Votes"
JanisFK asked 35247258 edited

Access prohibited for "/schedule" endpoints accessing Microsoft Graph via Power Query

Hello,

thank you for your time!


I access Microsoft Graph via a Power Query in Microsoft Excel.

This works perfectly fine for all endpoints besides the ones under "/schedule" and itself. Every time I try to access data of the "Shifts" app, I get an insufficient rights error.

"Schedule.Read.All" (tested also with "Schedule.ReadWrite.All") is granted and admin consent also.

I use application-based permissions. If I test the request with my user account in Microsoft Graph Explorer it works fine. But there I test with my user account and not the application account.


Not working:

 let 
     token_uri = "https://login.windows.net/" & #"Azure AD Tenant ID" & "/oauth2/token",
     resource="https://graph.microsoft.com",
     tokenResponse = Json.Document(Web.Contents(token_uri,
     [
         Content = Text.ToBinary(Uri.BuildQueryString(
             [
                 client_id = #"Azure Application Client ID",
                 resource = resource,
                 grant_type = "client_credentials",
                 client_secret = #"Azure Application Client Secret"
             ]
         )),
         Headers = [Accept = "application/json"], ManualStatusHandling = {400}
     ])),
     access_token = tokenResponse[access_token],
     Source = OData.Feed(Text.Combine({"https://graph.microsoft.com/beta/teams/", #"Teams ID", "/schedule/shifts"}), [ Authorization = "Bearer " & access_token ], [ ExcludedFromCacheKey = {"Authorization"}, ODataVersion = 4, Implementation = "2.0" ])
 in
     Source



Working:

 let 
     token_uri = "https://login.windows.net/" & #"Azure AD Tenant ID" & "/oauth2/token",
     resource="https://graph.microsoft.com",
     tokenResponse = Json.Document(Web.Contents(token_uri,
     [
         Content = Text.ToBinary(Uri.BuildQueryString(
             [
                 client_id = #"Azure Application Client ID",
                 resource = resource,
                 grant_type = "client_credentials",
                 client_secret = #"Azure Application Client Secret"
             ]
         )),
         Headers = [Accept = "application/json"], ManualStatusHandling = {400}
     ])),
     access_token = tokenResponse[access_token],
     Source = OData.Feed(Text.Combine({"https://graph.microsoft.com/beta/teams/", #"Teams ID", "/members"}), [ Authorization = "Bearer " & access_token ], [ ExcludedFromCacheKey = {"Authorization"}, ODataVersion = 4, Implementation = "2.0" ])
 in
     Source

PS: Chris Hill wrote a very nice article how to connect Excel with Microsoft Graph



power-query-not-supported
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,
Your problem is related to code in Power Query/Power BI, I removed excel-it-pro tag. The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

1 Vote 1 ·

1 Answer

bezhan-msft avatar image
1 Vote"
bezhan-msft answered
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.