Microsoft Flow – Custom Connectors – How to read an Excel file from SharePoint?

A while back I wrote a post about reading Excel files stored in SharePoint using Microsoft Graph. This has been a post that has been helpful to many people so why hasn’t this been turned into a custom connector?

Do you remember the reasons for a custom connector?

  1. Flow doesn’t support something yet.
  2. Flow supports something but ti can be done better!

Well, I added the reading of excel files to my MS Graph Connector on GitHub!

Simply download the latest version of the MS Graph Connector. Next to the Get My Profile action that I added last week there are now 3 action available to replicate the steps in my earlier MS Graph post.

Then upload the swagger file to Microsoft Flow.

Create the following actions:

  • Get Site ID
  • Get Lists in Site
  • Read Excel file from SharePoint Library

Get the site details

We start by getting the Site ID. Simply supply your tenant name and the site that contains the Excel file that needs to be read. My site is pieterveenstramvp/sites/FinanceDemo

Then I’m getting all the lists using the site id. The site is is set to @{body(‘Get_Site_ID’)[‘id’]}

 

Get the lists and libraries

Then I’m using the compose action to make it easier to get to my list data. the value is set to

@{body(‘Get_Lists_in_Site’)[‘value’]}

Read the Excel file

Then finally, I’m using the output from the compose and I’m selecting just the invoice library. Yes, this could be done a bit more efficiently, but that is somthing for the future.

Now finally I’m reading the excel file by supplying the filename, name of the sheet and the range for my data.

  • No locks on the excel spreadsheet while I develop the solution, like the standard connector does.
  • No need to use data tables in Excel.
  • On top of that it is extremely fast, compared to the standard connector.

I will soon create an updated version to improve the filtering options. , unless of course you beat me to it. Feel free to suggest the code changes on the GitHub site.

 

Share
Pieter Veenstra

Business Applications and Office Apps & Services Microsoft MVP working as a Microsoft Productivity Principal Consultant at HybrIT Services. You can contact me using contact@veenstra.me.uk.

View Comments

Recent Posts

8 top reasons to use Dataverse or SharePoint lists in the Power Platform

Why would you use Dataverse, if SharePoint lists gives you the option to avoid premium…

6 days ago

Filter by calculated fields in SharePoint using Power Automate

When you read items from a SharePoint list and you want to filter by calculated…

1 week ago

Expected String but got Null, when calling a flow from a Model Driven app

A few weeks ago I looked at calling flows from model driven apps. And for…

1 week ago

Get all your broken connections using Power Automate

To get all your broken connections in the Power Platform can be a challenge. In…

2 weeks ago

Customer Voice a.k.a. Forms Pro the database behind the magic

If you call Customer Voice still Forms Pro or not, have you ever looked into…

2 weeks ago

Many running flows in Power Automate

Recently I noticed that some of my flows end up with many running flows. Types…

3 weeks ago
%%footer%%