When working with Excel in Microsoft Flow you will find that there are 3 connectors available.
- Excel
- Excel Online (Business)
- Excel Online (OneDrive)
When you compare the 3 connectors you will quite quickly find out the differences.
What is the difference between these connectors?
Obviously the Excel Online (OneDrive) connector will give you access to Excel files stored in OneDrive.
The Excel Online (Business) is a bit less clear. This connector offers a similar set of actions that give you access to Excel files stored in SharePoint.
Then there is the last one left Excel.What is this about then? Apparently this is the old version of the Excel connector. When I tried it I couldn’t select the file name by clicking on the little folder icon.
Are there any triggers for Excel?
Yes there is a trigger but there is only one, but the weird thing is that when you search for excel and then click on a connector the trigger disappears. So it looks a bit like the trigger isn’t part of a connector. Which confirms my first screenshot in this post where the Excel connectors didn’t show any triggers.
And not until the tool tip appears is the trigger becoming a bit clearer.
So it is possible to specify a number of things here.
- me
- SharePoint Site URL
- users/someone’s UPN
- groups/group Id
- sites/SharePoint Site URL: /teams/team name:” (the colon are required)
Aaaaahhhhh, which techie put this tool tip together. Do you understand this?
I really wish that my uservoice was implemented here. Of course the SharePoint URL option I can figure out. but “me”??? what does that mean?
Then now it’s time to look at the real stuff that you can do with Excel. For my examples I’m going to place my Excel file in SharePoint. then I”ll have a look at how to build a table in my excel file.
In most of the actions you will find a source (In my example this is a SharePoint site). Ok, I can still understand this.
But then the option Drive? Again what does this mean. Luckily Flow offers my suggestions and Drive is the document library.
Ok, The Excel connectors are still preview. So hopefully these issues will all improve soon.
After running the create table I got the following result in my spreadsheet
The second attempt ( after I emptied my spreadsheet) I had more luck.
Ok, this is great! With all the other option available like adding row, get a row I’ve got yet another option to store my data in. Personally I don’t like storing data in spreadsheet. My main reason for this is that people build up their spreadsheets manually and spreadsheets easily become unstructured. However, using the data table structure in Excel is a great way to store data from Flow or PowerApps.
I’m sure you’ve probably already realized it, but those options for the trigger source appear to be Graph queries, at least these do:
me
users/someone’s UPN
groups/group Id
Hello.
Please help me with issue.
How to get List rows presents in table by using ODATA filter if name of header is “name of employee” (with space char) ?
I tried:
1. startswith(‘name of employee’, ‘Bob’)
answer: !sAj1K!’)’ or ‘,’ expected at position …
2. startswith(name of employee, ‘Bob’)
answer: !sAj1K!’)’ or ‘,’ expected at position …
3. startswith(“name of employee”, ‘Bob’)
answer: “!LKrqa!Syntax error: character ‘\”‘ is not valid at position …
4. startswith(name_x0020_of_x0020_employee”, ‘Bob’)
answer: “Invalid filter clause: cannot find the ‘name_x0020_of_x0020_employee’ column.
But this column is set in table.
I’m quite sure that filters in fieldnames do not handle spaces. So if you can try to remove the spaces from the column names in your Excel files
Could you give an example of the “sites/SharePoint Site URL: /teams/team name:”
I’ve had a go trying to copy this format and I can’t make it work for an Excel Get Rows. Am I typing the “team name:” and then the team name after?
The easiest way is to select the little folder icon and then navigate to the excel file that you want to use.
The file picker will only let you select items in OneDrive and not in SharePoint. That is why I’m interested in the correct syntax to get Sharepoint documents.
Hi Nicholas,
Which Excel connector are you using? There are 3. You will need to use the one marked Business if you want to use Excel files in SharePoint.
Hi, has anyone recently had trouble with the “List rows present in a table” step? I have a Flow that retrieved the rows in a table in a workbook stored on SharePoint, then deleted the rows, retrieved details from a Planner board and then wrote those details into that same table. I now get an error “Could not retrieve values. Graph API is unable to open the workbook. Other people in my organisation get the same error. Has something broken in the Excel Online (Business) step?
Are you reading the rows, then deleting the rows and then reading the rows again all in a single flow? Or are you using multiple flows?
Hi Pieter, in this particular case, i’m reading the rows, deleting them and then adding new rows. I’ve found that the same step (from the Excel Online (Business) connector) won’t even work if i start with a completely new workflow and try to retrieve rows from a test workbook with dummy data. The step is able to get the Location, Document Library and File but NOT the Table.
have you considered using MS Graph instead?
Yes! I’ve been having the same issue with several flows for the past 48 hours, all involve at least one step where the Excel (Business) connector is involved.
I am having the Graph API error, now on a flow that used to work. We are using the Excel Online (Business) connector also. I opened a ticket with Microsoft but all they told me was that the issue must be on my tenant that it works for them. If you find a solution I would love to know what it is.
can you post the error messages that you get from Graph?
400 error bad request on a create table Excel (business) connector, and in a different flow
Get a row Excel (business)
Could not retrieve Values
Same following error on both.
Graph API is unable to open the workbook client requested: ………
Hi, just heard back from my tech team who spoke with microsoft…
“Thanks for contacting MIcrosoft Technical support and I apologize for not responding sooner.
The notes state that you are not able to use the Excel Connector in Flow and that the error is coming from Graph API.
This is a known issue that the Product Team is working on. We have had other customer report the same error recently.
I will add you case to the bug.”
Hi Lee, I would probably switch toa direct Graph API call from Flow.that way you can see what is going wrong/right. Please do keep me updated when you get any further updates from Microsoft.
Hi Pieter, I have not used MS Graph before. I’ll have a chat with the guys in IT.
This post should help you
https://veenstra.me.uk/2018/10/17/microsoft-flow-read-large-excel-files-within-seconds-without-creating-tables-using-microsoft-graph/
Hi Pieter, thanks for the link. I abandoned Excel entirely (after recreating the flow using the Excel (OneDrive) connector and finding that, while it worked at first, it started to time out).
I eventually created a flow that took the data from Planner and put it into a SharePoint list. I find that this is actually a much better solution for me. It works faster, doesn’t have issues with file locks and also means that i can make use of the list data views for my stakeholders.
Hi im trying to use the excel get rows functionality but I am not able to see the option at all. I only have Excel (for business) and Excel (OneDrive) and neither of them have “get rows”. Has it been removed?
Hi Tony,
It looks like they have been removed. When ready data from Excel I would use the Graph API anyway.
Microsoft Flow – Read large Excel files within seconds without creating tables using Microsoft Graph
Hi i am read an excel and add a row in another excel but after 256 row it is not can you please help me on this how to add further
I’ve tried 600 updates without any problem: