Get Office 365 data in Excel using MS Graph API

Learn how to use MS Graph API in Excel

Objective:

Recently I wrote an article about 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 are going to 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 weekly basis, new employees join your organization and some leave too. The receptionist needs up-to-date information about all 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 list of employees which she can refresh anytime to get latest updates.

 Introduction:

What is MS Graph API?

Excerpt from https://developer.microsoft.com/en-us/graph/docs/concepts/overview

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 to https://developer.microsoft.com/en-us/graph

There is no coding involved to follow steps of this article. However, if you want to follow along 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 thru 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 PC.

Open MS Excel and create new workbook.

Go to “Data” tab in ribbon and click “Get Data” on left side:

GetData1

When “Get Data” menu expands, click on “From Other Sources”, then click “From OData Feed” as shown below:

GetData 2

Once you click on “From OData Feed”, you will see a dialog to enter OData feed URL:

Odata URL

Why we choose this option?

MS Graph API is based on open web standards and it supports OData V4, and MS Graph API accepts and returns data in JSON format, making it easy to integrate with other applications and technologies.

We want to access all the 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 textbox under URL and click OK:

Odata URL 2

Once you click on OK, you will see a dialog where you can specify your credentials to connect to MS Graph API:

Odata feed

Click on “Organizational account”, then click on “Sing in” button:

Odata feed 2

You will see “Office 365 Sign in” dialog:

O365 login

Don’t use your organization/live account to sign in. Use your Office 365 developer account to sign in.

After successful login, the “Office 365 Sign in” dialog will close, and your status on OData feed dialog will change to “singed in”:

Odata feed 3 Connect

Click on Connect button to continue.

It may take some time to fetch the result from MS Graph API call depending 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 result in a dialog.

For demo, 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 like this, filled with your organization’s users:

result dialog

Notice the “Load” button has a down arrow, click on it and you will see “Load” and “Load To…” options:

Load

Click on “Load To…” link, you will see an “Import Data” dialog:

import data

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 a new sheet has been added to Excel and data is populated:

excel result

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:

queries

Right click on “Query1” and click “Edit”:

Query Edit

You will see “Query Editor” is opened in a popup, click on “Choose Columns”:

Choose Columns

You will see “Choose Columns” dialog:

Choose Columns 2

Uncheck the very first “(Select All Columns)” checkbox, then select only below columns, then click OK:

  • displayName
  • jobTitle
  • mail
  • mobilePhone
  • officeLocation

You will see now Query Editor will only show the columns we selected in above step:

Close and load

Click on “Close & Load” button on top left to continue.

The Query Editor will close and you will Excel now shows you only those columns you selected:

excel chosen columns

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 office. She needs his details in this Excel too. What should she do?

For the 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”:

refresh

Excel will once again connect to MS Graph API and will fetch result and refresh the contents in worksheet:

new user

Do you see the user “Graph Explorer” now in the first row?

So, the reception’s requirement is fulfilled. Every time she wants 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 by yourself calling some other MS Graph API endpoints.

Meanwhile, if you want to read more of my articles on MS Graph API, please visit https://nilesh.live/blogs/msgraph/.