PowerAutomate Get Tables Usage

[MikeWJS]

Hi,

I have a file that is recreated each night.

The file does not contain a table so I have used the create table in PA to create a table (table1) which works OK.

I am using Powerautomate to import the spreadsheet which when run manually works.

However, when scheduling, because the table is recreated each time, the flow fails and a hex string is in the import table name because the uid is different each time the tabel is createdl.

I think I need to use get tables function in PA to get the table ID and then pass it to the create sharepoint list step but the problem is, unless select the dropdown from the previous step, I am unable to map the fields from the excel spreadsheet to the sharepoint list.

Could anyone possibly help on how I can acheive this?

Thanks,

[MikeWJS]

To clarify - if I try to use "Table1" the flow fails as it is looking for the hex value.

If I try to use a dynamic field I cannot map the fields as per this screenshot

If anyone could help me out I would be extremely appreciative. I have spent 6 hours today trying to find a solution and have been pushed to the brink of insanity 😄

[rubin_boer]

hi @MikeWJS you on the right track. when you create the table the output of that step will give you the name of the table, which you then use to get the data to update SharePoint.

Flow

Result

Hope it Helps,

R 

[MikeWJS]

Thank you sooo much @rubin_boer for taking the time to respond, you are very kind. A reply after trying to get this working after 8 hours has certainly made my day! Any feedback is welcome.. The part I am struggling with is the next step of creating the items in sharepoint, if you are kind enough could you possibly show me how that part would work?

[rubin_boer]

hi @MikeWJS my apology o thought the issue was the table 🙂

To update SharePoint ad the following steps to the above:

• Control: Apply to each
  • the value output from List rows present in table goes here
• SharePoint: Create Item
  • the columns will not be listed in dynamic content, you need to add an expression for your tables

• item()?['Title']
• item()?['uid']
• item()?['value']

make sure your table names match your table in Excel

There you go.

Result

 

Hope it helps,

R

[MikeWJS]

@rubin_boer 

Thanks so much for your reply and help. I am currently on annual leave but will attempt this when I return and let you know how I get on!

Once again, many thanks!

[MikeWJS]

Thanks for your help,

I eventually got it working using the items ('Apply_to_each') [Column name']

It looks like this for informations sake.

Although the flow works fine, it errors out.

This is because the number of rows in the spreadsheet is unknown. I therefore have to set the table to be created for 5000 rows but after the actual data is imported, it creates lots of blank entries so expects a number instead of a blank record. How can I possibly suppress this error please?

Once again, thanks for your help. I may mark this question as answered and open another question for the error above.

[abhilsbishal10_]

Hi @rubin_boer , I having the same problem and did everything but couldn't find what's causing the problem. I have a excel file text.xlsx in share point which get replace every day with the same column, table name everything same but with fresh data. Now every time that file gets replaced I want to attach the excel table content in the body of the email. 
If I use, List rows present in a table, I get 404 table not found error. And if I use List rows present in a table it gives me 404 table not found error

And if I use Get tables and list rows like @MikeWJS I cannot find the column name in select action to attach table in Send email action. I have tried every solution I can find so can you please help with this.
Thanks

[MikeWJS]

Hi,

Just thought I would chip in how I eventually got this all working.

Here is the flow,

While this situation may be different to yours (my file comes from OneDrive) you may be able to adapt it?

Basically, a script is run in Excel online which passes the information to the flow.

The script is,

function main(workbook: ExcelScript.Workbook,
  SheetName: string = "Report 1"
) {


  const selectedSheet = workbook.getWorksheet(SheetName);
  let data = selectedSheet.getUsedRange().getValues()
  console.log(data)
  return (data)
}

Note my data is in a sheet called Report 1.

Then this is used to populate a Sharepoint list in my case (again, just use the fields in the body of the email etc., adapting as necessary) using this for the items,

items('Apply_to_each')?[0]

items('Apply_to_each')?[1]

items('Apply_to_each')?[2] etc. etc. 

Mine then matches the fields to the corresponding Sharepoint list fields.

Hope this may help you get there....

[antc2]

For anyone looking for a solution where new files are uploaded just with 1 table just use Get Table first and use first() to pass to List rows action.