---

1 Introduction

This article is going to explain how to create a function app on Azure and use Excel API in Microsoft Graph. It shows how to use MS Graph explorer and postman to call function endpoints in Excel API. A demo project is explained how to process an existing excel file, Add a new row to a excel sheet, Calculate total in excel columns, Create a column and pie chart based on excel data.

↑Return to Top

---

2 Background

• If you love to start with .NET Core on a Linux server, check what are the basic things you have to do,Install .NETCore in a Linux server
• Say Hello from .NET Core application in a Linux server,.NET Core with a Linux server: Hello World!
• If you are interested in open source platforms and want to try out .NET Core application development in Linux, try with these steps,.NETCore: Create a web application with a Linux server
• If you want to know how to create an ASP.NET Core service application check this article,ASP.NETCore: Create a Web API application
• If you want to show a loading panel on the screen using ASP.NET Core, go through this article, ASP.NET Core : How to show a loading panel in a page
• If you like to use template generators for .NET Core this is the ideal article for you,.NET Core: Create web application with Yeoman template generator
• When you are developing ASP.NET Core web applications, security is a crucial thing, follow this article to know more about it,Secure your .NETCore web applications using IdentityServer 4
• Check how to show a confirmation dialog with jquery from this post,ASP.NET Core : How to show a confirmation dialog with jquery
• If you want to try out advanced features in jquery dialog on top of .NET Core, this will be a great article for you,ASP.NET Core : Advanced JQuery dialog actions
• If you are interested in user authentication & ASP.NET Identity follow this link,ASP.NET Identity: Customize User Authentication
• If you are interested to know about Azure storage, blobs check this article,Send smtp email with Azure blob storage attachments
• We are going to create a Function app and publish that in Azure, if you are curious to know more about Azure functions check this one,Azure Function App: Create a trigger on Cosmos DB
• If you are interested in Microsoft Graph and want to know more about Outlook API, check this article.NET Core: Building Function app with Microsoft Graph API and Azure Functions

↑Return to Top

---

3 Get profile information

You should have a Microsoft account to use Microsoft Graph. You can query on top of the data available in MS Graph as your emails, OneDrive files, Calendar events, Tasks etc. At first let's try to get your profile information.

↑Return to Top

---

3.1 Microsoft Graph

Microsoft Graph is the API for Microsoft 365 that provides access to all the data available in Office 365, we can connect to mail, calendar, contacts, documents, directories, users. Microsoft Graph exposes APIs for Azure Active Directory, Office 365 services like Sharepoint, OneDrive, Outlook, Exchange, Microsoft Team services, OneNote, Planner, Excel

↑Return to Top

---

3.2 Get profile details using Graph explorer

We can access to all these Office 365 products through a single REST endpoint and manage millions of data in Microsoft Cloud, Let's see how we can view profile information of your Office 365 account using Graph explorer

Navigate to Graph explorer,https://aka.ms/geClick on my profile section in sample queries, you can see profile details of a test account as below

Let's login to your office 365 account and view your profile information,

↑Return to Top

---

3.3 Get profile details in Postman

We connected to Graph explorer using Office 365 account and viewed profile information, Let's try to do the same using Postman
Postman is a tool that interacts with http APIs and helps developers to construct http requests and responses in a more easy way with authentication, different type of responses in different environments
Let's open Postman and try to get profile information, paste url in to the postman like this and click on Send button,https://graph.microsoft.com/v1.0/me/ It tells youAccess Token is empty, we have to get a valid access token with required permissions to call graph API,



Let's try to retrieve an access token by connecting to your Office 365 account, Go to Authorization tab in postman and click on available authentication type drop down. Select OAuth 2.0 from the drop down. OAuth 2 is an authorization framework allows third party applications to grant access to an HTTP service. In this case we are requesting access to Office 365 data by passing valid office 365 account details

You can see available OAuth 2.0 tokens as below, for our application let's try to get a new access token. Click on Get New Access Token button

