How to move data from Excel to SharePoint with Power Automate

The world will not run out of different Excel sheets. This time we are wondering about the automation related to Excel file data.

Our example problem is the following:

We have several (identical) Excel files that are used for planning. At the end of the planning phase, selected rows from Excel files are created into the SharePoint list. After that, other people continue to work in there.

Like this.

What would be the most convenient way to automatically copy selected rows from Excel to the SharePoint list?

With Power Automate?

Let’s first consider how the end user initiates the process. In practice, this determines which trigger we will use in our cloud flow.

Option 1 – Embedding Power Automate in Excel

The first thing that comes to mind is to use the cloud flows embedded in Excel. Let’s open Excel file and add the Power Automate for Excel Add-in to it.

Open the flow section from the data tab. There are no flows attached to this Excel file.

Yes. If there are several Excel files, an identical flow must be created for each one. Not nice.

Let’s see how to do this anyway.

Create a new flow that uses For a selected row trigger. In trigger, we define which Excel file and table the flow is connected to.

After that the row selected from Excel is added to the SharePoint list (create item action).

Now we can select table rows in Excel and start the flow. Directly within Excel!

Flow is executed separately for each selected row.

As a result, two rows were added to the SharePoint list. As desired.

Pretty clever, but…

• Every user must add the Power Automate Add-in to Excel
• Each Excel file must have its own flow

In addition, flow must be done in the Default environment.

Maybe we try something else.

Option 2 – Start flow from the SharePoint document library or list

What if the process starts from the document library where the Excels are located? Now a flow is triggered when a file is selected from the document library (For a selected file).

Flow appears when one or more files are selected from the library.

The flow has a separate run for each selected file.

The same can be done from the target end as well. From the SharePoint list the rows will be added eventually. In this case, the flow starts with SharePoint’s For a selected item trigger.

Now the flow is started from the list, where the lines are finally created.

In both methods, the user must first somehow mark the rows in Excel as ready for the transfer. Otherwise, the flow does not know which rows it should move.

But now flow appears in the menu only when at least one line has been selected from the list/document library. A bit tricky in our example. We are looking for a solution where flow can be easily started from one place, it goes through all Excel files and makes the necessary transfers.

Oh yeah. The flows starting from the selected line in the list are also all in the default environment.

Option 3 – Start with single click

Finally, let’s make a flow that can be started from Power Apps and transfers data from Excel files to a SharePoint list.

After this, a simple Power Apps is made to start the flow.

Let’s embed Power Apps in a SharePoint page. To the same place where the Excels and the SharePoint list are located.

Now user only needs to press the button, and the copying from Excels to the SharePoint list starts.

Best of all, both flow and Power Apps can be located in any Power Platform environment.

We have found a convenient way to initiate the transfer. Let’s see how the actual walkthrough of the Excels is carried out.

Processing multiple Excel files with Flow

The flow starts from Power Apps. First, we list the files of the document library containing our Excel data using the Get files action.

For each Excel file, we get all the tables it contains.

The Excel files must have a table with the same name (BuMonthly).

Next, we look for that table in Excel (with Filter array).

And we list the marked rows (isReady eq ‘Yes’) from the table we found.

We don’t want to create rows in SharePoint if they have already been created before. Our Excels do not have rows with the same BU and month combination, so let’s check if there is a row with the same BU and month combination in the SharePoint list.

If no line is found (length of the result set = 0), one is added. We also store source Excel information on the row.

Ready!

The flow as a whole looks like the following.

Excel without a table?

Flow can only process the lines of an Excel file if they are in a table. What if, for example, an Excel file has been sent from some 3rd party tool, and the rows are not in the table?

Like this.

No worries. You can use Power Automate to create this missing table (Create table action). In this case, you need to know from which cell the table starts and where it ends (Table range),

The table and columns can be named.

After executing the action, our Excel sheet will look like this.

Now we can process its rows in the normal way with flow.

Restrictions

The Excel connector is really neat, but when working with it, it’s good to be aware of its limitations. For example the following.

• It’s slow. Very slow. Using Graph API instead of ready-made actions is way faster!
• The Excel file can be maximum 25MB in size
• You can filter rows in an Excel table using OData. But you can filter only with one column.
• By default, only 256 rows of the table are listed. Using paging, 100,000 rows are retrieved.
• Modifying the Excel file is slow. New rows can appear in Excel 30 seconds after the execution of the writing function
• Flow locks the Excel file for a long time (up to 6 min)

The Excel Online connector can perform 100 operations in a 60-second window. After this, it will be throttled.

Despite its limitations, a great connector. Because business data can be found in Excel files everywhere.