August 06, 2019

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

161 Views
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
    Use Graph API to Read SharePoint Online Excel Data from Microsoft Flow
  • 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
    Use Graph API to Read SharePoint Online Excel Data from Microsoft Flow
  • Provide a user friendly APP name as “Resource Management MS Flow App” and use redirect URI as (https://localhost ) then click on “Register”
    Use Graph API to Read SharePoint Online Excel Data from Microsoft Flow
  • After successful creation, the AP details are shown as below. Record the Client ID in table above
    Use Graph API to Read SharePoint Online Excel Data from Microsoft Flow

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
    Use Graph API to Read SharePoint Online Excel Data from Microsoft Flow
  • Provide a Meaningful description and choose Expiry value “Never” and click on “Add
    Use Graph API to Read SharePoint Online Excel Data from Microsoft Flow
  • Once the Client Secret is added, record in the table above.
    Use Graph API to Read SharePoint Online Excel Data from Microsoft Flow

D.    Add APP Graph API Permission

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

    • Use Graph API to Read SharePoint Online Excel Data from Microsoft Flow
  • Your final permission list would look like below and will demand Admin Consent too
    Use Graph API to Read SharePoint Online Excel Data from Microsoft Flow

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:

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

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

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

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

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

Step – 2: Get SharePoint Site Identifier – Another prerequisite

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

Step – 3: Design MS Flow

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

Leave a Reply

Your email address will not be published. Required fields are marked *