Read Excel Data from SharePoint Using Graph API

Read Excel Data from SharePoint Using Graph API

Have you ever considered obtaining data from a shared Excel sheet? Are you interested in learning more? Let’s examine the procedures to accomplish the same.

Reading data from an Excel sheet is a difficult task that requires the Excel importer module. SharePoint allows organizations to maintain their data in a safe shared cloud environment, without using any additional Marketplace Modules.

Requirements

We’ll talk about the requirements we need to read data from SharePoint before we get started.

• The Mendix app should be registered in the Azure active directory with the necessary permissions.
• Office 365 group should be created with users who can modify the Excel sheet.
• An Excel sheet, from which data will be read, should be created on the SharePoint site.

Registering an App in the Azure Active Directory

Let’s go over the steps to register your app in Azure Active Directory:

1. To create an account in the active directory, we need a Microsoft 365 developer account. Join the Microsoft developer program through the link:

Developer Program | Microsoft 365 Dev Center

2. Login into the Azure active directory using the email from the admin and create your application.

3. To create an app, Click on the Azure active directory. Select “Enterprise applications” from the list and click, “New Application” to create a new app.

4. Click on “Create Your Own Application”, enter the name of your app, and select “Register an app to integrate with Azure AD”.

5. Select “Accounts in this organizational directory only” and click on Register. The app will be created successfully.

6. For your Mendix App to access SharePoint data, the required permissions should be defined in the active directory. To do this, navigate to “API Permissions” and click “Add new permission”.

7. From the list select “Microsoft Graph” (We need to use graph API to interact with SharePoint), then add the required permissions, and grant admin consent. To do so refer to the below table

Creating an Office 365 Group in Azure Active Directory

1. Click On Azure Active Directory and navigate to Groups.
2. Now, Click on New Group and fill in the details as shown in the below image

3. After the group is created, members can be added. All members should be added to this group if they wish to have access to the shared Excel spreadsheet.

4. Click the link to visit the SharePoint site on the Group overview page.

5. Open the SharePoint site, go to the document section, and create an Excel sheet as shown in the image below.

How to read data from the sheet, in steps

1. Create access tokens using the client’s App tenant identifier, client’s secret key, and client’s ID (Copy these details from Azure Active Directory)
2. Find the Office 365 group ID using the group name through a Graph API call.
3. Find the worksheet ID using the Excel sheet name through Graph API call.
4. With group ID and worksheet ID, data can be retrieved.

API Calls

To get an access token:

Method 
Get
Location
'https://login.microsoftonline.com/{AppTenantID}/oauth2/token'
Request body:
  'ClientSecretKey'="XX_YourSecretKey_XX",
  'ClientID'="XX_YourClientID_XX",
  'Resource'= "https://graph.microsoft.com",
  'Scope' = "Sites.ReadWrite.All"

To find the group ID

Method 
GET

Location 
https://graph.microsoft.com/v1.0/groups/?$filter=startswith(displayname,'GroupName')

Headers
Authorization: "Bearer XX_YourAccessTokenXX"
Accept :"Application/JSON" 

To find the worksheet ID

Method 
GET

Location 
https://graph.microsoft.com/v1.0/groups/GroupID/drive/root:/ExcelSheetName:/workbook/worksheets

Headers
Authorization: "Bearer XX_YourAccessTokenXX"
Accept :"Application/JSON"

Reading worksheet data

Method 
GET

Location 
https://graph.microsoft.com/v1.0/groups/GroupID/drive/root%3A/ExcelSheetName.xlsx%3A/workbook/worksheets(WorksheetID)/Range(address%3D'Sheet1!A1%3AZ500%E2%80%B2)

Headers
Authorization: "Bearer XX_YourAccessTokenXX"
Accept :"Application/JSON"

We will then receive data from the designated Excel sheet as a response. The response can be processed so that the data in our application is organized.

Examples of Data

Domain Model

Here, we have created an entity to store app credentials and employee data which we read from the Excel sheet. Other non-persistable entities are used to process the JSON response from the API call.

ACT_Employee_ReadData

This microflow reads data from the shared Excel sheet and saves it in the employee entity.

Reading shared Excel data starts with generating a token using your App credentials. Using the token, Group ID and worksheet ID will be retrieved using a graph API call. Finally, excel data will be retrieved using a group and worksheet ID.

Once we get the data through the API call, we will retrieve all rows and filter the data row excluding the header row from the list. To get updated data on every data pull, we will remove the old data from our app before generating new data.

Next, we will iterate all rows to get a cell list from each row. We will iterate the cell list to get data, create an employee object, and add it to the list for the final commit.

Finally, the Employee data list will then be committed, saving the data to the database.

Conclusion

I hope you enjoyed learning how to efficiently read Excel data from SharePoint using Graph API and streamlining your data retrieval processes today.

Please let your friends and co-workers know about this article if you enjoyed it!

Read more

https://azure.microsoft.com/en-us/get-started

Azure documentation

From the Publisher -

Inspired by this article to bring your ideas to life with Mendix? Sign up for a free account! You’ll get instant access to the Mendix Academy, where you can start building your skills.

For more articles like this one, visit our Medium page. And you can find a wealth of instructional videos on our community YouTube page.

Speaking of our community, join us in our Slack community channel. We’d love to hear your ideas and insights!