Interacting with Data from Excel without a table

[ahall]

I have an excel file that is emailed to me that I want to add into a SQL database. My issue is the excel file is not configured with a table. How can I get these rows into a table format using flow?

I see the create table function from excel but that creates and empty table with the column headings I choose. Is it possible to create the table with data?

I can get my whole process to work if I start with a file that has a table in it, but my emailed report is not possible to be created with the table from the source so I have to automate that somehow.

[majorfriend]

Hi @ArshUser,

Creating scripts is pretty easy (at least basic scripts are easy). You'll want to open your Excel file in the browser, you'll then see a tab in Excel for Automation. Click on this, then you can create a new script by recording your actions. After you've recorded your actions you are presented with some JS code, you can modify this if necessary or you can just save the script. Then in PA you can use the Run a Script action and select the Excel file you want to run the script on and which script you want to run. 

[ArshUser]

Hey, thanks for the explanation! Sounds straightforward. I'll try it.

[ArshUser]

Hey @majorfriend,

I am facing one more issue regarding this. 

So I need to use the dynamic content from the excel (which can be done only when it is in table form). But as you can see, I am not able to select  a "Table" as it doesn't exist yet. Table will be created after the scrip is run.

How do I go about doing this? Any ideas?

Thanks in advance!

[GeoffRen]

There's two things you can do here:

1. You can return dynamic content using a script. All you do is add `return whateverValueYouWant` to the end. The value can be basically any type you want. For example, if you return an array (like what the 'List rows from a table' connector does) then you can use it like an array in your Flow. This allows you to return dynamic content from non tables.

2. You can just input the name of the table in the Table drop down as a custom field.

[majorfriend]

Thanks @GeoffRen,

Option 1 could be helpful to me as I often run a script, then use Option 2 to list the rows in the table. Do you add 'return whateverValueYouWant' to the end of the JS in Excel? Could you post a screenshot of the code?

[ArshUser]

Hey @GeoffRen thanks for this!

I have been trying the 2nd solution but it gives out an error that the table doesn't exist.

Solution 1 works!

Here is a screenshot @majorfriend 

[majorfriend]

Thanks @ArshUser!

[GeoffRen]

@majorfriend  Yup, you just add it to the end of the main function like @ArshUser did. You can return almost anything (as long as there is type information). 

[Cippacbristian]

Says I dont have access

[Anonymous]

Thank you all for the wonderful discussion and so I came up with below script to be used in power automate. it first try to detect if table exist, if yes, just return the table name, if not, create a new table. Hope this helps.

function main(workbook: ExcelScript.Workbook) {
	let targetTable = workbook.getTable("Table1");
	if (!targetTable) {
		let selectedSheet = workbook.getActiveWorksheet();
		// Create a table with format on range A:T on selectedSheet
		let table = workbook.addTable(selectedSheet.getRange("A:T"), true);
		return table.getName()
	} else {
		return targetTable.getName()
	}	
}

[Anonymous]

I find you can just use the result from the script and you will have the table. like this,

[nich3play3r]

@Anonymous @GeoffRen thanks so much for this info on Office Scripts. Very cool.

FWIW, I'd been trying to create a flow that runs a paginated report every morning, then sends the Excel output from that report *if there are any rows in the report output.* That last part was what I was stuck on, but now I have an Office Script that checks the range (in my case A2:J2) of a worksheet for empty rows. Basically, if that script returns 0, I know that the paginated report has output. If the script result = 0, I create a sharing link to the file and email that off to recipients. If the script result = 1, I delete the file.

[gopla]

Hello, I cannot find the tab for automate,

any help? 

[Anonymous]

Hi, it's only available from Excel Online, not from desktop version. more detail: Office Scripts in Excel on the web - Office Scripts | Microsoft Docs

[gopla]

Thanks for reply

I already use Excel Online

But the automate tab seems like turned off

Any advice?

[Anonymous]

Read this section from the link I copied,  see if that helps, otherwise I also have no idea, sorry.

[gopla]

Thanks @Anonymous !