Workarounds needed to use the Excel Connector in Microsoft Flow

/

There are two Excel Connectors in Microsoft Flow. There’s a quirk to work with them.

Plan

  • Two Connectors

  • How do we use them? What’s the problem we need this workaround for?

  • Extra tips


Two Connectors

In Microsoft Flow, we now have two connectors for Excel. We have Excel Online (OneDrive) and Excel Online (Business).

There used to be a third Excel connector - which has been deprecated.

How do we use them?

The Excel Connectors call Microsoft Graph under the hood. We need to provide a folder to the file, the file itself, read tables from that file, and then get rows from a table in the Excel file.

To use these connectors, the Excel file must have the data that we want to read in a table.

To use the Excel Online connectors with dynamic file name, we need to find the MSGraph ID of that file. It looks something crazy like:

01BUSAX432CKPX6HWHFJBI7CHAXKHKDHSS

So we build a carefully crafted Send HTTP Request to SharePoint, using the v2.0 endpoint to pull back the MSGraph ID for the selected file.

This then allows us to work with the Excel Online connector.

To use List Rows Present in a Table - we need the Table ID, since this is dynamic now - I’m fetching this with Get Tables (need the file ID).

Then I use an expression to fetch the ID of the first table in the excel 

first(body('Get_tables')?['value'])?['id']

Extra Tips

By default, Excel Online connector retrieves 250 items. We can change the top query param to return 500 or 1000 items.

But to truly get back All the items, we need to switch on Flow’s pagination policy.

See the result - my meteorite.xslx has 49998 rows.

Extra Tips 2

Excel Connector can only retrieve rows via a ‘table’. So if you have to read rows from an Excel file that doesn’t have a table, we can call the Excel Connector action “Create Table” to insert one, then read the rows with that.

This is originally Flow lifehack 96.

https://twitter.com/johnnliu/status/1129715544712597504