Power Automate / Flow - Excel: List Files from a Channel in an Excel Workbook with Power Automate Tutorial

Discover how to list files from a channel or library and store the list in an Excel file using Power Automate. This provides a comprehensive overview of your files, enabling better organization and management. Learn how to create an instant flow, add a SharePoint action, and generate a new worksheet with a unique name for each execution. With step-by-step guidance, this tutorial showcases the powerful combination of Power Automate and Excel for efficient file management.

  • 04:51
  • 450 views

Objectifs :

This tutorial aims to guide users on how to add content to an Excel file using Power Automate, specifically by listing files from a SharePoint library or a team's channel and storing this information in an Excel file for better organization and management.

Chapitres :

  1. Introduction to Power Automate and Excel Integration
    In this tutorial, we will explore how to effectively use Power Automate to add content to an Excel file. This process is particularly useful for listing files from a SharePoint library or a team's channel, allowing users to maintain an organized overview of their files.
  2. Setting Up the Instant Flow
    To begin, select an instant flow that can be executed as needed. Instant flows can be triggered through the mobile app or directly from your list of Power Automate flows. This flexibility allows for on-demand execution whenever necessary.
  3. Adding SharePoint Action to List Files
    Next, add a SharePoint action to list the files in a channel, team, or SharePoint library. Choose the 'Get Files' action and define the site or relevant team along with the library to focus the flow on a specific folder or channel. Enable advanced options to exclude folders and retain only file-type items.
  4. Specifying the Folder and Including Subfolders
    In this example, we will limit the results to a specific folder named 'Training Folder,' which corresponds to the team's channel. Additionally, we will include files present in the subfolders of this channel to ensure a comprehensive listing.
  5. Choosing the Excel File for Listing
    Select an Excel file to host the listing. Ensure that each execution creates a new worksheet by using the 'Create Worksheet' action. Specify the location and name of the Excel file, which will be stored on your OneDrive, and define the sheet name using a Power FX function that includes the date and time of the export.
  6. Creating a Table in the Excel Workbook
    Once the sheet is created, proceed to create a table in the same workbook. Specify the range of the table on the previously created sheet. Use dynamic content to retrieve the name of your sheet, ensuring the correct format with apostrophes and an exclamation mark followed by the range.
  7. Defining Table Structure and Populating Data
    To accommodate varying numbers of files, create a table with one row and three columns. Customize the table name using Power FX and the 'Conca' function to ensure each table has a unique name. Define the names of the three columns, separated by commas, and prepare to populate the table with data.
  8. Implementing the 'Apply to Each' Loop
    Generate an 'Apply to Each' loop to process each item related to the results of the 'Get Files' action. Include the action of creating a row from the Excel actions, specifying the target Excel file and referring to the previously created table using dynamic content.
  9. Writing Data to the Excel Table
    In the JSON syntax, open a brace and refer to the field names in quotes, followed by a colon. For the name column, retrieve the full name of the file using dynamic content. For the second field, retrieve the storage folder, and for the third column, include the name of the last user who modified the file.
  10. Testing and Sharing the Flow
    After saving and testing the flow, check the Excel file to confirm that a sheet has been created with a table hosting each file from your team's channel. To enhance usability, share the flow via Power Automate and add a workflows application tab in the team's listing to make it easily accessible.
  11. Conclusion
    This flow successfully demonstrates the capabilities of Power Automate in conjunction with Excel. By following these steps, users can efficiently manage and organize their files, showcasing the potential of automation in everyday tasks.

FAQ :

What is Power Automate used for?

Power Automate is used to create automated workflows that connect different applications and services, allowing users to automate repetitive tasks and processes.

How do I create an Instant Flow in Power Automate?

To create an Instant Flow, go to Power Automate, select 'Create', choose 'Instant Flow', and then define the trigger and actions you want to automate.

Can I list files from SharePoint in Excel using Power Automate?

Yes, you can use Power Automate to list files from a SharePoint library or channel and store that list in an Excel file.

What is the purpose of using Dynamic Content in Power Automate?

Dynamic Content allows you to reference data from previous steps in your flow, making it easier to customize actions based on the data processed.

How can I ensure each execution of my flow creates a new worksheet in Excel?

You can use the 'Create Worksheet' action in Power Automate and specify the sheet name using a Power FX function to include the date and time of the export.

What should I do if I want to exclude folders from the file listing in Power Automate?

You can enable advanced options in the SharePoint action to exclude folders and only keep file-type items in your results.

Quelques cas d'usages :

Automating File Listings for Team Collaboration

A marketing team can use Power Automate to automatically list all files in their SharePoint channel and store this information in an Excel file. This helps them keep track of documents, ensuring that everyone is aware of the latest materials available for campaigns.

Monthly Reporting of Project Files

A project manager can set up a flow to generate a monthly report of all files related to a project stored in SharePoint. The flow will create a new worksheet in an Excel file each month, allowing for easy tracking of project documentation over time.

Tracking Document Modifications

An IT department can implement a flow that logs every modification made to files in a specific SharePoint library. By storing this information in Excel, they can analyze who modified which files and when, improving accountability and tracking.

Streamlining File Organization

A team can use Power Automate to regularly list files in their SharePoint library and identify outdated or unnecessary documents. This allows them to reorganize or delete files efficiently, maintaining a clean and organized workspace.

Glossaire :

Power Automate

A cloud-based service that allows users to create automated workflows between applications and services to synchronize files, get notifications, collect data, and more.

