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.

Advertisements

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

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

    Like

  2. 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?

    Like

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

        Like

  3. 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?

    Like

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

        Like

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

      Like

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

        Like

      2. 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.”

        Like

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

        Like

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

        Like

  4. 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?

    Like

Please leave a comment or feedback

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

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