Unanswered

When a file is created or modified (properties only) - No worksheet selection

(0) Share

Report

Posted on by KooiJit

Hi,

I am working on getting the latest Excel file in a subfolder of Shared Documents in Sharepoint. The path is like this: https://sharepoint.com/sites/Test/Shared%20Documents/pass

All the Excel files in the subfolder are named as Report_DDMMMYY format (I cannot make changes as the owner wants to keep it like this.) The Excel file has three worksheets. I just want to retrieve data from the first worksheet.

So far, only the 'List rows present in a table' action allows me to specify the worksheet. However, it becomes tedious since we have to give the specific link every time a new file comes into Sharepoint.

I wonder is there any way to get the data from the Excel file, like an action that can capture the last modified time while getting the first worksheet?

And since this is not a Sharepoint List. I am unable to use any action that requires 'List Name'.

Desired action:

Current action that I use:

Any help is very much appreciated. Thank you.

Categories:

General topics

I have the same question (0)

All responses (8)

Answers (1)

v-jefferni on at

Like (0)

Report
Hi @KooiJit ,

Using below method, you will be able to get the first table from dynamic Excel files:

first(outputs('Get_tables')?['body/value'])?['name']

Best regards,

Was this reply helpful? Yes No
KooiJit 28 on at

Like (0)

Report

Hi, @v-jefferni

Thanks for helping.

I got an error at the 'Get tables' action. I suspect the error is caused by the trigger (first step) but I am not sure which part I should modify.

Here are more details about my flow:

Was this reply helpful? Yes No
v-jefferni on at

Like (1)

Report

Hi @KooiJit ,

I tested it in new designer, and found that the Get tables action needs Identifier than Full path:

Best regards,

Was this reply helpful? Yes No
KooiJit 28 on at

Like (0)

Report

Hi @v-jefferni

After changing to 'Identifier', it works.

But now it stopped at the 'List rows...' action
Here is the view of the 'List rows...' action
I wonder if it is because of the table function. I put it as:
first(outputs('Get_tables')?['body/value'])?['name']
For the 'name', should I modify to worksheet name or action name?

Was this reply helpful? Yes No
v-jefferni on at

Like (0)

Report

Hi @KooiJit ,

I can run the flow successfully using the expression to get the first table in the Excel. This 'name' means the name of the first table in the workbook. Could you please expand the outputs of Get tables? I'm wondering if there's one table in that workbook.

Best regards,

Was this reply helpful? Yes No
KooiJit 28 on at

Like (0)

Report

Hi @v-jefferni

Here is the output of 'Get Table'
Cont.

Was this reply helpful? Yes No
Verified answer

v-jefferni on at

Like (1)

Report

Hi @KooiJit ,

I tried id instead of name and it worked as well.

first(outputs('Get_tables')?['body/value'])?['id']

Maybe the names have been customized but cannot be recognized by Power Automate.

Best regards,

Was this reply helpful? Yes No
KooiJit 28 on at

Like (0)

Report

Hi @v-jefferni

Thank you so much for all the support!
The flow is successful now.

I have made two changes.
The first one is I have modified the function as your suggestion: first(outputs('Get_tables')?['body/value'])?['id']
The second one is the 'File'. I noticed in 'Get Table', that the Identifier has {}, but in 'List rows...', the identifier isn't shown as {Identifier} no matter how many times I click and refresh. So I choose {FullPath}. And somehow it just work!
Thanks for guiding me step by step. Thank you so much, sincerely.

Was this reply helpful? Yes No