Microsoft Flow – Working with Excel, Excel and Excel

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.

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

  • 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.

    • 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.

      • 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 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 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

Recent Posts

Get started with adaptive cards in Power Automate

Getting started with adaptive cards can be difficult. In this post i have written some…

6 days ago

Create PDF documents from data in Power Automate

In this post I will look at how to create PDF documents from data. Use…

7 days ago

Unnest nested arrays in Power Automate

We all know this problem, you have a nested array in Power Automate but how…

2 weeks ago

Advanced settings not loading in Power Platform

Yesterday one of my clients showed me an issues where the Advanced settings didn't load.…

3 weeks ago

1 Better way to use Create Item in SharePoint with Power Automate

Have you been using the REST API instead of the create item action in Power…

3 weeks ago

Filter by Content Type using Get Items in Power Automate.

In a comment on my Filter Query on Get Items in the SharePoint connector post…

3 weeks ago
%%footer%%