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.
