Flow / Power Automate - 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
  • 16 views
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 !

 

DiLeaP AI: THIS MIGHT BE HELPFUL

Reminder

Show