Our use of cookies

We use cookies to tailor your experience, gather analytics, and provide you with live assitance. By clicking "Accept" or continuing to browse our site you agree to the use of cookies. For more details please read our Cookie Policy.

Use Graph API in Power Automate to read large Excel files from SharePoint

Power Automate, a part of Microsoft’s suite of tools, is increasingly becoming a preferred choice for automating workflows and business processes across a variety of platforms and services. It provides a rich set of features and connectors to help businesses automate their operations with minimal coding. One such common operation is reading Excel files from SharePoint. However, when dealing with large data sets, some inherent challenges arise. In this blog post, we will dive into an efficient method to overcome these challenges using the Graph API.

Check out our video: Use Graph API in Power Automate to read large Excel files from SharePoint

The Challenge

For those familiar with Power Automate’s out-of-the-box (OOTB) actions, you might have stumbled upon its constraints. The most glaring limitation is its capability to read only up to 250 records from Excel files. This might suffice for smaller datasets, but in a business scenario where records can easily go beyond this number, it’s a significant bottleneck. Additionally, these OOTB actions often lack the desired efficiency in terms of performance.

Enter Graph API

The Graph API is Microsoft’s unified programming interface for accessing data across Microsoft 365 services, including SharePoint. By leveraging the Graph API, you can access the contents of Excel files stored in SharePoint document libraries seamlessly and without the aforementioned constraints.

Advantages of Using Graph API

  1. Scalability: With Graph API, the 250 records limitation becomes a non-issue. It’s designed to handle large data sets, making it an ideal solution for businesses with extensive Excel data.
  2. Performance: Graph API offers superior performance in comparison to the OOTB actions of Power Automate. Faster read times and reduced latency mean that workflows run more smoothly and efficiently.
  3. Flexibility: It provides a range of querying options, allowing you to fetch data precisely as per your needs. Whether it’s specific columns, rows, or data based on certain conditions, Graph API can handle it all.

Steps to Integrate Graph API with Power Automate

  1. Register your app with Azure AD: Before you can make calls to the Graph API, you need to register your application in the Azure AD of your Microsoft 365 subscription. This will provide you with an application ID, which will be used to authenticate your requests.
  2. Set permissions: Post registration, assign the necessary permissions for your application to read files from SharePoint.
  3. Create a Power Automate Flow: Begin by creating a new automated flow. Add a trigger based on your requirements. It could be a timed trigger or based on certain events, like when a new file is added to SharePoint.
  4. Add an HTTP action: This action will be used to make a call to the Graph API. Configure the HTTP action to GET the desired Excel data. Here you’ll use the previously obtained application ID for authentication.
  5. Parse the results: The result from the Graph API will typically be in a JSON format. Use Power Automate’s ‘Parse JSON’ action to structure the data. After this, you can use the data as needed in your workflow.
  6. Finalize your flow: Add any additional actions necessary for your business process. For instance, if you’re reading data to update a database or send a report, add these steps accordingly.

Conclusion

In the digital age, where data drives decisions, the necessity to access, process, and utilize large datasets efficiently is paramount. While Power Automate is a powerful tool in its own right, the incorporation of the Graph API enhances its capabilities multifold, especially when dealing with large Excel files in SharePoint.

To sum up, for businesses and individuals looking to harness the full potential of their Excel data in SharePoint, integrating Power Automate with Graph API is not just an option, but a requisite. It ensures scalability, performance, and precision – key components for success in any automated workflow.

#PowerAutomate #GraphAPI #SharePoint #ExcelIntegration #DataProcessing

Latest articles

AxioWorks Newsletter February 2024: Partnerships, Upcoming SQList Update, and Summit Invitation

27 Feb 2024

Hello Reader, This February has been pivotal for AxioWorks as we’ve focused on laying strong foundations for the year. Here are the key […]
[read article]

How AxioWorks SQList Aligns With the Priorities of Software Buying Decision Makers

26 Feb 2024

As outlined in the “2024 Global Software Buying Trends” report by Gartner Digital Markets, the criteria that predominantly influence […]
[read article]

Maximising Power BI Reporting from SharePoint: The Strategic Advantage of AxioWorks SQList over Native Connectors

15 Feb 2024

In today’s data-driven environment, making informed decisions rapidly can offer a significant competitive edge. This is where Power BI comes […]
[read article]

AxioWorks Newsletter January 2024: A Promising Start to the New Year!

29 Jan 2024

Hello Reader, We hope this newsletter finds you well and thriving. At AxioWorks, we are embracing the new year with great enthusiasm and are […]
[read article]

Exciting New Partnership and Innovative SharePoint Tools from AxioWorks and Lightning Tools

17 Jan 2024

AxioWorks is excited to announce a strategic partnership with Lightning Tools, a leader in creating innovative SharePoint components. This […]
[read article]

AxioWorks Newsletter November 2023: ESPC23, new partnership, and SQList update!

30 Nov 2023

Hello Reader, Welcome to this month’s edition of the AxioWorks newsletter! We’re thrilled to bring you updates on our recent […]
[read article]

Why Support Legacy SharePoint Versions Like SharePoint 2010?

16 Nov 2023

In the fast-paced world of technology, where new versions and updates are continuously rolled out, a recurring question often arises: “Why […]
[read article]

Why Companies are choosing SQList over Power Automate

30 Oct 2023

In the realm of synchronisation services, AxioWorks’ SQList and Microsoft’s Power Automate (earlier known as PowerApps) have emerged as […]
[read article]

AxioWorks Newsletter October 2023: An exhilarating month.

28 Oct 2023

Hello Reader, As we step into the season of autumn, we are thrilled to share with you the latest developments and opportunities unfolding at […]
[read article]

Why Choose SQList for SharePoint Data Extraction Over DIY Solutions

16 Oct 2023

In the ever-evolving world of technology and data management, the question often arises: “Why should I use a product like #SQList when I can […]
[read article]

Get SharePoint and SQL news to your inbox

Stay up-to-date with industry news and trends, SQL and SharePoint innovations and all the latest from AxioWorks by subscribing to our monthly newsletter.

Subscribe to the AxioWorks newsletter

*