Replace the Excel Online connector with the Microsoft Graph API in Power Automate

If you have worked with the Excel Online connector in Power Automate then you will most likely know the pains of this connector. In this post I’m giving an easy way to replace the connector with the much more reliable Microsoft Graph API alternative.

The pains of the Excel connector

Table of Contents

• The pains of the Excel connector
• The Graph API equivalent
• The downloadable solution
• Initialize Connection to a file
• Excel – List Tables
• Performance
• Some thoughts
  • Like this:
  • Discover more from SharePains

The Excel connector most often isn’t too bad until you put it under a bit of pressure.

Some of the issues are:

• Throttling error, also known as 429 errors
• Action’s retry isn’t great
• Very unclear error messages

Some of the common error message are:

Action ‘List_rows_present_in_a_table’ failed: Graph API request failed. Error code is ‘InvalidSession’. Error message is ‘The target session is invalid.’.

Or

Action ‘List_rows_present_in_a_table’ failed: Graph API is unable to open the workbook.

Or

Graph API request failed. Error code is ‘itemNotFound’. Error message is ‘Item not found’

From the above messages we can see that the Graph API is being used by the Excel connector however the Excel connector gives us a lot more errors than direct Graph API calls. The biggest problem with the Excel connector in my experience is that it fails randomly.

Now, random doesn’t really exist in software development. Random behaviour merely means that we haven’t figured out the pattern to a problem yet.

The Graph API equivalent

The Microsoft Graph API offers us end points that will give us similar functionality compared to the Excel Online connector, however the performance is a lot better, especially when we are pushing the connector to its throttling limits.

For detailed limitations please have a look at the Excel Online connector documentation

The downloadable solution

At the end of this post there will be a link to a downloadable solution. This solution is not meant for production but merely to get you started. This solution includes a copy of the examples in this post. If you need additional actions to be covered then please let me know.

Initialize Connection to a file

The first flow in the solution is the Initialize Connection flow

The flow takes three parameters.

• Site URL
• Library (This is the name of the Library e.g. Documents)
• Workbook (The file name e.g. 1234.xlsx)

The flow returns three pieces of information that we will need for any future actions.

• Site ID
• Library ID
• Item ID

The other flows in the solution are:

• Excel List Tables
• Excel Get Workbook Application
• Excel Get Table Range
• Excel Get Table
• Excel Get Data in Table
• Excel Get Table Row
• Calculate Workbook Application
• Excel Create Table Row

Excel – List Tables

The Excel List Tables action will ask you to supply the Site ID, Library ID and the Item ID as we retrieved when we initially connected to the Excel file in SharePoint. And then we simply connect to the Graph API end point.

Performance

As mentioned earlier performance due to 429 errors. In the screenshot below, you can se two flows runs where on the left I’ve used child flows and the Invoke and HTTP request action. On the right I used the Excel Online connector. I only asked the flow to read the same Excel file 300 times, which is just enough to make the Excel connector struggle a little bit.

Some thoughts

Now should you replace every Excel action with a Microsoft Graph API call? Not really, I would only replace the Excel connector if it causes any inconsistent behaviours in your flows, which is most likely when you have high volumes of Excel files to process.

Also this is not a reason to start using Excel as your database. However there are many business that use Excel as a temporary solution that may be a little less temporary as expected.

If you would like to use flows described in this post then please download the solution below.