You can see a window like this to retrieve an access token, At first we have to create an application in Application Registration portal in Microsoft, that app is going to act as an application layer to access all your Office 365 data. You can get anaccess tokenby providing application details and your Office 365 account details, let's see how we can do that. We have to give authorization urls and application specific details to get an access token,

We can assign a name for this token, in this example let's say its ProfileToken We have to provide a Authorization server url, url to your Office 365 accounthttps://login.microsoftonline.com/common/oauth2/v2.0/authorizeand a url to retrieve the acces tokenhttps://login.microsoftonline.com/common/oauth2/v2.0/token Then we have to tell about the application details that authorization server is going to give the access


Let's navigate to Microsoft Application managemenmt portalhttps://apps.dev.microsoft.com/and will create an application to access Office 365 data using your credentials

Let's try to register a new application, Click on Add an app, you can see a screen like this, specify the application name as sldevforum and click on Create button

You can see detail of the application you created as below, It shows Application Id & ways to generate Application secrets

Let's click on Generate New Password and retrieve a password to access this created application

Let's get back to the authentication token retrieval in Postman, we have registered an application in Microsoft Application registration portal, let's paste the Client ID and Client Secret as below

We have to provided the relevant scopes or permissions we should have in the token we are getting. That token should pass its value to the application namesldevforum and access files in your O365 account,passhttps://graph.microsoft.com/files.read as permission and request the token



You can see the prompt to login to your Microsoft account, click on it



Let's open Postman Console window to check what happens behind the scene



When trying to get the access token, it shows an error message like this, it says No reply address is registered for the application

We haven't specified a reply address to the application, If you check what is the Callback URLis, its the url that redirects after application is authorized

Navigate to Application registration portal, go to Platforms section, click on Add platform button,

You will get a screen to select the platform as below, let's go with Web option

You can see web platform section as below, we have to specify the redirect url in here,https://www.getpostman.com/oauth2/callback and save changes

You can see the access token is retrieved as below

Now lets add this token in Request headers, select that option as below

You can see access token is added in headers section with Authorization as the key, click on Send button. You will see the profile information like this

↑Return to Top

---

4 View files in your One Drive

Login tohttps://www.office.comyou can see all the office products as below

Click on OneDrive, you can see available files like this, now lets try to read sldevforum.xlsx file fromGraph explorer.

Opensldevforum.xlsxfile, you can view available table data like this

Lets' try to view available files in your OneDrive, you can see details of your drive as below,https://graph.microsoft.com/v1.0/me/drive

You can further query the drive and get more details, no of items in your file system etc, ping tohttps://graph.microsoft.com/v1.0/me/drive/root

We can call /children endpoint and get children in the root drive




We can pass filename and get file details as below, https://graph.microsoft.com/v1.0/me/drive/root/children/sldevforum.xlsx



You can navigate to /workbook endpoint and view available workbooks, https://graph.microsoft.com/v1.0/me/drive/root/children/sldevforum.xlsx/workbook



Navigate to /worksheets, you can see 3 worksheets are available https://graph.microsoft.com/v1.0/me/drive/root/children/sldevforum.xlsx/workbook/worksheets



You can retrieve the worksheet by passing the worksheet name as follows https://graph.microsoft.com/v1.0/me/drive/root/children/sldevforum.xlsx/workbook/worksheets/Year1



You can see the tables available in Year1 worksheet, let's try to retrieve this table rows by passing Range parameter,



Pass Range parameter like this, Range(address='Year1!A1:H11') with row and column indexes, from column A to H and row 1 to 11
https://graph.microsoft.com/v1.0/me/drive/root/children/sldevforum.xlsx/workbook/worksheets/Year1/Range(address='Year1!A1:H11')

↑Return to Top

---

5 Create Function App

5.1 Create basic solution 

Let's create a function App from Visual Studio and try to retrieve table information as we did using Graph explorer
Select Azure Functions v2 template from drop down and select Http trigger from the list, leave everything as it is and click OK



Let's publish this function to Azure, select Create New option to create a new function app in Azure



You can create app service by providing a App name, Azure subscription, Resource group, Hosting plan and Storage account as below, In this example I created a new Resource group, hosting plan and a storage account

↑Return to Top

---