Can Power Automate analyze Excel data that's not in a table?

(2) Share

Report

Posted on by IT_Matt

I've searching for a while with my friends Bing and Google for this answer. How do I get Power Automate to analyze data in an Excel file that's NOT in a table. So I have a large spreadsheet created by another system (it's an export report). There are 14,000 rows and column A thru L all have data in them. To help visualize this I created the test data below (not real data). So, I need to search for "owners" under the "Role" column and then get their "DisplayName", "Company" and "Mail" address. I'm going to write that data to a SharePoint list, but I'm fairly curtain I can figure that part out on my own. I just can't figure out the Excel Connector.

DisplayName	LoginID	Company	Role	Mail
Westley, John	jwestlee	Westside Branch	Employee	jwestlee@automagictone.com
Mackin, Jon	jmackin	Eastside Branch	Owner	jmackin@automagictone.com
Wesseling, Bert	bwesseling	Westside Branch	Owner	bwesseling@automagictone.com
Brent, Lisa	lbrent	Corporate	Owner	lbrent@automagictone.com

Your response is greatly appreciated.

Categories:

General topics

I have the same question (0)

All responses (7)

Answers (0)

Sort by

Yutao Huang Microsoft Employee on at

Like
a
(2)

Report
Copy link

Link copied!
Hi @IT_Matt ,

If converting the data in Excel into table is not an option for you, maybe you could consider trying out the new "Run script" action.
You'll first need to create your script (TypeScript) in Excel Online. Here is a sample script that does what you've described in your question - returns the displayName, company, and mail for the records with "Owner" as the Role.
interface OwnerInfo { displayName: string; company: string; mail: string; } function main(workbook: ExcelScript.Workbook): OwnerInfo[] { let range = workbook.getWorksheet("Sheet1").getRange("A2:E5"); let values = range.getValues(); let result = values .filter(row => row[3] === "Owner") .map(row => ({ displayName: row[0], company: row[2], mail: row[4] })); return result; }

This script assumes the below cells (non-table) on Sheet1. You will need to adjust the above script code for things like sheet name, range address, column index, etc. to match your actual workbook.

Here are some links that might be helpful to learn more about the new Run script action and the Office Scripts in general:
Office Scripts in Excel on the web (overview)
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!
Yutao

Was this reply helpful? Yes No
sumurthy Microsoft Employee on at

Like
a
(3)

Report
Copy link

Link copied!
To add to the solution @Yutao proposed, I'd recommend using the used range API instead (line-8) so that you get all rows each time you run.

let range = workbook.getWorksheet("Sheet1").getUsedRange();

Was this reply helpful? Yes No
IT_Matt 57 on at

Like
a
(0)

Report
Copy link

Link copied!

@Yutao Thank you for the response! Unfortunately, I'm in the GCC cloud and the Run Script for Excel Online is not available yet. GCC lag behind the commercial cloud about 6 - 18 months for most features and previews.
So, how could I create a table if don't know how many rows I will have. The number of rows will probably keep changing with each new report.
Thanks for your help!

Was this reply helpful? Yes No
sumurthy Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!

Sorry, Office Scripts is not yet supported in GCC.
To address your other question about number of rows, my previous response addressed that issue. You can use the getUsedRange() API to get all rows.

Was this reply helpful? Yes No
IT_Matt 57 on at

Like
a
(1)

Report
Copy link

Link copied!

@sumurthy can I use that reference "getUsedRange()" in the Create Table action?

Was this reply helpful? Yes No
sumurthy Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!

The API that I mentioned is only available within Office Scripts.
Excel online offers few out of the box actions such as Read rows, create row, delete row, etc. These are fairly simple and requires data to be in a certain format/shape, etc. They are limited in-terms of what they can do (can't do formatting, etc.). For more complex logic, calculations, etc., you'll need Office Scripts, which can run a TypeScript language script against a file. The script can receive input like any other action and return values like any other action.
So, you'll need to handle all your logic plus creation of table, all in the script itself.

Was this reply helpful? Yes No
Suggested answer

takolota1 4,980 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!

There is also this template Azure Function that allows you to pull data from any Excel sheet without it being formatted as a table...
https://community.powerplatform.com/galleries/gallery-posts/?postid=eea06380-8483-ef11-ac21-7c1e5201b42f

Was this reply helpful? Yes No