1667 Views
6 minute read
Categories
Content and Collaboration SharePoint Custom Development

How to Read and Import Large Microsoft Excel Files from SharePoint Online Document Library

Introduction :

In this blog, I will elaborate on how to read large Microsoft excel files from SharePoint online document library and import it into a list using Azure App with Microsoft Graph API delegated permissions and Power Automate flow. There are options such as Excel service which help you import MS Excel data into a SharePoint list. But in scenarios where the number of records is high (e.g., >25,000 rows), the excel file must be processed periodically, after the new file is uploaded or modified in the SharePoint library. One of the ideal ways to process the file is with Microsoft Graph API with Azure App.

Follow the steps below:

Step 1:

Validate the sample source Excel (.xlsx) file in the SharePoint library

How to read and import large Microsoft Excel files from SharePoint online document library

Step 2:

Configure an Azure app with the following Microsoft Graph API delegated permissions. To connect with Microsoft Graph, you will need a work/school account or a Microsoft account.

  • Go to the Azure App registrations page.
  • When prompted, sign in with your account credentials.
  • From the top menu, select the New Registration button.
  • Enter the name for your app
  • Confirm changes by selecting the Register button.
  • Now click on “API Permissions” and add the following “Microsoft Graph” Delegated permissions- (Site.Read.All, Site.ReadWrite.All,User.Read)
How to read and import large Microsoft Excel files from SharePoint online document library
  • Now click on “Certificates & secrets” from the left panel and generate a “New client secret “
  • Copy and save the secret value for future reference
  • Also copy the app Client ID and Tenant ID from the APP Overview Page
How to read and import large Microsoft Excel files from SharePoint online document library

Step-3:

Create a Power Automate Flow. This is a glimpse of how our Power Automate Flow will look like,

How to read and import large Microsoft Excel files from SharePoint online document library
Follow the steps below to create the Power Automate flow:

a. Create a new “Automated cloud flow”

How to read and import large Microsoft Excel files from SharePoint online document library

b. Enter the Flow Name and chose “When a file is created (property only)” trigger

How to read and import large Microsoft Excel files from SharePoint online document library

c. Add the following variables to store Site URL, Excel File Name, List/Library name, Azure App Client ID, and Client Secret etc.

How to read and import large Microsoft Excel files from SharePoint online document library

Step 4:

Add an HTTP action to get the access token. This access token is used to get the SharePoint site details and read excel file.

How to read and import large Microsoft Excel files from SharePoint online document library

Note: As I am using the Microsoft Graph API delegated permissions, in the above action I am using “grant_type=password” and used Username and Password along with the “client_id” and “client_secret”

Step-5:

Add a Parse JSON action to parse the previous HTTP call response to get the “access_token”

How to read and import large Microsoft Excel files from SharePoint online document library
Sample JSON
{
    "type": "object",
    "properties": {
        "token_type": {
            "type": "string"
        },
        "scope": {
            "type": "string"
        },
        "expires_in": {
            "type": "integer"
        },
        "ext_expires_in": {
            "type": "integer"
        },
        "access_token": {
            "type": "string"
        },
        "refresh_token": {
            "type": "string"
        }
    }
}

Step-6:

Collect the SharePoint Site ID from MS Graph by configuring an HTTP action as shown below. Below is the URI format

URI: https://graph.microsoft.com/v1.0/sites/.sharepoint.com/sites/

How to read and import large Microsoft Excel files from SharePoint online document library

In the above HTTP call, we pass the “access_token” as “Authorization,” that we get from Step-4 parse JSON output

Step-7:

Add a Parse JSON action to parse the above HTTP call response to get the SharePoint site ID

