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

A First Look At The New Microsoft Graph Explorer – Part Three

Note: All my blogs on Microsoft Graph API can be found here.

In first two parts of my article series “A First Look at the New Microsoft Graph Explorer”, I explained about different components of Microsoft Graph Explorer. I suggest you to read those two parts first, before reading this third part for better understanding.

You can read the previous parts here:

I will continue the article series with part three where I will explain how to execute the calls to Microsoft Graph API.

The objectives of this article are:

  • Execute some Graph API GET calls using sample account
  • Look at Microsoft Graph API metadata
  • Calling beta API

Execute Graph API GET calls using sample account

Let’s look at some simple GET calls to Microsoft Graph API using the Graph Explorer.

Open the Microsoft Graph Explorer by clicking here.

Check the left section under “Authentication”.

Office Development

It says currently a sample account provided by Microsoft with some test data is being used. You can fire GET calls using this account right away.

If you see the API endpoint address bar you will notice,

Office Development

API endpoint for getting user’s profile is already loaded.

Simply click on “Run Query” button to execute the API call.

Office Development

You will see that the response area of the page is updated with something, like below.

Office Development

The status in the green background indicates that the call was successful with HTTP status code of 200 and executed in 869 mill seconds.

Look at the JSON data in “Response Preview” section.

It has some data of “user” which is the current user provided by Microsoft sample account. If you are logged in with your account, then you will see your data. I will cover the calls after login with your account later.

There is also something more.

Microsoft Graph API metadata

You might be wondering how to find out what data to expect in an API GET call response or what data to pass in an API call POST request. You can read the Graph API documentation or look at the sample queries on left side section, but there is also another way.

Look at the first line of the JSON response,

Office Development

You will see a URL for “@odata.context” property.

Copy the URL and paste in a new tab.

Office Development

You will see it loads an XML file. A big XML file. That’s the OData documentation of the Microsoft Graph API. Microsoft Graph API metadata in other words.

It specifies the different entities and actions along with properties and parameters.

Search for the following in the page- EntityType Name=”user”.

Office Development

You will find the user entity along with its properties and navigation properties. You will also find some more entities, more actions, functions. You can also see the Graph API metadata page directly here,

The following lines are taken from the Microsoft Graph website as it is:

“The metadata allows you to see and understand the Microsoft Graph data model, including the entity types, complex types, and Enums that make up the resources represented in the request and response packets.

You can use the metadata to understand the relationships between entities in Microsoft Graph and establish URLs that navigate between those entities.

Path URL resource names, query parameters, and action parameters and values are not case sensitive. However, values you assign, entity IDs, and other base64-encoded values are case sensitive.”

For the scope of this article, I will not go deeper inside this XML file and will leave it to you to explore more.

Back to the Graph Explorer

If you place your mouse cursor in the API address bar and press back space key and remove everything till “v1.0/” you will see the Graph Explorer will hint you possible API endpoints you can fire,

Office Development

Similarly, if you start typing “me/” then it will show you possible endpoints after “me”

Office Development

Now, type https://graph.microsoft.com/v1.0/organization in the address bar and press “Run Query” button, you will see the logged in user’s organization info,

Office Development

Please note that in Microsoft Graph API “me” and “organization” are the only two aliases in Microsoft Graph API i.e. these two are not the actual objects in Office 365.

If you want to test more GET calls, then you can see list of some GET calls in left section under “Sample Queries”.

Office Development

More GET calls featured scenarios can be found here on Microsoft Graph website,

Office Development

Calling API in preview

Now, let’s see how to call some Graph API endpoints which are still in preview i.e. in beta.

Change the API version to “Beta” in the version lookup,

Office Development

Copy and paste https://graph.microsoft.com/beta/me/insights/trending in the API endpoint address bar and press “Run Query” button,

Office Development

You will see the names of some documents are returned with their “weight”. These are the “trending” documents around the user and you need to write some code to convert the “weight” of each document to some meaningful representation to end user.

But the purpose of making this call is to make you understand how to fire a beta API call in Microsoft Graph Explorer.

Now change the API version to V1.0, keep the API endpoint URL same and fire the call,

Office Development

You will see it returns an error, because this endpoint is only in beta version and not yet in V1.0.

I hope you will play with Microsoft Graph Explorer and fire some more GET calls until I write the next article and we continue the journey of learning with the new Microsoft Graph Explorer.

Note: This article was first published by me on C# Corner website here.

Header image courtesy: Microsoft

Introduction to Microsoft Office 365 Developer Program

In this article, I will introduce you to MS Office 365 Developer program.

Background

The user base of MS Office 365 is increasing day by day by leaps and bounds. Microsoft needs to prepare more and more developers who work with Office 365. For this reason, Microsoft has designed the MS Office 365 Developer Program for the developers who build Office 365 solutions across desktop, web, and mobile platforms.

The MS Office 365 Developer Program can be accessed via the link https://dev.office.com/devprogram

MS Office 365

You can start registering for the developer program by clicking on “Join Now” button.

What are the benefits of MS Office 365 developer program?

The program offers many benefits. The most interesting one is one year free Office 365 developer subscription.

MS Office 365

You can also access some free online training on Office 365 too.

If you register now and till next few days, you will also get a chance to participate in a draw to win MS Ignite 2017 event.

MS Office 365

Why you should register

If you want to start with Office 365 development which is very much in demand now, then you will need a demo or test account for your programming. You may have access to Office 365 via your employer organization but that is the live organization data. You may not want to play with live data while you are in  the testing and training phase of some new programming.

Once you get the developer account, you can use it for various Office 365 related programming works, like:

  • MS Graph API
  • Office 365 API
  • Office 365 Add-ins

Microsoft is giving you a free one year subscription which you should not miss.

What you will get

Among other things described in one of the screenshots above, you will get “Office 365 Enterprise E3 Developer Trial” subscription with 5 users and US $10.40 user/month credit.

MS Office 365

Note: Shown above is a screenshot of my developer trial subscription from Office 365 Admin portal.

How to register

You can either click on “Join Now” button as shown in first screen or scroll down on the dev program home page and click on the “Join Developer Program >” button.

MS Office 365

Once you click on the “Join” button, you will be shown a registration page.

MS Office 365

Fill in your details and continue. Once you have completed the registration, you will get an email from Office Developer account.

MS Office 365

This email has a link to redeem your free Office 365 developer account. Click on the “Redeem today” link in the email to continue.

You will be shown the following screen.

MS Office 365

Fill in your details and continue.

You will see the following confirmation page.

MS Office 365

Please keep note of your user id which ends with “.onmicrosoft.com” which you will use to login to Office 365.

Office 365 Portal & Admin Portal

Once you have everything setup, you can visit the Office365 portal to access your account here.

MS Office 365

Note: The above shown image is my office 365 portal landing page. You can start using the Office 365 products right away.

You can visit the Office 365 admin portal here.

MS Office 365

You can manage users, groups, etc. from the Office 365 admin portal.

Finally, go to the billing section and check your subscription. It will show you how many number of days are left.

MS Office 365

Go now and create an Office 365 developer account, create some test data, and start programming with Office 365.

What’s stopping you?

 

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

Header Image Credit: C# Corner