Prologue
Working with Excel files using Power Automate is relatively straightforward, thanks to the standard Excel connector. It works well for small files, but things can get challenging with larger ones. Your flow may struggle with ‘Apply to Each’ loops to handle large datasets, and the biggest issue? It’s painfully slow.
The good news is that you can leverage the Microsoft Graph API to work with Excel files. It’s not only efficient but also significantly faster. Let’s see!
What is Microsoft Graph?
Microsoft Graph is a powerful API that connects multiple Microsoft services, allowing seamless access to data and insights across platforms like Microsoft 365, Windows. It provides a unified way to interact with resources such as emails, files, users, and analytics.
Microsoft Graph with Power Automate
In Power Automate, several connectors can interact with Microsoft Graph. These include both standard and premium connectors.
To work with Excel file, you can use ‘Send an HTTP request’ of Office 365 Users connector.
This tutorial supported both Excel file stored in SharePoint Document Library and OneDrive!
Read data
Let’s start with basic thing, read data. Here is overall of the flow.
Let’s break down to each action
Excel Online (Business): Get worksheets
This action was simple, just pick the location and the file you want to read data from.
Initialize Variable
We will create two variables to holds value of drive id and file id which is required for Microsoft Graph.
Luckily that we can obtain these from previous ‘Get worksheets’ action using these expression.
actions('Get_worksheets')?['inputs/parameters/drive']actions('Get_worksheets')?['inputs/parameters/file']
Office 365 Users: Send an HTTP request
This is the action where our flow will read data from Excel file.
Method: GET
URI: https://graph.microsoft.com/v1.0/me/drives/@{variables('varDriveId')}/items/@{variables('varFileId')}/workbook/worksheets/Page1/range(address='A1:I10')?$select=textReplace ‘Page1‘ with the name of your worksheet.
Replace ‘A1:I10‘ with the range within your Excel file.
Microsoft Graph will return Excel data in nested-array format.
Select
We need to transform the Microsoft Graph output to make it easier to use in later step. Easiest way is to use ‘Select’ action and then map the value into your desired name.
From: body('Send_an_HTTP_request')?['text']item()[0]Replace ‘0’ with column index of your Excel file (Started from 0, left to right)
Read data: result
Try run our flow, you will see well-formatted Excel data returned from Select action
Write data
Next, we will write data to Excel file.
The data you want to write must in array format, no matter where they from, you need to transform it to look like this, I will put it into Compose action
The ‘Write flow’ is similar with ‘Read flow’. You just need a little bit modification of ‘Send an HTTP request‘ action.
Method: PATCH
URI: https://graph.microsoft.com/v1.0/me/drives/@{variables('varDriveId')}/items/@{variables('varFileId')}/workbook/worksheets/Page1/range(address='A1:I3')Replace ‘Page1‘ with name of worksheet you want to write data to.
Replace ‘A1:I3‘ with range of where you want to write data. (The number of column, row should match with data)
Write data: result
Try run the flow.
Our data has been wrote to Excel!
Performance & Limitation
As per my test:
- Read: 50,000 rows, 9 columns
- Finished within ~3 seconds.
- Didn’t test with larger dataset.
- Write: 32,500 rows, 9 columns
- Finished within ~10 seconds
- I attempted to process 50,000 rows, but the flow encountered an error. It seems to have hit a service protection limit, though it’s unclear whether the issue stems from the Graph API or Power Automate.
- I’m not sure of the exact maximum data size that can be handled. I recommend splitting the data into smaller batches and processing them sequentially.
