My first year as a Microsoft MVP

On May 1, 2018, I got my first “Most Valuable Professional” award by Microsoft. I was so happy on that day when the email from Microsoft arrived confirming that I got MVP award in the “Office 365 Development” category:

mvp2

A few days later, I received a package full of MVP goodies from the Microsoft Canada team:

20180509_085247_HDR - Copy

And again after a few more days, I received my MVP award kit:

20180510_145805_HDR - Copy

 

It has been 1 year since getting an MVP award, and I am still a Microsoft MVP. I will continue to remain Microsoft MVP till June 30, 2019. When Microsoft announces MVP renewals on July 1, 2019, I hope to be renewed as an MVP once again.

So who gets a Microsoft MVP award? What does it mean to a Microsoft MVP?

There are many misconceptions in the mind of people in general about being a Microsoft MVP. I tried to answer a few of those “myths” below:

MVP Myth#1 MVP is a Microsoft employee

That’s the most common misconception which I encountered in the last one year. Many people who came to attend meetups hosted by me, or I met at technical events had that misconception – “Oh, you work for Microsoft, right?”. No, a Microsoft MVP is not a Microsoft employee.

MVP Myth#2 MVPs are paid by Microsoft to promote its technologies

Microsoft does not pay us anything to promote its technologies. It’s true that being a Microsoft MVP, any new technologies/products rolled out by Microsoft will be highlighted by us first. But it is out of passion, and that’s what made us MVP in the first place. There isn’t any monetary benefit involved.

MVP Myth#3 MVPs are experts in all Microsoft technologies

The MVP award is awarded mostly in one of the many categories. e.g. I am MVP in “Office 365 Development” category. There may be MVPs who are awarded in more than one category. But these categories mean that we had valuable contributions in those areas. It has nothing to do with the expertise of the person being awarded. It can be possible that an MVP has expert knowledge in his/her award category, but may not be in all others.

MVP Myth#4 MVPs earn a lot of money for being an “MVP”

It’s true that being an MVP gets you recognition and it is definitely going to help you in getting business or finding a job. But only “being” an MVP does not automatically earn you any money. You have to continue doing your best in your professional life.

 

So again, what does it mean to be a Microsoft MVP?

Being a Microsoft MVP means one is constantly contributing for the betterment of the technical community around him/her. That contribution can be in the form of hosting meetups, speaking/organizing conferences, writing blogs, mentoring and many more other avenues. Being an MVP means that person has done significant contributions in the public domain in one of the areas of MVP categories.

Which benefits do I get for being a Microsoft MVP?

As mentioned above there is no monetary benifit involved. But there are many other benefits one gets from Microsoft and its partners. Most of those are in terms of subsciptions to various online services or licenses for softwares.

I got invited to represent Microsoft at various events happening in and around Toronto. I represented Microsoft at “Hack the North” and “Disrupt AI” hackathons as a mentor and judge. I presented a session on Excel programming at “Microsoft Ignite The Tour | Toronto” in January 2019.

We also get invited to a big event called “MVP Summit” once a year which takes place at Microsoft HQ in Readmond near Seattle, US. We meet MVPs from across the world, get a chance to meet Microsoft employees and other prominent Microsoft personalities who are attending the MVP Summit. This year the MVP Summit was held from March 17 to 22, 2019 and I had a blast:

1

2

3

4

What’s Next?

In the coming year, I plan to keep continuing serving the technical community in the Greater Toronto Area. I wish to be renewed as Microsoft MVP again. Eagerly waiting for July 1, 2019!

Here is to coming many more years as a Microsoft MVP !!!

C# Corner MVP award (July 2018 – Jun 2019)

happy to be awarded C# Corner MVP once again!

I’m happy to be selected once again for C# Corner MVP award on July 1, 2018.

You can read the announcement in the news section on C# Corner website here.

CSCorner MVP 2018

What it means to be a C# Corner MVP?

It is a recognition for contributors on the C# Corner platform – website, forums, chapter events and webinars. It is an appreciation of hard work and contributions by members who make C# Corner a successful community. There is no monetary price or gain but the sense of recognition and fulfilment is great.

Why is it important to me?

C# Corner MVP was the first recognition/award I received since I started contributing to the technical community. This is my second time C# Corner MVP award. It gives a sense of fulfilment that your efforts/contributions are recognized and those don’t go unnoticed. Getting C# Corner MVP award mentally prepared me to take up the challenge to become Microsoft MVP.

Why I got C# Corner MVP award?

C# Corner MVP award is not limited to any technology or a specific domain. It is given to individuals for their contributions on C# Corner platform as well as to those who help spread the awareness about C# Corner by hosting public events either online or in-person.

Over past year, I have written many blogs/articles on C# Corner website, answered questions in the forums section, started C# Corner Toronto chapter and hosted many events under that chapter. Currently, it has 170+ members.

What’s next?

This award is a morale booster for me. I will continue to contribute to the C# Corner website articles and forums section. I am planning many more C# Corner chapter events and webinars. I am also planning to expand C# Corner Toronto chapter to include more speakers and guest speakers as well.

Sharing is caring!

Write your first program using Microsoft Graph SDK


Download source code associated with this article here- MSGraphSDKConsoleApp.


 

In this article, I will explain you how you can write your first program to interact with MS Graph API using MS Graph SDK in Visual Studio.

Microsoft Graph API is a powerful way to interact with Office 365 data. MS Graph API gives you a single REST API endpoint which you can call to interact with “almost anything” in Office 365.

