Convert Excel data to table

[jf367]

Is there a way to convert Excel data to a table using Power Automate?

I get sent a daily report in Excel (always the same number of columns but amount of rows change), but because it isn't formatted in a table, PowerApps doesn't recognise it. I'm looking for a way to create an automatically triggered Automate to format the data into a table so that it can be used.

Thank you

[Yutao]

Hi @jf367 ,

With the new "Run script" action of the Excel Online (Business) connector, you should be able to run some Office Scripts against a workbook to convert a range of cells into a table.

Here is a sample script that can convert a specified range of cells into a table:

function main(workbook: ExcelScript.Workbook, sheetName: string, range: string, hasHeader: boolean) {
  workbook.getWorksheet(sheetName).addTable(range, hasHeader);
}

And here is a sample flow that consumes this script with an email trigger:

 Here are a few links that might be helpful to learn about using Office Scripts with Power Automate:

- Announcing support for Office Scripts in the Excel Online (Business) connector
- Run Office Scripts with Power Automate
- Call scripts from a manual Power Automate flow
- Pass data to scripts in an automatically-run Power Automate flow

Hope this helps!

[jf367]

Hi @Yutao 

Thank you for the below, the links and screenshot were really helpful, however, I've just found out that my company hasn't enabled Office Scripts in Excel (and aren't going to for some time). Is there any other way around it? If it's easier, I can get the data in CSV format and add it in to a table, but I'm not sure how to do that either?

Thank you again

[Yutao]

Hello @jf367 ,

Sorry actually I should have thought about this - I guess we can probably just use the "Create table" action of the "Excel Online (Business)" connector to convert an existing range into a table. Here is a sample flow:

Thoughts?

[Eduardoosorio23]

Hi 

Can this script be placed in another worksheet or does it have to be in the worksheet that the connector runs on?

[Yutao]

@Eduardoosorio23 - the script is not associated with any workbook so as long as you can see it in the "Script" dropdown list, you should be able to run it against any other workbook that you have access to.

[nich3play3r]

Thanks for this information, @Yutao . I'm looking to create a table in an Excel sheet that's been created in the course of my flow; it will have a static number of columns but variable number of rows. How would I modify this code:

function main(workbook: ExcelScript.Workbook, sheetName: string, range: string, hasHeader: boolean) {
  workbook.getWorksheet(sheetName).addTable(range, hasHeader);
}

Would I set the range variable using the 

getUsedRange()

function?

Thanks! 

[222lovebirds]

Hello,

Does the range have to be determined in the flow? Can it be automatically detected somehow?
I have an excel data sheet sent to me daily and the range changes very frequently.

[nich3play3r]

@222lovebirds you can run that ExcelScript at any point in your flow as code to get the shape of your Excel file in its current state.