How to read and import large Microsoft Excel files from SharePoint online document library
Sample JSON
{
    "type": "object",
    "properties": {
        "@@odata.context": {
            "type": "string"
        },
        "createdDateTime": {
            "type": "string"
        },
        "description": {
            "type": "string"
        },
        "id": {
            "type": "string"
        },
        "lastModifiedDateTime": {
            "type": "string"
        },
        "name": {
            "type": "string"
        },
        "webUrl": {
            "type": "string"
        },
        "displayName": {
            "type": "string"
        },
        "root": {
            "type": "object",
            "properties": {}
        },
        "siteCollection": {
            "type": "object",
            "properties": {
                "hostname": {
                    "type": "string"
                }
            }
        }
    }
}

Step-8:

Add a HTTP action to read the Excel file

How to read and import large Microsoft Excel files from SharePoint online document library

Note: In the above HTTP call if you want to read selected range of cells from excel file you can specify the range like “range(address=’A1:D4500′)” instead of “usedrange” in the URI

Step-9:

Add a Parse JSON action to parse the above HTTP call response to get the excel data

How to read and import large Microsoft Excel files from SharePoint online document library
Sample JSON
{
    "type": "object",
    "properties": {
        "@@odata.context": {
            "type": [
                "string",
                "number"
            ]
        },
        "@@odata.type": {
            "type": [
                "string",
                "number"
            ]
        },
        "@@odata.id": {
            "type": [
                "string",
                "number"
            ]
        },
        "address": {
            "type": [
                "string",
                "number"
            ]
        },
        "addressLocal": {
            "type": [
                "string",
                "number"
            ]
        },
        "columnCount": {
            "type": "integer"
        },
        "cellCount": {
            "type": "integer"
        },
        "columnHidden": {
            "type": "boolean"
        },
        "rowHidden": {
            "type": "boolean"
        },
        "numberFormat": {
            "type": "array",
            "items": {
                "type": "array",
                "items": {
                    "type": [
                        "string",
                        "number"
                    ]
                }
            }
        },
        "columnIndex": {
            "type": "integer"
        },
        "text": {
            "type": "array",
            "items": {
                "type": "array",
                "items": {
                    "type": [
                        "string",
                        "number"
                    ]
                }
            }
        },
        "formulas": {
            "type": "array",
            "items": {
                "type": "array",
                "items": {
                    "type": [
                        "string",
                        "number"
                    ]
                }
            }
        },
        "formulasLocal": {
            "type": "array",
            "items": {
                "type": "array",
                "items": {
                    "type": [
                        "string",
                        "number"
                    ]
                }
            }
        },
        "formulasR1C1": {
            "type": "array",
            "items": {
                "type": "array",
                "items": {
                    "type": [
                        "string",
                        "number"
                    ]
                }
            }
        },
        "hidden": {
            "type": "boolean"
        },
        "rowCount": {
            "type": "integer"
        },
        "rowIndex": {
            "type": "integer"
        },
        "valueTypes": {
            "type": "array",
            "items": {
                "type": "array",
                "items": {
                    "type": [
                        "string",
                        "number"
                    ]
                }
            }
        },
        "values": {
            "type": "array",
            "items": {
                "type": "array",
                "items": {
                    "type": [
                        "string",
                        "number"
                    ]
                }
            }
        }
    }
}

Step 10:

Add an “Apply to each” action to loop through all the rows from excel and add into the SharePoint list using “Send an HTTP request to SharePoint” action.

Add the expressions to specify the column index of the excel file of each item in the “Send an HTTP request to SharePoint” action.

How to read and import large Microsoft Excel files from SharePoint online document library

Now our flow is ready to go. Run the flow and validate data into the SharePoint list.

Outcome:

  • Process enormous number of records quickly.
  • No need for Excel Tables and Excel connector.
  • My flow is a generic process that is easy to copy between environments as we are using variables to store site and excel file details
  • You can configure the flow to read file periodically

Conclusion:

I hope this blog has helped you learn how using the Microsoft Graph API we can easily reach into an excel file, stored in SharePoint, and process the data for use in other places

LinkedinTwitterFacebook

Leave a Reply

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




Enter Captcha Here :