One of the connectors in Microsoft flow is the Excel connector. Actually there are 3 connectors as I described in one of my past posts. I’ve seen many people complain that the connector doesn’t work for them and the Excel file becomes unusable or it is locked resulting inn the flow failing.

I’m assuming that I have an empty Excel file. My flow therefore first needs to create a table so that I can add rows to my table. However I only want to create a table if the table doesn’t already exist.

To make a bit more sense I tried to get the stats of my SharePains blog by getting my statistics on an hourly basis and then adding details to an excel table.

Then I will add the data to my table using the Add a row into a table action

 

Add a row into a table

That is all easy, but I still need to create the table.

 

If I only have one table in my excel file you could simply run a Get tables and if any tables are returned then get on with the row addition with the above mentioned action. But I might have multiple tables. So I’m going to do some additional checks.

I will start by initialising a variable.

Then I will get all the tables in my excel file and when I find a table with the name Data then I’m setting the FoundData variable to true

Now that we now that the data is found or not we can create the table in the excel file with all the right columns only if the table doesn’t yet exist.

In the past I would simply use the run actions and accept that the Create table would fail after the first run of my flow. By configuring the the run actions I would then ignore the failure and continue. This approach however resulted in locked files and problems with the stability of my flow. With the approach of only creating a table when it is needed, my flow has become a lot more stable.

This has now resulted in an excel file giving me the website stats on an hourly basis:

 

 

Advertisements