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

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

[Yutao]

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

[sumurthy]

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();

[IT_Matt]

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

[sumurthy]

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. 

[IT_Matt]

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

[sumurthy]

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.