cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jf367
Frequent Visitor

Convert Excel data to table

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

8 REPLIES 8
Yutao
Microsoft
Microsoft

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:

Screenshot - 8_5_2020 , 10_19_41 AM.png

 

 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
Frequent Visitor

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

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:

 

Screenshot - 8_6_2020 , 1_25_08 AM.png

 

Thoughts?

Hi 

 

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

Yutao
Microsoft
Microsoft

@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.

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! 

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.

@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.

Helpful resources

Announcements
MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Power automate tips 768x460 v2.png

Restore a Deleted Flow

Did you know that you could restore a deleted flow? Check out this helpful article.

Users online (3,207)