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.
  • I am a Microsoft Business Applications MVP and a Senior Manager at EY. I am a technology enthusiast and problem solver. I work/speak/blog/Vlog on Microsoft technology, including Office 365, Power Apps, Power Automate, SharePoint, and Teams Etc. I am helping global clients on Power Platform adoption and empowering them with Power Platform possibilities, capabilities, and easiness. I am a leader of the Houston Power Platform User Group and Power Automate community superuser. I love traveling , exploring new places, and meeting people from different cultures.
  • MCT | SharePoint, Microsoft 365 and Power Platform Consultant | Contributor on SharePoint StackExchange, TechCommunity
  • Encodian Owner / Founder - Ex Microsoft Consulting Services - Architect / Developer - 20 years in SharePoint - PowerPlatform Fan
  • I am the Owner/Principal Architect at Don't Pa..Panic Consulting. I've been working in the information technology industry for over 30 years, and have played key roles in several enterprise SharePoint architectural design review, Intranet deployment, application development, and migration projects. I've been a Microsoft Most Valuable Professional (MVP) 12 consecutive years and am also a Microsoft Certified SharePoint Masters (MCSM) since 2013.
  • Big fan of Power Platform technologies and implemented many solutions.
  • Passionate #Programmer #SharePoint #SPFx #Office365 #MSFlow | C-sharpCorner MVP | SharePoint StackOverflow, Github, PnP contributor
  • Web site – https://kamdaryash.wordpress.com Youtube channel - https://www.youtube.com/channel/UCM149rFkLNgerSvgDVeYTZQ/