How to Use Graph API to Read SharePoint Online Excel Data from Microsoft Flow

Microsoft Flow provides many different actions to interact with SharePoint Online list and libraries. Recently, we had a requirement where excel data to be processed as soon as user upload the file to the document library.

Limitation/Issues

Initial idea was to utilize the Excel Online (Business) connector related actions like “List rows present in a table” in MS Flow but soon we realized that it does not work well for dynamic file. In case excel file is already present in the library and specific file is predefined in the flow, it works well. However, when we try to process the newly uploaded file from SPO, it does not work.

Solution

Microsoft Graph API came as a saviour to overcome this situation. Graph API provides the API methods to read excel data from SharePoint Online. The tricky part here is to establish authentication and authorization between MS Flow Graph API call and SharePoint Online. We can configure Azure AD app to achieve the same. Here are the step by step guide to be performed.

Step – 1: Get the Prerequisites ready

We would need to create the app and record the following information which are prerequisite to connect Graph API from MS flow:

A. Active Directory Tenant ID
B. Client ID
C. Client Secret

A. Active Directory Tenant ID

• Login to Azure portal (https://portal.azure.com/) with a global admin account
• Navigate to Azure Active Directory ->Properties
  
• Record your Tenant ID (GUID) in the above table for later use

B.    Create Azure AD APP and fetch Client ID

• Login to Azure portal (https://portal.azure.com/) with a global admin account
• Navigate to Azure Active Directory ->App registration –> New registration
  

• Provide a user friendly APP name as “Resource Management MS Flow App” and use redirect URI as (https://localhost ) then click on “Register”
  
• After successful creation, the AP details are shown as below. Record the Client ID in table above
  

C.      Create APP Client Secret

• Go to your App Registration blade and select the APP just created
• Click on Certificates & Secrets ->New Client Secret from the APP Overview Blade
  
• Provide a Meaningful description and choose Expiry value “Never” and click on “Add”
  
• Once the Client Secret is added, record in the table above.
  

D.    Add APP Graph API Permission

• In the APP Overview blade, select “API Permission” -> Add a permission
  
• From the Request API permission blade, select “Microsoft Graph” group from the Microsoft API’s tab
  
• Select Application Permission by selecting the appropriate Tab and select from the groups below.
  
• Select the Application Permission Tab and browse permission from the list below:
  • Sites -> Sites.Read.All
  • User -> User.Read.All

  

• Your final permission list would look like below and will demand Admin Consent too
  

E.    Grant Admin consent for the permission added

Once you add the permissions, if you have logged in as Global Admin, Grant admin consent to these permission from the same window as shown below:

If a non admin user creates this APP, then a global admin must login to grant these permissions separately.

Once the admin consent given, the status of all permission changes to ‘Granted’.

Step – 2: Get SharePoint Site Identifier – Another prerequisite

This step can be performed by any domain user having access to the SharePoint Online Site.

• Open Graph Explorer site and make sure to login
• On Query box put https://graph.microsoft.com/v1.0/sites/{host-name}:/{server-relative-path}. Replace with your hostname and site relative url.
  
• When we run the query, we get the following result. Keep the “id” attribute value.
  

Step – 3: Design MS Flow

• Login to https://flow.microsoft.com with
• Click on My Flows>New>Automated-From Blank
  
• Give a name to your flow
• Select the trigger action as “When a file is created or modified (properties only)” from SharePoint connector.
  
• Select appropriate “Site Address” and “Library Name”. SP connection to be created with the account having at least read permission to the library.
  
• Add “HTTP” action from “HTTP” connector:
  

• Site Identifier received from Step -2
• Library Guid
• Excel file name with extension – output from trigger action
• Tenant ID received from Step – 1
• Client ID received from Step – 1
• Client Secret received from Step – 1

This would read the excel data as JSON data. We can parse the JSON Data and process however we would like to do.