Microsoft Excel Power Automate Flow

Like (1) Share

Report

Posted on 16 Dec 2024 16:53:02 by AC-16121504-0

Hi all,

I am looking to automate an excel workflow and then send an email based on the output after the workflow has run. Firstly I'd like to understand if power automate can accommodate what I want to do. In summary when excel file 1 is copied into a specific sharepoint folder. I would like excel file 2 (also in this folder) to delete the contents in worksheet 4 (3300 rows , 7 columns), copy the contents of worksheet 3 (3300 rows , 7 columns) and paste them into worksheet 4. Then for the contents of worksheet 1 (3300 rows , 7 columns) from excel file 1 to be copied into worksheet 3 in excel file 2. Output 1 or Output 2 is read from worksheet 1 in excel file 2 and email 1 or email 2 is sent on the back of that.

I would greatly appreciate if anyone could advise if that might be possible using the online version of power automate. Thanks.

Categories:

Business Process Flows

All responses (3)

Answers (0)

AC-16121504-0 6 on 21 Jan 2025 at 10:54:02

Like (0)

Report

Microsoft Excel Power Automate Flow

Thank you very much for the help, both of these answers helped greatly getting my power automate flow and script to work.
Suggested answer

Robu1 1,218 Super User 2025 Season 1 on 16 Dec 2024 at 22:39:46

Like (1)

Report

Microsoft Excel Power Automate Flow

Hi AC-16121504-0

Thank you for choosing Microsoft Community.

Power Automate can handle your request.

Here’s a step-by-step outline of how you can set this up:

Trigger: Use the "When a file is created in a folder" trigger for SharePoint to monitor the specific folder for when Excel file 1 is added.

Delete Contents: Add an action "Run a script" for Excel Online (Business) to access Excel file 2 in the same SharePoint folder. Use the script to delete the contents of Worksheet 4.

Copy Data: In the same script, copy data from Worksheet 3 to Worksheet 4.

Retrieve Data: Use the "List rows present in a table" action (or "Get worksheet" if no table exists) for Excel file 1, retrieve data from Worksheet 1, and then use "Run a script" for Excel file 2 to paste this data into Worksheet 3.

Check Output: Use another "Run a script" action to check a specific cell or range in Worksheet 1 for Output 1 or Output 2.

Send Email: Add a "Condition" action to evaluate the output from the previous step. If it matches "Output 1," use the "Send an email (V2)" action to send Email 1. If it matches "Output 2," send Email 2.

If this Post helped you, please click "Does this answer your question" and like this post to help others in the community find the answer too!

Happy to help.

Robu 1
Suggested answer

SwatiSTW 558 Super User 2025 Season 1 on 16 Dec 2024 at 17:10:48

Like (1)

Report

Microsoft Excel Power Automate Flow

Yes, Power Automate can handle your request. We can use Office Scripts and standard Power Automate connectors.

1. Use the "When a file is created in a folder" trigger for SharePoint to monitor the specific folder for when Excel file 1 is added

2. Add an action "Run a script" for Excel Online (Business) to access Excel file 2 in the same SharePoint folder.

3. Use the "Run a script" action to delete the contents of Worksheet 4. You'll need an Office Script similar to this

function main(workbook: ExcelScript.Workbook) {
let sheet = workbook.getWorksheet("Sheet4");
sheet.getUsedRange()?.clear(ExcelScript.ClearApplyTo.contents);
}

4. In the same script, copy data from Worksheet 3 to Worksheet 4

function main(workbook: ExcelScript.Workbook) {
let sheet3 = workbook.getWorksheet("Sheet3");
let sheet4 = workbook.getWorksheet("Sheet4");
let data = sheet3.getUsedRange().getValues();
sheet4.getRange("A1").setValues(data);
}

5. Use the "List rows present in a table" action (or "Get worksheet" if no table exists) for Excel file 1, retrieve data from Worksheet 1, and then use "Run a script" for Excel file 2 to paste this data into Worksheet 3, Following script may help.

function main(workbook: ExcelScript.Workbook, data: string[][]) {
let sheet3 = workbook.getWorksheet("Sheet3");
sheet3.getRange("A1").getResizedRange(data.length - 1, data[0].length - 1).setValues(data);
}

6. Use another "Run a script" action to check a specific cell or range in Worksheet 1 for Output 1 or Output 2:

function main(workbook: ExcelScript.Workbook): string {
let sheet1 = workbook.getWorksheet("Sheet1");
return sheet1.getRange("A1").getText(); // Adjust range as needed
}

7. Add a "Condition" action to evaluate the output from the previous step. If it matches "Output 1", use the "Send an email (V2)" action to send Email 1. If it matches "Output 2," send Email 2.

Community site session details

Microsoft Excel Power Automate Flow

Helpful resources

News and Announcements

Quick Links

🌸 Community Spring Festival 2025 Challenge Winners! 🌸

Warren Belz – Community Spotlight

Congratulations to the April Top 10 Community Stars!

Leaderboard > Power Automate - Building Flows

Featured topics

Product updates

Community site session details

Microsoft Excel Power Automate Flow

Helpful resources

News and Announcements

Quick Links

🌸 Community Spring Festival 2025 Challenge Winners! 🌸

Warren Belz – Community Spotlight

Congratulations to the April Top 10 Community Stars!

Subscribe to this forum!

Select categories

Leaderboard > Power Automate - Building Flows

Featured topics

Product updates