Read my article here to know more about MS Graph API.

If you want to follow the steps mentioned here, then there are some prerequisites which you must have, otherwise you can continue reading.

Prerequisites:

  • Visual Studio 2017

Don’t have VS 2017? Download free “Visual Studio Community 2017” here.

Read my article how to install VS2017 here.

  • Office 365 subscription

Don’t have Office 365 subscription? Read my article here about how to get 1-year free Office 365 developer subscription.

Even if you have Office 365 account from your employer, I strongly suggest not to use it for development purpose.

  • Microsoft Graph SDK

MS Graph SDK is a class library with ready to use code for interacting with MS Graph API. It’s freely available as a NuGet package and can be easily added to your project from Visual Studio NuGet package manager.

Read more about MS Graph SDK in my article here.

 

Let’s get started.

Open Visual Studio. I am using VS 2017 community edition for this article:

1 VS 2017

Click on “Create new project” in start page:

2 Create project

Select “Console App”, give proper project name, click Ok to create a new project:

3 Console

Configure your Application

Once you have created a new project, you need to configure it so that authentication using Microsoft account (your Office 365 developer account) can be done in your application and you can receive a token required to access data using MS Graph API. For that you need to go to Microsoft App Registration Portal.

Please note these steps of registering your application are explained in detail in my articles here and here.

Open Microsoft App Registration Portal located at https://apps.dev.microsoft.com and login with your Office 365 developer account.

Click on “Add an app” button on top right:

4 add an app

Enter application name and click “Create”:

5 create app

Alternatively, you can also click on “Let us help you get started” checkbox and the wizard will guide you step by step.

A new application is created for you, please note it’s application id which we will need in next steps:

6 app id

Under the platforms, click “Add Platform” button:

7 platform

Select “Native Application”:

8 Native platform


Why we use “Native Application”?

Because we are writing code for a console application which is going to run on Windows OS. If it was a web application, then you would select “Web” and similarly “Web API” for an API.


You don’t need to make any more changes in MS App Registration Portal. Click on “Save” button at the bottom of the page.

Back to VS 2017 now. We need to configure our application with the application id we just created.

Open the App.config file. Add a new key “clientId” under appSettings, paste the application id from MS App Registration Portal in value section:

9 clientid

 Add MS Graph SDK to project

Simply search for “Microsoft Graph” in package manager and add “Microsoft.Graph” and “Microsoft.Graph.Core” packages.

Also add “Microsoft.Identity.Client” package as it’s required for authentication code.

Need more detailed steps? Read “Adding MS Graph SDK to your C# application” section of my article on MS Graph SDK here.

 

Write code

In the following section, I will explain you how to write simple code which calls MS Graph API to fetch logged in user’s details.

I will also explain code required to authenticate user using Microsoft account.

The first piece of code you need to write is to authenticate user and get access token:

static async Task<string> GetTokenAsync(PublicClientApplication clientApp)

