Although Power Automate has actions to update Excel tables, they are less suited to bulk updates. We show how Microsoft Graph can be used instead.
Table of Contents
Many office users are familiar with Microsoft Excel and although there are many better ways of working with business data, there are times when simple spreadsheets of data need to be generated, perhaps to be emailed to external users who do not have access to the original data source. Power Automate is capable of populating a table within an Excel document, but care needs to be taken when working with larger datasets.
Why not use the Excel Online (Business) actions?
The Excel Online (Business) connector does come with a range of useful actions that can be used to manipulate data within Excel tables. However, actions such as ‘Update a Row’ and ‘Get a Row’, as their names suggest, will only process one row at a time and once you create a loop to process multiple rows, the overall time taken for the Flow to complete grows significantly.
What’s the better alternative?
Microsoft Graph. A range of APIs used for access the wide range of Microsoft 365 data, including:
- Microsoft 365 core services: Bookings, Calendar, Delve, Excel, Microsoft 365 compliance eDiscovery, Microsoft Search, OneDrive, OneNote, Outlook/Exchange, People (Outlook contacts), Planner, SharePoint, Teams, To Do, Workplace Analytics.
- Enterprise Mobility and Security services: Advanced Threat Analytics, Advanced Threat Protection, Azure Active Directory, Identity Manager, and Intune.
- Windows 10 services: activities, devices, notifications, Universal Print.
- Dynamics 365 Business Central.
What are the advantages and disadvantages of using Microsoft Graph?
The key advantage of using Microsoft Graph for this task is speed; flows using Graph complete in a fraction of the time as all of the rows are processed in bulk, meaning no loop is required. The disadvantage of using this method is that there are some additional setup tasks required before Microsoft Graph can be used. However, many of these are one-time tasks – future flows can benefit from the setup work already carried out.
