cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
IT_Matt
Helper I
Helper I

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

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. 

 

DisplayNameLoginIDCompanyRoleMail
Westley, JohnjwestleeWestside Branch Employeejwestlee@automagictone.com
Mackin, JonjmackinEastside BranchOwnerjmackin@automagictone.com
Wesseling, BertbwesselingWestside Branch Ownerbwesseling@automagictone.com
Brent, LisalbrentCorporateOwnerlbrent@automagictone.com

 

Your response is greatly appreciated. 

6 REPLIES 6
Yutao
Microsoft
Microsoft

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.

Yutao_0-1602212342857.png

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.

Yutao_1-1602212596116.png

 

Here are some links that might be helpful to learn more about the new Run script action and the Office Scripts in general:

Hope this helps!

Yutao

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

 

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

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. 

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

 

IT_Matt_0-1602616361917.png

 

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. 

Helpful resources

Announcements
MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MSFTBizAppsLaunchEvent

Experience what’s next for Power Virtual Agents

See the latest Power Automate innovations, updates, and demos from the Microsoft Business Applications Launch Event.

New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Users online (38,887)