{

//need to pass scope of activity to get token

string [] Scopes = {“User.Read"};

string token = null;

AuthenticationResult authResult = await clientApp.AcquireTokenAsync(Scopes);

token = authResult.AccessToken;           

return token;

}

Note here the most important code is clientApp.AcquireTokenAsync(Scopes) which will redirect user to Microsoft login page, show consent for permission(s) required by your application for given Scopes.

Then you need to write code to get MS Graph client to deal with MS Graph API:

PublicClientApplication clientApp = new PublicClientApplication(ConfigurationManager.AppSettings["clientId"].ToString());

GraphServiceClient graphClient = new GraphServiceClient(

"https://graph.microsoft.com/v1.0",

new DelegateAuthenticationProvider(

async (requestMessage) =>

{

requestMessage.Headers.Authorization = new AuthenticationHeaderValue("bearer", await GetTokenAsync(clientApp));

}));

Above code will get access token and pass it to Graph API client. Now you have client, simply make a call to MS Graph API:

var currentUser = await graphClient.Me.Request().GetAsync();

Console.WriteLine(currentUser.DisplayName);

Don’t forget to add following using directives at top of class:

using Microsoft.Graph;

using Microsoft.Identity.Client;

using System;

using System.Configuration;

using System.Net.Http.Headers;

using System.Threading.Tasks;

The whole code looks like this:

22 code

I have attached the whole project with this article. You can download it and view/run. Please note that this code is not suitable to be used in production application.

 

Run application

Now you are ready to run the application. Press F5.

You will be shown a dialog to enter your Microsoft account and password:

19 sign in

Note that we did not write any code to show this popup. It is handled by Microsoft Identity package; your application will never get user name and password.

Once you enter correct Microsoft account details or your Office 365 developer account user name and password, then you will be shown permission which the application requires, and it asks for your consent:

20 consent

Remember in above code, I have written “User.Read” in “Scopes”? That’s why user is shown that this application wants to “View your basic profile”. Click “Accept” to continue.

Hurray! You will be shown logged in user’s display name!

21 result

That’s it for this article. The purpose of this article was to make you aware how you can write simple most program to connect to Office 365 using MS Graph API and fetch some data.

For more detailed code and some samples, I suggest you visit MS Graph API samples page on GitHub here. Please note that some repositories are good to be used in your production application as well.

For more on MS Graph API, read my articles here.

What is Microsoft Graph SDK?

This article is a quick overview of MS Graph SDK. Using MS Graph SDK, you can easily write code which calls MS Graph APIs to interact with Office 365 data.

What is MS Graph API?

First, let’s see what MS Graph API is. The official MS documentation defines MS Graph API as:

“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”

In a nutshell, MS Graph API gives you a single REST API endpoint which you can call to interact with “almost anything” in Office 365.

To know more about MS Graph API, read my article here.

 What is MS Graph SDK?

Microsoft Graph Software Development Kit is “Microsoft Graph Client Library which allows you to call Office 365, Azure AD and other Microsoft services through a single unified developer experience.”

Using MS Graph SDK, you can quickly integrate MS Graph API in your application, so that you don’t have to write everything to deal with MS Graph API from scratch.

It provides ready-made classes and methods to be used while making Graph API calls. Please note that using MS Graph SDK is not “mandatory” if you want to code your application for MS Graph API. It just makes coding quick and easy.

What’s its availability?

MS Graph SDK is available for most leading development platforms e.g. ASP.Net, Xamarin, Android, iOS, JS, Angular, and many more. Day by day more platforms are being added.

This is the latest availability as of today:

Live availability:

Beta/Preview availability:

No SDK, only code samples available:

Keep watching the official Microsoft page here which mentions MS Graph SDK’s latest availability:

23 SDK

MS Graph SDK for .NET is available as “Microsoft.Graph” and “Microsoft.Graph.Core” NuGet packages:

24 nuget packages

Adding MS Graph SDK to your C# application

Let’s add MS Graph SDK for .NET i.e. MS Graph NuGet packages to a C# application.

Quickly create a new C# application (either console app or web app) in Visual Studio 2015/2017.

Right click project in Solution Explorer and click “Manage NuGet packages”:

10 NuGet

Go to “Browse” and type “Microsoft Graph”, press Enter key:

11 MS Graph package

Select “Microsoft.Graph” package from the result and click “Install” button on right side:

12 MS Graph package 2

It will show you “License Acceptance” dialog:

13 accept license

Note here that it will also install “Microsoft.Graph.Core” package, among others, as one of its dependencies. Click on “I Accept” button.

Output window will show success message once installation is finished.

14 success output

You have successfully added MS Graph SDK packages to your application.

You can make sure that by two ways.

Go to Solution Explorer and select “packages.config” file:

15 packages

See that “Microsoft.Graph” and “Microsoft.Graph.Core” packages are visible in config file:

16 packages file

Also, if you expand “References” in Solution Explorer then you will see Microsoft Graph references:

17 references

You will also need to install “Microsoft.Identity.Client” package as it’s required for authentication code because users are authenticated against Microsoft account.

18 Ms identity

Don’t forget to select “Include prerelease” checkbox!

That’s it! You have now successfully added MS Graph SDK and you are ready to start your coding.

To know how to write code to fetch Office 365 data using MS Graph SDK, read my article “Write your first program using Microsoft Graph SDK” here.

Testing and deploying an Office 365 add-in from network share

C-EdzGWUAAAEWCY

In this article, I will show you how you can load an Office 365 add-in from a network share for testing or live deployment.

If you are a developer who builds add-ins for Office 365, then you need to test those before deployment; and if you are developing for enterprise users then they need to access your add-ins after roll-out.

A network share (sharing your add-in from a network drive) is the easiest possible way for both of that. Read this interesting article where I show how to test and deploy Office 365 add-ins from a network share.

Prerequisites:

  • Either of:
    • Visual Studio 2015 or
    • Visual Studio 2017 (with Office Developer Tools)
    • Visual Studio Code (with Yeoman Office generator a.k.a. Yo Office!)
  • Office 365 add-in manifest (XML file)
  • A web server running your Office 365 add-in (web component)

Getting started:

If you have already created an Office 365 add-in, then you can skip this “Getting Started” section and jump directly to the next “Publish, Test & Deploy” section.

Create an Office 365 add-in using tools I have mentioned in prerequisites. Read below section for how to do that:

VS2015 office project

Give proper project name and click “Ok” button in above dialog.

Select “Task pane” from the next dialog:

VS2015 office task pane

Click next and select Excel from the next popup:

VS2015 office project finish

Click “Finish” and you are done!

  • Visual Studio Code with Yeoman Office Project Generator
    • Follow the instructions on Office Developer GitHub page here.

Publish, Test & Deploy

You can publish your Office 365 add-in web project on either IIS Server or on Azure.

For deploying web app on Azure, read another author’s article here.

For deploying web app on IIS Server, follow instructions here.

Or simply you can test it by pressing F5 from Visual Studio and let it run till you finish testing.

For showing you further instructions in this article, I have deployed my add-in web project on Azure.

All you need now is the URL on which your add-in web project is deployed. You need to configure it in the add-in manifest xml file.

Go to the solution explorer in VS 2017 and open the file location of the manifest file:

sol explorer

All you need is a copy of this manifest XML file. Copy it and paste it on any network share available for testing. I have shared it under “ExcelWebAddIn2Manifest” network share for my testing.

Open the manifest XML file in VS 2017 or any XML editor or simply Notepad, find “SourceLocation”:

source loc

You need to change the “DefaultValue” to the URL on which you deployed your add-in web app.

I have changed its value to point to my deployment URL:

defaultvalue

That’s the only change you need to make. Now let’s add this manifest file in Excel to see our add-in working.

Open Excel, create a new blank workbook. Go to File -> Options -> Trust Center -> Trust Center Settings:

trust center

Go to “Trusted Add-in Catalogs”, enter network share path in “Catalog URL”, then click “Add catalog”:

add catalog

Select “Show in Menu” checkbox, then click “OK”:

show in menu

Once you click OK button, you will be shown following message:

restart message

Click OK and close all dialogs, then close Excel and start it again. Create a new workbook.

Go to Insert ribbon then click “Store”:

store

Click “Shared Folder” link, and you will see your add-in. If not, then try clicking refresh and see:

shared folder

Select the add-in and click add button.

Excel will load add-in and show you message that it has loaded:

show task pane

Click on “Show Taskpane” and your add-in will be loaded:

add in loaded

Congratulations! Your Office 365 add-in is now ready to use. You can use the above steps for testing your add-in or while deploying in live environment too.

That’s it for this article. Happy learning!

 

 

Introduction to MS Graph API

MSGraph_Graphic.png

In this article, I will introduce you to the MS Graph API which is a powerful way to interact with Office 365 data.

Here are the objectives of this article:

  • What is MS Graph API?
  • How it came into picture?
  • What are its features, benefits and limitations?
  • MS Graph Explorer
  • MS Graph SDK

Let’s start with MS Graph API.

 

What is MS Graph API?

The official MS documentation defines MS Graph API as:

“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”

In a nutshell, MS Graph API gives you a single REST API endpoint which you can call to interact with “almost anything” in Office 365.

 

How it came into picture?

Prior to MS Graph API, Microsoft had different APIs for it’s different Office 365 and cloud products. Outlook, OneDrive, OneNote, Yammer all had their different APIs to interact with them.

Consider the following steps involved while dealing with different Office 365 APIs in an application:

  • Find the URL/endpoint of the API
  • Authenticate / get access token
  • Get results from the API
  • Repeat for each service
  • Manage multiple tokens for each API

It made a developer’s life difficult if one had to connect to many different APIs in an application.

As a solution, Microsoft launched the MS Graph API which is also called “One endpoint to rule them all”.

 

Why should you learn MS Graph API?

Office 365 is undoubtedly most widely used office produce worldwide. Millions of people are using the commercial versions of Office 365 suite or one or another of its products like Outlook, OneNote, etc.

On an average, a user in any office will use 2-3 hours working on different Office suite product like Outlook, Excel, Project, etc. Millions of new emails, events, etc. are generated in Outlook and millions of files are stored on OneDrive.

If you want to access all this information stored in Office 365 or on Azure, then you need MS Graph API.

 

Advantages

  • Single well-known resource

MS Graph API is the easiest way to interact with Office 365 data. Using MS Graph API, you can get mostly everything from a single endpoint https://graph.microsoft.com.

  • Ease of development

It liberates a developer from calling different Office 365 APIs and doing multiple token management.

  • Logical navigation

The entities of Office 365 are accessible in a logical way thru MS Graph API. Look again at the picture posted at the top of this article. You will see that once you access a user, all other entities related to it – files, messages, notes, etc. – are then accessible in a logical manner.

You don’t have to remember how an entity will be accessed, it follows a logical manner of graph structure.

  • Platform flexibility

MS Graph API does not mandate you to learn a new programming language. It can work with many different languages/platforms and Microsoft has launched SDKs to work with different platforms. You can work with MS Graph API in Windows/Android/iOS platforms. You can read more on it on last section of this article “MS Graph SDK”

 

Features

  • MS Identity Stack

MS Graph API does not introduce any new authentication mechanism where users have to go to register and then remember one more user name and password.

Instead, MS Graph API uses the Microsoft identity stack (which is used by 85% of fortune 500) – means that your users can login using the same familiar Azure AD / Microsoft Office 365 login credentials in your application.

MS Graph API uses Open ID Connect for authentication and OAUTH 2.0 for access tokens.

  • Open Standards

MS Graph API is based on and it supports many open web standards making it flexible and highly interoperable. Some of these standards are: 

REST – heart of MS Graph API implementation, any device/technology able to make http call and able to interpret JSON can work with MS Graph API 

JSON – lightweight format for request and response data 

OAUTH – provides API access token management, MS Graph API supports OAUTH v2.0 

ODATA – MS Graph API supports open data protocol v4.0 which is also called “The best way to REST”. It uses URL query string operators to make simple, standard and readable expressions.

I have written some articles on using optional ODATA query parameters with MS Graph API which can be found here:

 

  • Webhooks

Using webhooks, MS Graph API can send you notification for any changes in entities for which you have subscribed to. Instead of keeping on querying MS Graph API to see if there are any changes, you can use webhooks mechanism to have notification sent to you in case of any action/event happening. For example, you can subscribe to get notified when a new message arrives. 

  • Intelligence & Insights

MS Graph API does not only do simple input and output operations on your organization’s Office 365 entities. Instead of that, MS Graph API also has intelligence inbuilt in it which can help you to find treading documents around a user, people frequently contacted, etc. It can also help you find the best meeting times in a team.

  • Custom data extensions

You can extend MS Graph API results with custom data i.e. you can add custom data to MS Graph without using any external data source. This is helpful when you want to store some custom data relevant to your application e.g. profile setting or favorite theme, etc.

There are two types of extensions: Open extensions which are flexible and allows you to store any untyped data; and Schema extensions where you can define your own complex strongly typed schema, which can be shared between your different applications.

  • Delta query

Using delta query, it become easier to track data changes in entities. Instead of getting list of all entities and comparing them one by one to identify any changes, just fire a call using delta query which will give you only the changes i.e. inserted, updated, deleted data since your last call. 

  • Batching

Using MS Graph API’s JSON Batching you can combine multiple requests in one HTTP call and optimize application performance. Instead of looping thru multiple calls in your application, you can fire all related / unrelated calls in one batch in one go.

 

Limitations

The most visible one is some of its features are still in beta, but MS Graph team keeps launching new features in live regularly.

For a list of all MS Graph API known issues, refer here.

 

MS Graph API Explorer

Graph Exp

MS Graph API Explorer is a great tool to get started with MS Graph API and learn first-hand how it works. Graph Explorer is a developer sandbox where you can fire actual Graph API call and see its result.

You can access MS Graph API Explorer here.

I have written some articles on how to use MS Graph Explorer which can be found here:

 

Microsoft Graph SDK

MS Graph team has done some great work in launching SDKs for different platforms which makes development with MS Graph API easy.

You don’t have to code everything from scratch, ready to start code is available in SDK.

The following SDKs are available:

SDKs.png

If you are a java developer, then recently Microsoft has launched Java SDK for MS Graph API in preview.

If you want to add MS Graph SDK in any of your C# application, then open Visual Studio and simply search for “Microsoft Graph” in NuGet package manager to get started:

Nuget MS Graph.PNG

That’s it for introduction to MS Graph API in this article. Wait for my next article where I will show how you can start programming in MS Graph API.

Till then following links will be useful to get started with MS Graph API:

Using optional query parameters with Microsoft Graph API – Part 2

In this article, I will continue showing how to execute the calls to Microsoft Graph API using some optional OData query parameters.

The part-1 of this article series can be found here. I suggest you read it first, as this article continues from where I stopped in part -1.

Let’s now see the remaining query parameters one by one.

(5) $select

Explanation:

When you fire an API call to MS Graph, it will return the JSON result with all the properties. You may not need all the properties of a result set always. When you want MS Graph API to return only certain properties, then use $select parameter to specify which properties you want in result set.

Please note that the no. of records in the result will not be affected with or without $select parameter, only what’s returned for each record in result will change.

 Example:

Open MS Graph Explorer here and fire following call to get list of files from OneDrive:

See the data returned in JSON response:

OneDrive

Out of all these properties, what may be most important for you if you wanted to show the user his/her files from OneDrive? Of course, the name of the file/folder.

Fire following call in MS Graph Explorer to get only file name in result:

You will see the following result:

OneDrive name only.PNG

Now MS Graph API returns only file/folder name, instead of all the details. (Of course, “etag” is included but ignore it)

You can also use more than one property separated by comma, e.g.

And you will get following result:

OneDrive name size.PNG

If you type in the property that does not exist in the result set, then it will be ignored.

e.g. fire the following call and see what happens:

Usage:

In your programming, you can use $select query parameter to:

  • Get only specific property in result set (to make efficient use of bandwidth)
  • Specify those properties not returned in default result set

 

(6) $search

Explanation:

Using $search query parameter, you can specify free text search criteria for items being returned in result set. Please note that this parameter is currently supported for only “messages” and “person” entities. Microsoft Graph team will cover more entities later.

Some programmers are confused between $search and $filter parameters, because both do similar operations. I will explain the difference briefly in the next section for $filter.

When you specify $search parameter, it tells MS Graph API to include only those entities in result which match the specified expression. Here what to match is left for the implementer, OData specification does not mandate anything. Hence when you specify search criteria with $search query parameter, the result will be dependent on what MS Graph API team has decided to implement. Mostly it works as free text search.

Example:

Let’s say you want to find out emails which has “brainstorming” mentioned somewhere.

Open MS Graph Explorer here and fire following call:

See the data returned in JSON response:

search.PNG

Graph API will match the word brainstorming in an email’s from, subject and body fields, and will show the matching messages in the result.

Let’s see another example of using $search with “people” entity.

Open MS Graph Explorer here and fire following call:

See the data returned in JSON response:

people search

MS Graph API will search for the people related to the sample account user, will search for “langer” in display name or email, and will return matching records in result.

There are many more possible calls using the $search parameter, but I will restrict to there two calls only for brevity. I encourage you to explore more by yourself.

Usage:

In your programming, you can use $search parameter to:

  • perform free text search on messages or people related to user
  • perform fuzzy searches on people

 

(7) $filter

Explanation:

With $filter parameter, you can specify the filter criteria on a result set using logical operators or string functions. There are fixed logical and string operations which can be performed on a result set using $filter function.

The following extract is taken from the OData specification website here:

The $filter system query option allows clients to filter a collection of resources that are addressed by a request URL. The expression specified with $filter is evaluated for each resource in the collection, and only items where the expression evaluates to true are included in the response.

 Let’s understand the difference between $search and $filter parameters:

$search starts with an empty result set and adds items to it based on criteria that match, while $filter takes full result set, and removes items based on criteria that don’t match.

$search operates like a free text search, while $filter works on predefined logical and string functions.

Which logical and string functions are supported with $filter?

As per current MS Graph API documentation, following functions are supported, but the support varies as per which entity you are dealing with, so always refer to latest documentation on Microsoft site:

  • equals (eq)
  • not equals (ne)
  • greater than (gt)
  • greater than or equals (ge)
  • less than (lt), less than or equals (le)
  • and (and)
  • or (or)
  • not (not)
  • startswith
  • any

Example:

Let’s see some examples of how you can use $filter parameter.

Open MS Graph Explorer here and fire following call:

See the data returned in JSON response, it returns all people associated with the sample account whose job title is not set:

filter job title

Similarly, if you want to see only those records where job title is set, then simply change the operator “eq” (for equal to) to “ne” (for not equal to)

You will see the following response which will list all users who has job title assigned:

filter job title not null

Please note here that you see only 2 properties in the response, because I also used the $select parameter in the API call. You can use “&” in an API call when you want to use more than one optional query parameter: two parameters.PNG

There are many more uses of $filter parameter possible, but I will restrict to only two examples for brevity. I suggest you read more on Microsoft Graph API documentation page here.

Usage:

Use the $filter parameter in your programming to:

  • make API calls with logical operations for checking equality, greater than, less than, etc. operations

 

(8) $skipToken

Explanation:

When a response from MS Graph API does not contain all the available records (due to user explicitly specifying to select only some top records or due to server-side paging implemented in Graph API to limit response size), then you will see the response contains a server generated value with $skipToken parameter.

This value is to be specified in the next call to MS Graph API so that it will return the next set of records. The value of $skipToken is generated by server, it can not be specified by you as a consumer.

Example:

Fire the following call to get only top 10 files stored on user’s OneDrive:

See the result, note the value in @odata.nextLink property:

skip token value

The @odata.nextLink property contains the call which should be fired to get the next set of result. For identifying what should be returned, it has a skip token generated from server-side with some logic to skip those records in current response.

Simply fire the query mentioned in the @odata.nextLink property, and you will get next set of values.

Usage:

  • In your programming, you can use $skipToken to get paginated data (implement paging).

 

Limitations/Errors

Not all query parameters are supported for all entities. Always refer to latest Microsoft documentation for what’s supported. I also suggest firing the call in MS Graph Explorer first and see if you are getting the expected result or not, before starting any programming and later having any surprise.

If you include some query parameter for a non-supported entity, then MS Graph API will throw appropriate error.

Let’s understand with an example.

Fire following call in MS Graph Explorer:

You will get error 400:

 filter 123

 

Using query parameter without ‘$’ prefix in beta version of MS Graph API

With beta version of MS Graph API, using “$” prefix before a query parameter is optional.

Fire following call in MS Graph Explorer to get top 2 recently accessed files in result:

You will see the following result:

beta top

Note that the “top” query parameter is used without any “$” prefix here and still you get only 2 records in result.

This concludes my 2-part article series on MS Graph API query parameters. I hope you enjoyed learning about how to use query parameters in the MS Graph API call.

I suggest you keep reading more about MS Graph API until my next article.

You can read some of my articles on MS Graph API here.

 

Header image courtesy: Microsoft

This article may be published by me on other websites too.

 

Using optional query parameters with Microsoft Graph API – Part 1

How to use optional OData query parameters while working with MS Graph API

In this article, I will explain how to execute the calls to Microsoft Graph API using some optional OData query parameters.

The objectives of this article are:

  • What are the optional OData query parameters?
  • List of supported OData query parameters, when to use which one
  • Execute some Graph API calls using query parameters

While Microsoft already has the documentation to cover all its technology, in this article, I will try to explain these OData query parameters with some different angles and usage scenarios. I’m sure you will enjoy reading this article and learn something new.

Background

This article shows how you can fire some calls to MS Graph API using optional query parameters with the MS Graph Explorer available here:

Office Development

In my previous articles, I explained the different components of Microsoft Graph Explorer and showed how to execute MS Graph API calls using the sample account. I suggest you read those articles too for better understanding MS Graph API Explorer.

You can read my previous articles on MS Graph API Explorer here:

What are the Optional Query Parameters?

The current version of Microsoft Graph API supports the OData version 4.0 standard. This OData standard defines how the data is exchanged between the originator and the consumer of the REST APIs.

Look at the format of an API endpoint in Microsoft Graph API:

Office Development

Here “query-parameters” is an optional set of parameters to modify the response/output of API calls. It always starts with $ sign. (Please note here that in the beta version of MS Graph API, using $ is now optional. We will see that later in the article). Query parameters start after “?” sign.

The Microsoft Graph API documentation here describes the optional query parameters as:

“You can use optional query parameters to customize the response in your Microsoft Graph app. Use query parameters to include more or fewer properties than the default response, filter the response for items that match a custom query, or provide additional parameters for a method.”

The OData standards specification on www.odata.org defines the optional query parameters as “System Query Options” which are explained in great detail here.

The ODATA standards specification explains these query parameters as:

“System Query Options are query string parameters a client may specify to control the amount and order of the data that an OData service returns for the resource identified by the URI. The names of all System Query Options are prefixed with a “$” character.

An OData service may support some or all of the System Query Options defined. If a data service does not support a System Query Option, it must reject any requests which contain the unsupported option…”

In short, using these optional query parameters in the API endpoint query string you can define the criteria applicable on the result. And these parameters are optional meaning if you don’t mention any of them in the query string, then also the API call works fine.

If you are not aware of OData standard, then I suggest you to spend some time on reading about the OData specification on the odata.org website here for better understanding of OData concepts.

List of supported query parameters in Microsoft Graph API

The following query parameters are supported in the current version of Microsoft Graph API:

  • $count
  • $expand
  • $filter
  • $orderby
  • $search
  • $select
  • $skip
  • $skipToken
  • $top

A complete list with details can be found on Microsoft Graph API documentation website here.

Let’s now see each of the above query parameters one by one in detail and fire queries in Microsoft Graph API Explorer.

I will explain them in increasing order of complexity of understanding.

(1) $top

Explanation

This parameter will fetch only certain number of data items as specified along with $top.

At times, you will not need to fetch all the items from the result set, but only first few of them. In this case, $top query parameter is used to specify how many items should be returned in the result.

Usually, $top query parameter is used with $orderby query parameter (Read more about $orderby in section 3 below).

Example

Open MS Graph Explorer here and fire following calls

Notice the amount of data being returned in results in the above calls. In real life API call execution, it will return the current user’s emails and all files on his/her OneDrive. This may not be required always, and returning these many items may not be best suited option performance-wise too.

When you want to see only some limited number of records, at that time you can use $top.

Go to MS Graph Explorer here and fire the following call:

https://graph.microsoft.com/v1.0/me/messages?$top=2 or click here, then press “Run Query”.

You will see only two result items, not the list of all messages,

Office Development
Note: I have collapsed the JSON result in above screenshot to show items fit in one screen.

Usage

In your programming, you can use $top query parameter to:

  • Show recent/latest/top items from a list of items
  • Implement paging (often used with $skip and $orderby)

(2) $count

Explanation

This parameter will fetch the total number/count of data items in the specified API endpoint along with the result.

Note that the API call will return the result “as it is” without this query parameter, but if this parameter is specified, then in addition to the result, a number indicating the result count is also returned.

You will see the result count in @odata.count property in response JSON.

Example

Open MS Graph Explorer here and fire following call to get list of emails

It will show the following result,

Office Development

Notice here as highlighted in above screenshot, MS Graph API will return to you the count of emails as the value of property @odata.count.

Fire more calls in MS Graph Explorer to understand the $count parameter as shown below

You will see MS Graph Explorer does not return any count or does not return any error, it simply returns a valid result. If the $count parameter is not applicable on the API endpoint it will ignore it.

I leave it to you now to try and explore the $count parameter with different Graph API calls.

Usage

In your programming, you can use $count query parameter to:

  • Get the no. of result items in the result, along with result item(s)

(3) $orderby

Explanation

This parameter will make the Graph API return the result in a specific sorting order. Sometimes, you will want your result from MS Graph API to be sorted in a certain order depending on the date or name or some other property of the resource being returned. Use $orderby parameter in those cases to get your result sorted in certain order.

Example

Open MS Graph Explorer here and fire following call to get all users.

You will see all the organization’s users are returned here. They are in the order of email address. You may want to have the result ordered by another property, let’s say display name.

Execute the following call

You will see the list of users is ordered by the display name of user,

Office Development
Note: In above screenshot only one result fits in the screen. Try scrolling the response view by yourself to see all the items.

The default order by is done in ascending order. If you want to order by descending order, then specify “desc” in the URL separated by a space.

Execute the following call,

You will see the list is now ordered descending by the display name:

Office Development

Usage

In your programming, you can use $orderby query parameter to:

  • Get an ordered list of result items, based on certain property
  • Get emails, events, other items in ascending or descending sort order

(4) $skip

Explanation

This query parameter tells the MS Graph API to omit the number of records as specified along with $skip from the result set. MS Graph API will follow the same sorting order it follows (if $orderby is not specified) and will omit that many number of records from the result as specified in the query string.

Example

Open MS Graph Explorer here and fire following call to get the list of contacts:

It will show the result with all contacts of sample account. Note the result’s “displayName” values for first 4 records (they start with “A”).

Now run the following query to skip first 4 records from result

You will see the result without those first 4 contacts,

Office Development

MS Graph API has omitted first 4 records from the result set.

What happens when we specify a skip count greater than no. of records in the result? Let’s find out.

Run the following query to skip 10000 records from result

We know there are no more than 10000 contacts in the sample account. This is the result you will get, it simply returns an empty set

Office Development

Usage

In your programming, you can use $skip query parameter to:

  • Omit the first few items from a list of items
  • Implement paging (often used with $top and $orderby)

We will see the rest of the parameters in next article. Until then you can go to MS Graph Explorer and try the above examples by yourself.

You can also read more of my MS Graph API articles here.

 

This article was first published by me on C# Corner Website here.

Header image courtesy: Microsoft

Office 365 Development with Visual Studio 2017 – Part 2

When Visual Studio 2017 was launched earlier this year, I wrote an article to provide a glimpse of how to get started with Office 365 development with VS 2017 and different project templates available in VS 2017. You can read it here.

Let’s continue our journey of Office 365 development with VS 2017 in this article.

Objectives

In this article, we will walk through creating and running Office 365 projects in VS 2017. Along with that, I will explain Office 365 Add-in concepts side by side.

We will learn the following in this article.

  • Creation of Excel add-in projects
  • Brief intro of Office 365 Add-in
  • When you should choose which type of project

Prerequisite

This article assumes that you already have installed Visual Studio 2017 with Office 365 development option on your PC. If not, then please read part-1 and follow the installation steps. You will not see the project templates mentioned in this article if you don’t have the correct version of Visual Studio with Office 365 development option.

To make sure which version of Visual Studio you are using, go to “Help” menu and select “About Microsoft Visual Studio” sub-menu in Visual Studio.

Make sure you are using Visual Studio 2017.

Office Development

I’m using Community edition of VS 2017 which is “Free, fully-featured IDE for students, open-source and individual developers”.

Office Development
Image credit: Microsoft

You can download VS 2017 Community edition here.

The Office 365 development options are same in all editions of VS 2017, namely Community, Professional, and Enterprise. So, it will not matter whichever edition of VS 2017 you have for following the steps of this article.

Getting Started

Let’s start with creating a new Office 365 project for the add-in.

Either click on the menu “File” -> “New” -> “Project” or on the “Start Page” under “New Project” section, click on “Create new project…”:

Office Development

In the “New Project” popup, expand “Office/SharePoint” category under Visual C# and click on “Add-ins”.

Office Development

You will see project templates for Word, Excel, PowerPoint, Outlook, and SharePoint add-in.

Let’s briefly understand about Office 365 add-ins.

What is Office 365 add-in?

With Office 365 add-in or Office add-in, you can build program/solution that extends Office applications and interacts with the content in Office documents. You can add new functionality to Microsoft Office clients or create new rich, interactive objects that can be embedded in Microsoft Office documents. Office add-in runs in Microsoft Office across multiple platforms, including Office for Windows, Office Online, Office for the Mac, and Office for the iPad.

For more information on Office 365 add-ins, read on Microsoft website here. I will cover more on Office 365 add-ins in a separate article later.

In the New Project popup, select “Excel Web Add-in”, then click OK.

You will see a “Create Office Add-in” dialog with following two options:

  • Add new functionalities to Excel.
  • Insert content into Excel spreadsheets.

    Office Development

Let’s quickly see what this means. We will understand by inserting some add-in in Excel.

Open Excel and create a new workbook. Go to “Insert” -> “My Add-ins” -> “See All…”:

Office Development

“Office Add-ins” dialog will open. Click on “STORE”, enter “Wikipedia” in search, once Wikipedia add-in appears, click on “Add” button next to it.

Office Development

The dialog box will close and you will see now that the “Wikipedia” add-in is added under “Add-ins” ribbon.

Office Development

Click on it and on the right side, you will see a new section.

Office Development

Anything you can do on Wikipedia website, now you can do from inside Excel only using this add-in.

This is an example of “Extend functionality” type of add-in. These types of add-ins let you do some work right from Office applications without opening any other application. If you want to create this type of add-in, you should select “Add new functionalities to Excel” option in “Create Office add-in” dialog.

Now, let’s see some other add-in examples. Once again in Excel, go to “Insert” -> “My Add-ins” -> “See All…”. In the “Office Add-ins” popup, click on “STORE” tab and search on “bubbles”,

Office Development

Click on the first row “Add” button. You will see in the top right under “Insert” ribbon, a new “Excel Bubbles” add-in is available now.

Office Development

Click on it and a section with bubbles is added into current worksheet.

Office Development

Click on the “Sample Table” bubble in this section. It will add some sample data in worksheet and populate bubbles as per data,

Office Development

This is an example of “Content” type of add-in which adds some content inside an Office application. If you want to create this type of add-in then you should select “Insert content into Excel spreadsheets” option in “Create Office add-in” dialog.

Let’s go back to Visual Studio now. We were on this popup,

Office Development

Let the selection be “Add new functionalities to Excel”. Click “Finish”.

VS will create a new Excel add-in solution with 2 projects,

Office Development

Before understanding “what” these projects are and “why” they are created, let’s first understand the composition of an Office 365 add-in.

An Office 365 add-in consists of two components,

Office Development
Image credit: Microsoft

  • XML manifest file
    specifies settings and capabilities of the add-in
  • Web application
    interacts with Office clients and documents

Now coming back to Visual Studio, in VS 2017 Solution Explorer, you will see these two projects:

  • ExcelWebAddIn1
    This is the manifest project containing the XML add-in configuration/settings
  • ExcelWebAddIn1Web
    This is the project which has all code defining the add-in & its interaction with Office document. It has JavaScript and HTML files which define the UI of the add-in.

    To provide same look and feel in the add-in as Office 365 clients, it uses Office UI Fabric JavaScript. I will cover more on Office UI Fabric JS in separate article later.

Let’s now run the project to see it working. Press F5, VS will build the solution and launch Excel to make your add-in available inside it.

In Excel, “Show Taskpane” button will be focused with a tool tip message,

Office Development

Click on the “Show Taskpane” button. The task pane area will be loaded on the right side of Excel worksheet.

Office Development

Please note that when you created a new Excel add-in project, VS 2017 has included some sample code in it. That’s why you see the text and button here. Also, if you note the worksheet has been loaded with some sample data,

Office Development

This sample add-in is for finding and highlighting highest value cell among the selected ones. To see it in action, select the sample data and click on “Highlight” button,

Office Development

The JS code will get executed and highest value cell among the selected cells will be highlighted.

Stop the running project from Visual Studio and let’s now see the code which made it work.

In the Solution Explorer, double click the “Home.html” file.

This is the file which contains all UI elements. The texts and button you saw in Excel add-in are defined here,

Office Development

In the Solution Explorer, double click on home.js file.

This is the file which contains all code to interact with Excel.

Office Development

It sets the button text to “Highlight” and also sets the JS function which should be executed when someone clicks on it, among other things.

The load sample data JS function which populates the worksheet with some random numbers,

Office Development

The code for finding the highest value cell and highlighting it,

Office Development

See how simple it is to get started with Office 365 add-in development? You must give it a try yourself using VS 2017.

The purpose of this article was to make developers aware of Office 365 add-in development using VS 2017 which I have done above. You have to come up with the idea of your add-in to create a real-life Office 365 add-in which adds some value to the Office clients.

That’s it for this article. I will cover more add-in options in next article.

Note: This article was originally published by me on C# corner website here.

Featured image courtesy: C# Corner

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/.