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
123 views

Available in Microsoft365

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.

Mandarine AI: CE QUI POURRAIT VOUS INTÉRESSER

RELATED COURSES This tutorial is part of the following courses

118 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