Read Excel Data from SharePoint Using Graph API

Reading data from an excel sheet is a difficult task that requires the use of the excel importer module. Some organisations maintain their data in a safe shared cloud environment, like SharePoint. Ever consider obtaining data from a shared Excel sheet? Are you interested in learning more? Let’s examine the procedures to accomplish the same.

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

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

Registering an App in the Azure Active Directory

1. To create an account in the active directory, we need a Microsoft 365 developer account. So, join the Microsoft developer program through the link: https://developer.microsoft.com/en-us/microsoft-365/dev-program

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

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

4. Click on create your own application and 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. App will be created successfully.

6. Required permissions to access SharePoint data should be defined in the active directory, so that app can access SharePoint data.

7. To give permissions, open your application and navigate to API Permissions

8. Click on “Add new permission” to add “new permissions”.

9. From the list select Microsoft Graph (Going to use graph API to interact with SharePoint) and then add the required permissions & Grant admin consent as well. To do so refer to the below table

Let see on how to Create 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 there as shown in the image below.

How to read data from the sheet, in steps

1. Creation of 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:  

https://login.microsoftonline.com/{AppTenantID}/oauth2/token

The request will be triggered with the following data as a request body: 

1. Client secret key 
2. Client ID 
3. Resource – https://graph.microsoft.com,  
4. Scope – Sites.ReadWrite.All 

To find group ID 

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

The request will be triggered with the following details as a header: 

1. Token as an Authorization header 
2. Accept – application/json 

To find worksheet ID

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

The following information will be included as a header in the request when it is made:

1. Token as an Authorization header 
2. Accept – application/json. 

Reading worksheet data

https://graph.microsoft.com/v1.0/groups/GroupID/drive/root:/ExcelSheetName.xlsx:/workbook/worksheets(WorksheetID)/Range(address=’Sheet1!A1:Z500′)

The following information will be included as a header in the request when it is made:

1. Token as a header for authorization

2. Accept the application/json format.

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

Examples of Data

Domain Model

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

ACT_Employee_ReadData

This microflow is used to read data from shared excel sheet and save it in employee entity.

Process to read shared excel data will starts with generating token using app credentials. Using token, Group ID and worksheet ID will be retrieved using graph API call. Finally, excel data will be retrieved using a group and worksheet id.

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

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

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

Conclusion

I believe I have successfully outlined how to read data from a shared Excel sheet. Please let your friends and co-workers know about this article if you enjoyed it.