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)

3 Excel Connectors in Microsoft Flow

 

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.

3 Get row actions in the Excel connectors in Microsoft Flow.

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.

Triggers available in Excel connectors

And not until the tool tip appears is the trigger becoming a bit clearer.

For a selected row action

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.

Excel Online (Business) actions

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.

Get Worksheets action

Ok, The Excel connectors are still preview. So hopefully these issues will all improve soon.

Create Table action

After running the create table I got the following result in my spreadsheet

Excel file showing a table

The second attempt ( after I emptied my spreadsheet) I had more luck.

Excel table in Excel online

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.

24 thoughts on “Microsoft Flow – Working with Excel, Excel and Excel

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

  2. commented on August 24, 2018 by Николай Эрлих

    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

  3. commented on December 17, 2018 by Nicholas Piotrowski

    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.

      • commented on December 18, 2018 by Nicholas Piotrowski

        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.

  4. commented on February 20, 2019 by Mr L S Hodge

    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?

      • commented on February 27, 2019 by LS Hodge

        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?

    • commented on February 21, 2019 by ehrmagerd

      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.

    • commented on February 23, 2019 by Terik

      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?

      • commented on February 23, 2019 by Terik

        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: ………

      • commented on March 20, 2019 by LS Hodge

        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.

  5. commented on March 1, 2019 by LS Hodge

    Hi Pieter, I have not used MS Graph before. I’ll have a chat with the guys in IT.

  6. commented on May 7, 2019 by Tony

    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?

  7. commented on May 12, 2020 by Saani

    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

Leave a Reply to LS Hodge Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: