Merge Multiple SharePoint Lists to one Excel file ...

DeepakS · ‎08-06-2020

The Need:

There is not an easy and direct method to export data from multiple SharePoint lists and consolidate it in a single Excel file.

The Solution:

To merge multiple SharePoint lists in single excel, we will use followings Power Automate actions:

Excel Actions
SharePoint Actions

I have the create two sample SharePoint lists to showcase the merge process:

List 1

List 2

The FLOW:

The Following flow will Showcase on how you can:

Create a new worksheet in an excel file (stored in SharePoint or One-drive)
Add Table with predefined columns in the worksheet
Connect to SharePoint list and add rows to this newly created Table

Step 1:
Create Excel worksheet (tab)

Name: Create the tab dynamically and set the name. I am setting tab name as of today's date:

formatDateTime(utcNow(),'yyyy-MM-dd')

Step 2:

Create an Excel Table:

Table range: Create an Excel table dynamically by selecting an excel worksheet (tab) name and providing column range.

body('Create_worksheet')?['name']'!A1:D1

!Note: if you provide only column range without worksheet name, it will create the table in the first worksheet (tab)

Table Name: Static or Dynamic as needed
Column Names: Provide the column name for the table

Step 3: (repeat this step for each SharePoint List)

Get SharePoint Lists items using "Get items" Action.
Use Apply to each List Item loop and add an item from SharePoint to Excel list using "Add a row into a table."

Table: Select the table name that you created in step 2.

Body: Once you use table name dynamically, you need to provide excel row details in a JSON format like below:

{
"Excel Table Column Name":" SharePoint List Column value"
}

Example:

{
  "Title": "items('Apply_to_each_List_2')?['Title']", 
  "FirstName": "items('Apply_to_each_List_2')?['FirstName']",
  "LastName": "items('Apply_to_each_List_2')?['LastName']",
  "Age": "items('Apply_to_each_List_2')?['Age']"
}

The conclusion:
With this approach, you can export multiple SharePoint Lists into a single Excel file. The procedure can be used for one time export or periodically export.