Use Graph Api in Power Automate to read (very)large Excel files from SharePoint

---

Current scenario

Quantity of items:

The sample file has 50K items:

Running time:

The scope of obtaining data from an Excel File hosted in a SharePoint Library takes around an hour to execute (in my study environment took eight hours 😭):

In the Production environment this quantity usually takes about an hour to run.

Occasionally, the “do until” action fails in the last iteration…. 😖

Solution: Use Graph API!

Microsoft Graph API is a single API that connects to data and insights from Microsoft cloud services, such as Microsoft 365, Windows, and Enterprise Mobility + Security. It’s powerful!

To use the HTTP with Azure AD connector or the Invoke HTTP request (easier way) action in Power Automate, you need to have a Power Automate per user or per flow license. These licenses are not included in the Microsoft 365 license, and they have different pricing and features. You can find more information about the Power Automate licensing options in this link.

Drive API

You can use Microsoft Graph to read and modify Excel workbooks stored on OneDrive for Business, your SharePoint site, or your Group Drive. The Workbook resource (or Excel file) contains all other Excel resources through relationships. You can access a workbook through the Drive API by identifying the file’s location in the URL.

For example:

https://graph.microsoft.com/v1.0/me/drive/items/{id}/workbook/
https://graph.microsoft.com/v1.0/me/drive/root:/{item-path}:/workbook/>

You can access a set of Excel objects (such as Table, Range in Table, or Chart) using the standard REST APIs to perform create, read, update, and delete (CRUD) operations in the workbook.

For example, the request will be:

GET- https://graph.microsoft.com/v1.0/me/drive/items/{id}/workbook/worksheets

Will return a collection of worksheet objects that are part of the workbook.

The Excel REST API only supports workbooks formatted in an Office Open XML file. Workbooks with .xls extension are not supported.

Application

By applying this request, pagination is not necessary (the documentation does not mention restrictions), one of the reasons that the acquisition time is surprisingly shorter:

Requisitions

The following request will be used to obtain Excel rows:

https://graph.microsoft.com>;v1.0/sites/{Site_ID}/drive/root:/{Item-path}:/workbook/worksheets/{Id|name_Worksheet}/tables/{Id/name_Table}/rows

Let’s see each parameter/placeholder:

Parameters/Placeholders:

Site_ID: obtained with a request to the Graph “sites” endpoint:

In the example:

https://graph.microsoft.com/v1.0/sites/xl86x.sharepoint.com:/sites/CursoSharepointBsico

Example of the value obtained to be used in the main request {Site_ID}:

"id": “xl86x.sharepoint.com,fab2ddf4-0490-4ce7-9a01-5494b0ebc15e,3c519f39-1169-4d2d-b470-26bfdcfdc2d9”

This may be a point for improvement, the documentation does not specify what the value “3c519f39–1169–4d2d-b470–26bfdcfdc2d9 is

Item-path: Is obtained with an action “get item from Trigger” or directly from the trigger output (triggeroutputs):

Id|name_Worksheet: Worksheet from the file

This can be obtained as a native Excel action (as is already standard in Load Flows). For the example I just “fixed” the value

Id/name_Table: Also, can be achieved with a native Excel action, but again, for the example, I entered the value manually. It can be obtained in the Excel file as shown below (remembering that the data must be formatted as a table):

Finally, the complete request:

URI from the example:

https://graph.microsoft.com/v1.0/sites/xl86x.sharepoint.com,fab2ddf4-0490-4ce7-9a01-5494b0ebc15e,3c519f39-1169-4d2d-b470-26bfdcfdc2d9/lists/9e4e3a0e-1a1b-44b6-a11a-85e542a7d2cc/drive/root:/SampleXLSFile_table.xlsx:/workbook/worksheets/Sample/tables/table/rows

You can manipulate the request using “OData query parameters”, such as $top, $skip etc:

https://graph.microsoft.com/v1.0/me/drive/items/{id}/workbook/tables/{id|name}/rows?$top=5&$skip=5

Request output

This is an example of a request return (in case of OK response: 200):

And this is from the Sample File:

Graph Explorer

Just a tip: a practical tool for quick testing and learning is Graph Explorer, in which you have direct links to the endpoint documentation:

Improvements for the Flow

The objective of this article is just to demonstrate the effectiveness and efficiency gains using Graph, for a dynamic and error proof Flow I suggest use “Dynamic Contents”, such as:

• Auto Format file and Get Sheet name to Table before Graph Api request using native Excel actions (for those action, native action will perform well)

References

Get access without a user — Microsoft Graph

Working with Excel in Microsoft Graph — Microsoft Graph v1.0

Graph Explorer | Try Microsoft Graph APIs — Microsoft Graph

https://learn.microsoft.com/en-us/office/dev/scripts/testing/power-automate-troubleshooting