Recently, I wrote an article on how you can consume MS Graph API in Power BI (read it here). MS Excel can also consume Microsoft Graph API in the same way.
In this article, I will show how you can quickly fetch Office 365 data of your organization in MS Excel using Microsoft Graph API. We will fetch an organization’s users list in MS Excel with MS Graph. Once you learn to use MS Graph API in MS Excel, you can explore more options by yourself.
To make this article easy to follow, let’s identify a real-world requirement and then see how we can solve it using MS Graph API and Excel.
Requirement
Suppose you are working in the IT department of an organization who employs 500+ users. On a weekly basis, new employees join your organization while some leave too. The receptionist needs up-to-date information about all the employees and their contact details. You are asked to provide her a simple solution. You create an Excel workbook for her using MS Graph API data feed which will show the list of employees which she can refresh anytime to get the latest updates.
What is MS Graph API?
Excerpt from here.
“You can use the Microsoft Graph API to interact with the data of millions of users in the Microsoft cloud. Use Microsoft Graph to build apps for organizations and consumers that connect to a wealth of resources, relationships, and intelligence, all through a single endpoint: https://graph.microsoft.com”
For more on MS Graph API, please go here.
There is no coding involved to follow steps of this article. However, if you want to follow along with the steps, then it’s better to have Office 365 developer account as mentioned in “Prerequisite” section below.
Prerequisite
- Office 365 developer account
You may have access to Office 365 through your employer/organization account. However, it is strongly advised that you don’t use your live/organization account to follow steps of this article. Instead, use Office 365 developer account. Use your live/organization account only when working in a production environment.
Read my blog on how to get Office 365 developer account for 1 year free here.
Getting Started
I assume you have Office 365 developer account and you also have MS Excel installed on your computer. Open MS Excel and create a new workbook.
Go to “Data” tab in ribbon and click “Get Data” on left side.
When “Get Data” menu expands, click on "From Other Sources >> From OData Feed”, as shown below.
Once you click on “From OData Feed”, you will see a dialog to enter OData feed URL.
Why we choose this option?
MS Graph API is based on open web standards and it supports OData V4. MS Graph API accepts and returns data in JSON format, making it easy to integrate with other applications and technologies.
We want to access the list of all users of an organization. The MS Graph API endpoint https://graph.microsoft.com/v1.0/users returns all users of an organization.
Enter https://graph.microsoft.com/v1.0/users in the text box under URL and click OK.
Once you click on OK, you will see a dialog where you can specify your credentials to connect to MS Graph API.
Click on “Organizational account”, then click on “Sign in” button.
You will see “Office 365 Sign in” dialog.
Don’t use your organization/live account to sign in. Instead, use your Office 365 developer account.
After successful login, the “Office 365 Sign in” dialog will close, and your status on OData feed dialog will change to “signed in”.
Click on "Connect" button to continue.
It may take some time to fetch the result from MS Graph API call depending on your internet connection, but it will not be more than a few seconds. Once MS Excel fetches the users using MS Graph API, it will show you the result in a dialog.
For demo purposes, I have created some users in Office 365 Admin Portal using my developer account. I suggest, you also create some demo users with your Office 365 developer account using Office 365 Admin Portal.
You will see a result dialog filled with your organization’s users like the following.
Notice that the “Load” button has a down arrow. Click on it and you will see “Load” and “Load To…” options.
Click on “Load To…” link by which you will see an “Import Data” dialog.
This dialog has options for how you want to view the data and where you want to place the data. You can import the data to new worksheet too. We will not do anything special in this dialog. I just wanted to show you the options available in Excel. Click on OK button and the dialog will close.
As “New worksheet” was selected in “Import Data” dialog, you will see that a new sheet has been added to Excel and data is populated.
What has happened here?
MS Excel has received the JSON data result from MS Graph API in response to the call to https://graph.microsoft.com/v1.0/users endpoint, and converted it to a data table for you. What you see here is the list of all properties it got from MS Graph API.
By default, Excel will load all the columns it received from MS Graph API, some columns will not have data and you will not want to display all the columns. We will see in some time how you can choose only some columns to be displayed.
Also, if you note on right side new section “Queries & Connections” has been added.
Right click on “Query1” and click “Edit”.
You will see that “Query Editor” is opened in a popup, click on “Choose Columns”.
You will see a “Choose Columns” dialog.
Uncheck the very first “(Select All Columns)” checkbox, then select only the below columns, and click OK.
- displayName
- jobTitle
- mail
- mobilePhone
- officeLocation
You will see now that the Query Editor will only show the columns we selected in the above step.
Click on “Close & Load” button at the top left to continue.
The Query Editor will close and your Excel now shows you only those columns you selected.
Good job! You got your organization’s data in Excel using MS Graph API. How simple it was!
Now, let’s come back to the receptionist’s requirement I mentioned at the start of the article. A new employee has just joined the office. She needs his details in this Excel too. What should she do?
For this demo to work, I have opened Office 365 Admin Portal and added a new user named “Graph Explorer” to my organization using Office 365 developer account. I suggest you also add a new demo user to your developer account using “Office 365 Admin Portal” -> “Add a user” link.
After adding a new user in Admin Portal, right click on “Query1” in Excel and click “Refresh”.
Excel will once again connect to MS Graph API to fetch the result and refresh the contents in the worksheet.
Do you see the user “Graph Explorer” now in the first row?
So, the reception’s requirement is fulfilled. Every time she wants the latest data, she has to just hit “refresh” and MS Graph API will do the rest.
What’s next?
The purpose of this article was only to show you how MS Graph API data can be consumed in MS Excel, which I have shown above. Similarly, you can try calling some other MS Graph API endpoints by yourself.
Meanwhile, if you want to read more of my articles on MS Graph API, please visit here.
Fabien TurcottePosted Oct 1, 2018, 4:58 PM
I have the same problem as Sajjad. Error msg: Access to the resource is forbidden. However, I can run the query directly in Graph and it works OK. Any idea?
sajjad ahmadPosted Sep 21, 2018, 12:53 AM
I am getting error message as "We could not connect to the datta source. Please check your logon information. The remote server retunred an error 401 unauthorzied" while connecting.
sajjad ahmadPosted Sep 21, 2018, 12:01 AM
Great article and nice way to present the information.