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?
- Flow doesn’t support something yet.
- 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
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.
Discover more from SharePains
Subscribe to get the latest posts sent to your email.
Pieter Veenstra
Is your business still running on paper trails, sprawling Excel files, or ageing Access databases? There's a better way — and I can show you exactly what it looks like. I'm the Technical Director of Vantage 365, a Microsoft solutions consultancy working with clients across the UK, the Netherlands, and worldwide. For over 30 years I've been turning messy, manual business processes into clean, automated systems that save time, reduce errors, and give teams the visibility they need to make better decisions. SharePains is not just any blog run by a Microsoft MVP. Have you ever used Try-Catch in Power Automate? The original post about Try-Catch in Power Automate can still be found on this site, https://sharepains.com/2018/02/07/try-catch-finally-in-power-automate-flow/ Or have you ever used the Pieter’s method to avoid variables and speed up your flows? https://sharepains.com/2020/03/11/pieters-method-for-advanced-in-flows/ You can contact me using contact@sharepains.com
Read Excel from SharePoint library is not giving me the “ID” options that you used here. I only see “Body”. Did I missed something ? Please help.
Did you try adding the id with the expression editor using the following expression
body(‘Get_Site_ID’)?[‘id’]
Hi, I ran into the issue of the default flow action to be too slow. I tried recreating the steps, but when testing the custom connector, I receive the message that the app is not registered as a multi-tenant application.
When I go into the app registration and change it to “Accounts in any organizational directory”, I can get to the login screen, but it then asks me to login with an admin account.
Logging in with the admin account gets me a “account unknown”-message. Thanks
Hi Wouter, can you email me screenshots on contact@veenstra.me.uk
Hi every i have an error in my api when add this connector or Swagger code in my api definition i have error when i click button try this operation the error is that ” Object is missing the required property ‘accept'”.
Which one of the actions is giving you this error?
Can you share with me the code of function which helps to crate this api defination
https://github.com/Pieter-Veenstra/MSGraphConnector
Hi,
We are getting 404 error on all actions and when we download error details on Get Profile ID it shows
{“host”:{“api”:{“runtimeUrl”:”https://flow-apim-europe-001-northeurope-01.azure-apim.net/apim/read.20excel.20file.5ff6fe3c3c36af320c.5ff35285b5326983de”},”connection”:{“name”:”/providers/Microsoft.PowerApps/apis/shared_read.20excel.20file.5ff6fe3c3c36af320c.5ff35285b5326983de/connections/35c280861e11458cb97e08369e54daf5″}},”method”:”get”,”path”:”/v1.0/me”,”headers”:{“Content-Type”:”application/json”},”authentication”:{“value”:”*sanitized*”,”type”:”Raw”}}
Your quick response in this regard will highly appreciated.
Regards.
Did you set the client id and client secret correctly to the details that you configures in your Azure app?
Hi Pieter,
What if I don’t know the range of my data like A1:J10? How do I figure out the exact number of rows?
Then you might want to use tables instead and use this MS Graph call:
https://docs.microsoft.com/en-us/graph/api/resources/excel?view=graph-rest-1.0#get-list-of-table-columns
Can I use this solution to get the value from one specific cell in the excel sheet?
Of course you can. Simply set the range used to the cell that you want.
if there are connections within the Excel file to other files can flow be used to refresh that connection
https://social.msdn.microsoft.com/Forums/vstudio/en-US/b288098d-d4e8-4845-ae3f-38ad235e22aa/how-to-execute-a-quotrefresh-allquot-for-excel-programmatically-in-c
The code mentioned above could do that. Maybe this is sonething thatvcan be called fron an azure function that is called from a flow.
While using update a row action for excel online in Ms flow there is a value items which takes dynamic values.
In what format does it accepts values if I have to update selectes columns of a row?
No that doesn’t exist
I cannot create the custom connector using your swagger.json,
only for Get My Profile
Do you have a tutorial how to “upload the Swagger?
https://imgur.com/U6vr0wD
https://imgur.com/izudXKH
Hello Peiter
thanks for posting this article .
just assume that there will be excel file received every day and user will upload into sharepoint library then flow can be triggered to process that data into SP list . but these files wont have fixed number of rows of data(but columns are static) , in this case how can I set the excel range dynamically ?so this solution wont work right ?
Yes, that can still work just format the excel as a table and then read the table instead of a range.