Unanswered

Create a table in an Excel workbook in a specific folder

(1) Share

Report

Posted on by bblande

We have a folder where an Excel file is dropped in SPO about every two weeks. It has tons of columns and rows, but not in table format.

I've looked at the Create table action in Power Automate, but it requires a specific file instead of just looking in a folder. Is there dynamic content or an expression that can be used to create the table in a newly created file in the folder without specifying a file name?

Categories:

Building flows

I have the same question (0)

All responses (5)

Answers (1)

ScottShearer 25,270 Most Valuable Professional on at

Like (1)

Report

You probably want to take a look at using Office script to create the table. The script can be executed from Power Automate when a new item is added to the folder.

Was this reply helpful? Yes No
bblande 59 on at

Like (1)

Report

Yeah I should have mentioned that I'm in a DoD environment and scripts are a no-go around these parts, but thanks.

Was this reply helpful? Yes No
ScottShearer 25,270 Most Valuable Professional on at

Like (1)

Report

@bblande

I am familiar with the DoD and these are a different type of script - you might want to try.....

Another option is for you to rename the files via a Flow when they are uploaded. This will allow you to specify a specific file in the Create table action. Then, after the table is created, rename it again to what is was. If you use this approach, you'll likely need to use a number of pauses or a loop as you will probably see file in use errors.

Was this reply helpful? Yes No
Verified answer

grantjenkins 11,063 Moderator on at

Like (3)

Report
Below is how I would likely build this.

See full flow below. I'll go into each of the actions.

I would use the trigger When a file is created (properties only) so each time you upload/create a new Excel file it will trigger the flow. In my trigger below it's looking for new files in my Documents library within the folder called Drop Excel Files - so any file created in that folder (including within subfolders) will work.

In addition - I would also add a Trigger Condition to ensure the flow only runs if the file uploaded is an Excel file. To do this, go into Settings then add a Trigger Condition and use the following expression.

@endsWith(triggerOutputs()?['body/{FilenameWithExtension}'], '.xlsx')

For the Table - As long as your Excel file has the table content starting at cell A1 and the first row contains your headers, then this should dynamically select your range of data and use the existing column headers.

=$A$1:INDEX($1:$1048576,COUNTA($A:$A),COUNTA(1:$1))

Was this reply helpful? Yes No
takolota1 4,974 Moderator on at

Like (1)

Report

You can also use this template Azure Function to pull data from any Excel sheet without the need for table formatting...
https://community.powerplatform.com/galleries/gallery-posts/?postid=eea06380-8483-ef11-ac21-7c1e5201b42f

Or if you need something without premium connectors, then you can use these Office Script templates…
https://community.powerplatform.com/galleries/gallery-posts/?postid=9f7a7bcf-c88f-ef11-ac20-7c1e525bd67d

Was this reply helpful? Yes No