Although it might sounds straightforward enough, if you come across this requirement you can get stuck once you realize that the Excel Online Connector provided by Microsoft Flow has a specific limitation (at least by the time I’m writing this article) that doesn’t allow it to read a file that isn’t already created in a specific folder, but luckily there is a way around that.
In order to iterate over excel rows you will need to use the Microsoft Excel Connector provided by Flow more specifically the List rows present in table Action.
The action works like a charm once you fill in all the required fields including the File, in which you need to define using the provided file picker, and there lies our problem.
I want to list the rows present in a table but when a file is created in a folder, how can I pick the file if it wasn’t created just yet? The answer is obvious right? We just need to use a dynamic content, maybe create a variable with the filename that is given by the SharePoint trigger, something link this:
I agree with you, this should work but unfortunately we are not quite there yet. When you execute this flow you will get the following error message
The parameter ‘file’ has an invalid value ‘your-file-name’
And the reason is because the Excel Connector uses Microsoft Graph to request the file to the SharePoint REST API and your variable with the File name doesn’t have the Microsoft Graph ID required for the call.
After a few hours I finally found this tweet that guided me through the solution and inspired me to write this post.
Here is the solution, since we need the Microsoft Graph ID to work with the Excel Connector we will ask SharePoint what is the ID for the file that was created, for that we just need to use the SharePoint action Send an HTTP request to SharePoint and use the ID in the response as our file.
The SharePoint REST API Uri for the request is composed by https://{your-site}/_api/v2.0/drive/root:/{File Path}/{File Name}
{your-site} you don’t need to send once you already have the Site Address
drive/root:/{File Path}/{File Name} is the Path to to access a DriveItem according to the Microsoft Graph documentation
Once we make the HTTP Request we just need to parse the response, to do so we will use the Compose Action, parsing the body and retrieving the ID, the following statement does the trick:
@{body(‘Send_an_HTTP_request_to_SharePoint’)?[‘id’]}
The output of the Compose action is the Drive Item Graph ID needed to work with the Microsoft Online Excel connector.
Tip: if you don’t also don’t know the name of your table and you know that you’ll have only one table in your file, you can use: @first(body(‘Get_tables’)?[‘value’])?[‘id’]
