Graph API in Excel for Office 365

Question

First of all, I am no developer. I manage an Office 365 server for my company and I would like to do some heavy reporting using excel.

I have learned of Microsoft Graph and how I could get my data with it.

I created an app in Azure Active Directory, and I tried to set it up. I added authorizations for Microsoft Graph, including Read All users full profile. I add one Read directory data permission for Windows Azure Active Directory too.

I clicked on the Grant Permission button.

In the manifest, I changed those parameters to true :

• oauth2AllowImplicitFlow
• oauth2AllowUrlPathMatching

Now from Excel (2016), I am trying to add a new OData Source, and tried

https://graph.microsoft.com/v1.0/users

I entered my credentials (as Global administrator) using the Professionnal Account tab, and unfortunately, I only get Resource Access Denied (Sorry if the message is not accurate, I get it in another language).

Could anyone give me a hand in telling me what I have missed, please ?

Thank you in advance.

Answer 1

I had similar issue and found that the problem might be that incorrect flow is being used to authenticate.

Warning: once you change your permission and grant them it may take up to 30 mins for the permission to be granted.

First of all, please check your access token (you can decode it easily using https://jwt.io. As a result you should receive this part in your decoded payload:

"roles": [ "User.Read.All" ],

If you don't see it it means that permission is not granted properly. As you wrote that you enter the credentials I assume that you use authorization code grant flow.

To resolve this you you can first check whether you granted application permission or delegated permission to your app. You can look at this page to see the difference. If you want to use application permission you can use client credential flow. If you want to use delegated permission you can use authorization code grant flow.

In application settings > Required permissions you can check which permissions you granted and correct it, if needed:

If you are using authorization code flow you can also add delegated permission to your app and check if new access code contains required role (keep in mind that some permissions might be available only in one of the categories - however, Users.Read.All is available in both).

Source