cancel
Showing results for 
Search instead for 
Did you mean: 

Merge Multiple SharePoint Lists to one Excel file Dynamically

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

DeepakS_1-1596398341250.png

 

List 2

DeepakS_2-1596398415251.png

 

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)

DeepakS_3-1596398643211.png

 

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:

DeepakS_4-1596398801685.png

 

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."

DeepakS_5-1596399078506.png

 

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.

Meet Our Blog Authors
  • Experienced Consultant with a demonstrated history of working in the information technology and services industry. Skilled in Office 365, Azure, SharePoint Online, PowerShell, Nintex, K2, SharePoint Designer workflow automation, PowerApps, Microsoft Flow, PowerShell, Active Directory, Operating Systems, Networking, and JavaScript. Strong consulting professional with a Bachelor of Engineering (B.E.) focused in Information Technology from Mumbai University.
  • Encodian Owner / Founder - Ex Microsoft Consulting Services - Architect / Developer - 20 years in SharePoint - PowerPlatform Fan
  • Cambridge UK Power Platform User Group Leader, Technical evangelist and speaker. Always says yes to coffee! #LetsGetCoffee
  • Passionate #Programmer #SharePoint #SPFx #Office365 #MSFlow | C-sharpCorner MVP | SharePoint StackOverflow, Github, PnP contributor
  • I am building business processes and applications that are easy for users' to stick to, so they can follow and understand them. In overall I transform processes to be more reliable and effortless. I am a proud co-organizer of SharePoint Saturday Warsaw and active community member, blogger and international speaker.