Instant Flow

A type of flow in Power Automate that can be triggered manually at any time, allowing users to execute specific tasks on demand.

SharePoint

A web-based collaboration platform that integrates with Microsoft Office, used for storing, organizing, sharing, and accessing information from any device.

Excel

A spreadsheet program developed by Microsoft that allows users to organize, format, and calculate data with formulas using a system of rows and columns.

Dynamic Content

Content that is generated dynamically based on the context of the flow, allowing users to reference data from previous steps in their workflows.

Power FX

A low-code programming language used in Microsoft Power Platform to create custom logic and expressions in applications.

JSON Syntax

JavaScript Object Notation, a lightweight data interchange format that is easy for humans to read and write, and easy for machines to parse and generate.

Table

A structured set of data made up of rows and columns in Excel, used to organize and analyze information.

résultat(s)
00:00:03
we will discover together how you can add
00:00:05
content to an Excel file via power automate.
00:00:08
For example,
00:00:09
you may want to list the files in a library or
00:00:12
a team's channel and store this list in an Excel file.
00:00:15
This will allow you to get an overview of the items present,
00:00:18
providing a global view of the structure of your channel's
00:00:21
files for the purpose of reorganizing or deleting some items.
00:00:24
For instance
00:00:26
to do this, choose an instant flow that can be executed as needed.
00:00:30
Remember that instant flows can be triggered via the mobile
00:00:33
app or directly in your list of power automate flows.
00:00:39
The next step is to add a SharePoint action to list the files in a channel,
00:00:43
a team's team or a SharePoint library.
00:00:46
Here, select the get files action
00:00:50
in the properties, define the site or the relevant team
00:00:54
as well as the library
00:00:57
to limit the flow to a specific folder or channel. Enable advanced options.
00:01:01
Take this opportunity to exclude folders from the
00:01:04
result and only keep file type items.
00:01:08
In this example,
00:01:09
we will limit the result to a specific folder named training
00:01:13
folder corresponding to the team's channel of the same name.
00:01:17
We will also choose to include the files present in the sub folders of this channel.
00:01:22
Next choose an Excel file to host this listing.
00:01:25
We will ensure that each execution creates a new worksheet.
00:01:29
The create worksheet action is designed for this purpose.
00:01:34
After specifying the location and name of the Excel file in question,
00:01:38
which is here on your onedrive, define the sheet name using a power FX function.
00:01:43
The name of this sheet will include the date and time of the export.
00:01:50
Once the sheet is created, create a table in this same workbook and specify it again.
00:01:59
The range of the table that is its
00:02:01
location should be on the previously created sheet.
00:02:04
Retrieve the name of your sheet via dynamic content.
00:02:08
Note that the location within your workbook must follow the classic Excel syntax.
00:02:13
The sheet name between apostrophes and exclamation mark
00:02:17
followed by the range of your table.
00:02:19
For example,
00:02:20
if you want to populate three columns but are unsure of
00:02:23
the number of rows depending on the number of files found,
00:02:26
create a table of one row over three columns.
00:02:29
A one C one
00:02:32
in the advanced settings, customize the table name
00:02:35
using power FX, use the conca
00:02:38
function to name your table followed by the position number of your Excel sheet.
00:02:43
Two for the second three, for the third et cetera.
00:02:47
Each table will thus have a unique name,
00:02:50
define the name of your three columns. Separated by commas.
00:02:55
This table should be populated by generating a row for each file found.
00:03:01
Therefore,
00:03:02
generate an apply to each loop which will individually process
00:03:07
each item related to the result of the get files.
00:03:10
Action
00:03:14
include the action of creating a row still from the actions dedicated to Excel.
00:03:21
Once again, specify your target Excel file in the table.
00:03:25
Property refer via dynamic content to the destination
00:03:28
corresponding to the name of the table created previously
00:03:33
to populate the columns,
00:03:34
write a Jason syntax by opening a brace referring to the field name in quotes,
00:03:39
followed by a colon
00:03:41
for the name column, retrieve the full name of the file using dynamic content.
00:03:49
Enter a comma and for the second field path, retrieve the storage folder.
00:03:53
In the same way,
00:03:57
the third column will contain the name of the last user who modified the file
00:04:03
save and test this flow
00:04:06
in the Excel file.
00:04:07
A sheet has indeed been created with a table
00:04:10
that hosts each file from your team's channel.
00:04:15
The flow was successful
00:04:16
to make this flow easily usable by your team members, share it via power automate.
00:04:26
You can then add a workflows application tab
00:04:28
in the team's team listing the shared flows.
00:04:31
At this point, you will indeed find your flow,
00:04:35
try a new execution and you will see that a second
00:04:38
sheet is created in your workbook and your table is populated.
00:04:42
This flow is a success and it allows you to measure
00:04:45
a sample of the possibilities of power automate combined with Excel.

No elements match your search in this video....
Do another search or back to content !

 

Mandarine AI: WHAT YOU SHOULD KNOW

RELATED COURSES This tutorial is part of the following courses

508 Views

Microsoft Video Training Power Automate – From discovery to mastering automation

Boost your productivity with our Power Automate course! Learn to automate your tasks and seamlessly integrate various Microsoft 365 services...

ALL COURSES

An Error Has Occurred

Unable to save your progress. Please reload the page, log in, and try again. If the problem persists, please contact an administrator.

Close

Reminder

Show

/

Join Live Training Session