Unanswered

Flow takes HOURS to run

(0) Share

Report

Posted on by Kuromi102

Hello everybody,

I'm fairly new with Microsoft Power Automate. I want to apply this office script to multiples Excel sheets in my Onedrive folder. This is the flow that I create, not sure why but it takes HOURS to run/test and I ended up canceling all of them because it took too long. I have a slight idea that it might be because of my license (I don't really understand about what licenses out there that allow me to run my script in about 30 Excel sheets). I have Microsoft 365 Business plan but Power Automate plan free.

Do I really have to upgrade my license to have flow run faster? Is there anyway I can improve this? For example replace "Apply to each" by another function? Thank you!

Categories:

Using flows

I have the same question (0)

All responses (14)

Answers (0)

MichelleRanMSFT Microsoft Employee on at

Like (0)

Report

Hi @Kuromi102 - sorry to hear that you're running into performance issues! While Power Automate licensing does affect certain limits such as the number of requests you can make, I don't think it should affect performance. There might be room to optimize your scripts so they run faster. Would you be able to share your script code?

Was this reply helpful? Yes No
Kuromi102 14 on at

Like (0)

Report
Thank you for your response! This is the Office Script code that I wrote.

function main(workbook: ExcelScript.Workbook) { let selectedSheet = workbook.getActiveWorksheet(); // Auto fit the columns of all cells on selectedSheet selectedSheet.getRange().getFormat().autofitColumns(); // Delete range E:F on selectedSheet selectedSheet.getRange("E:F").delete(ExcelScript.DeleteShiftDirection.left); // Insert at range B:B on selectedSheet, move existing cells right selectedSheet.getRange("B:B").insert(ExcelScript.InsertShiftDirection.right); // Set range B1 on selectedSheet selectedSheet.getRange("B1").setValue("Undeliverable Date"); // Insert at range D:D on selectedSheet, move existing cells right selectedSheet.getRange("D:D").insert(ExcelScript.InsertShiftDirection.right); // Insert at range D:D on selectedSheet, move existing cells right selectedSheet.getRange("D:D").insert(ExcelScript.InsertShiftDirection.right); selectedSheet.getRange("D:D").insert(ExcelScript.InsertShiftDirection.right); // Text to columns on range C1:C33 on selectedSheet let lastRow = (selectedSheet.getUsedRange().getLastRow().getRowIndex() + 1).toString(); for (let row = 0; row < selectedSheet.getRange("C1:C" + lastRow).getRowCount() ; row++) { let sourceRange = selectedSheet.getRange("C1:C" + lastRow); let destinationRange = selectedSheet.getRange("D1"); let sourceRangeValuesArray = sourceRange.getRow(row).getValues()[0][0].toString().split(/[\t ]/) let sourceRangeValues = [sourceRangeValuesArray[0], sourceRangeValuesArray[1], sourceRangeValuesArray.slice(2,sourceRangeValuesArray.length).join(" ")]; destinationRange.getOffsetRange(row, 0).getResizedRange(0, sourceRangeValues.length - 1).setValues([sourceRangeValues]); } // Set range C1:F1 on selectedSheet selectedSheet.getRange("C1:F1").setValues([["Full Name","First Name","Middle Name","Last Name"]]); // Text to columns on range K1:K33 on selectedSheet for (let row = 0; row < selectedSheet.getRange("K1:K" + lastRow).getRowCount() ; row++) { let sourceRange = selectedSheet.getRange("K1:K" + lastRow); let destinationRange = selectedSheet.getRange("L1"); let sourceRangeValues = sourceRange.getRow(row).getValues()[0][0].toString().split(/[\t,]/) destinationRange.getOffsetRange(row, 0).getResizedRange(0, sourceRangeValues.length - 1).setValues([sourceRangeValues]); } // Text to columns on range H1:H76 on selectedSheet for (let row = 0; row < selectedSheet.getRange("H1:H" + lastRow).getRowCount(); row++) { let sourceRange = selectedSheet.getRange("H1:H" + lastRow); let destinationRange = selectedSheet.getRange("H1"); let sourceRangeValues = sourceRange.getRow(row).getValues()[0][0].toString().split(/[\t,]/) destinationRange.getOffsetRange(row, 0).getResizedRange(0, sourceRangeValues.length - 1).setValues([sourceRangeValues]); } // Set range K1 on selectedSheet selectedSheet.getRange("K1").setValue("Country"); }

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

Like (1)

Report
Try the following script:
function main(workbook: ExcelScript.Workbook) { let selectedSheet = workbook.getActiveWorksheet(); // Delete range E:F on selectedSheet selectedSheet.getRange("E:F").delete(ExcelScript.DeleteShiftDirection.left); // Auto fit the columns of all cells on selectedSheet selectedSheet.getRange().getFormat().autofitColumns(); // Insert at range B:B on selectedSheet, move existing cells right selectedSheet.getRange("B:B").insert(ExcelScript.InsertShiftDirection.right); // Set range B1 on selectedSheet selectedSheet.getRange("B1").setValue("Undeliverable Date"); // Insert at range D:D on selectedSheet, move existing cells right let columnToInsert = selectedSheet.getRange("D:D"); columnToInsert.insert(ExcelScript.InsertShiftDirection.right); columnToInsert.insert(ExcelScript.InsertShiftDirection.right); columnToInsert.insert(ExcelScript.InsertShiftDirection.right); // Text to columns on range C1:C33 on selectedSheet let lastRow = (selectedSheet.getUsedRange().getLastRow().getRowIndex() + 1).toString(); let sourceRange = selectedSheet.getRange("C1:C" + lastRow); let sourceRangeRowCount = sourceRange.getRowCount(); let destinationRange = selectedSheet.getRange("D1"); for (let row = 0; row < sourceRangeRowCount; row++) { let sourceRangeValuesArray = sourceRange.getRow(row).getValues()[0][0].toString().split(/[\t ]/) let sourceRangeValues = [sourceRangeValuesArray[0], sourceRangeValuesArray[1], sourceRangeValuesArray.slice(2, sourceRangeValuesArray.length).join(" ")]; destinationRange.getOffsetRange(row, 0).getResizedRange(0, sourceRangeValues.length - 1).setValues([sourceRangeValues]); } // Set range C1:F1 on selectedSheet selectedSheet.getRange("C1:F1").setValues([["Full Name", "First Name", "Middle Name", "Last Name"]]); // Text to columns on range K1:K33 on selectedSheet sourceRange = selectedSheet.getRange("K1:K" + lastRow); sourceRangeRowCount = sourceRange.getRowCount(); destinationRange = selectedSheet.getRange("L1"); for (let row = 0; row < sourceRangeRowCount; row++) { let sourceRangeValues = sourceRange.getRow(row).getValues()[0][0].toString().split(/[\t,]/) destinationRange.getOffsetRange(row, 0).getResizedRange(0, sourceRangeValues.length - 1).setValues([sourceRangeValues]); } // Text to columns on range H1:H76 on selectedSheet sourceRange = selectedSheet.getRange("H1:H" + lastRow); sourceRangeRowCount = sourceRange.getRowCount(); destinationRange = selectedSheet.getRange("H1"); for (let row = 0; row < sourceRangeRowCount; row++) { let sourceRangeValues = sourceRange.getRow(row).getValues()[0][0].toString().split(/[\t,]/) destinationRange.getOffsetRange(row, 0).getResizedRange(0, sourceRangeValues.length - 1).setValues([sourceRangeValues]); } // Set range K1 on selectedSheet selectedSheet.getRange("K1").setValue("Country"); }
The main thing I tried to do was reduce the number of calls to getRange. For example, in your original script, you define sourceRange and destinationRange within your for loops, but those variables don't actually change over the course of the loop, so moving those definitions outside should improve performance.

Let me know if that helps, or if you have any questions!

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

Like (1)

Report

One thing I forgot: when running a script through Power Automate, it's best to avoid using getActiveWorksheet since it's not always clear which worksheet is "active" if the flow runs while Excel isn't open. Instead, you should get a specific worksheet by name (potentially passing in the worksheet name as a parameter).

Was this reply helpful? Yes No
Kuromi102 14 on at

Like (0)

Report

Thank you very much for your help! I really appreciate your fixed script! It looks really clean now!!
However, I've tried with the flow but unfortunately it doesn't improve the consumed time. It has been an hour but still running. I wonder if there is any other way to fix this issue?

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

Like (0)

Report

Could you share a screenshot of an execution from the flow's run history? I'm wondering whether the script ever runs successfully, or if the flow is just taking a long time because there are a lot of files to run the script on.

Was this reply helpful? Yes No
Kuromi102 14 on at

Like (0)

Report

Thank you and sure, I can share!
This is the flow run history. As you see, I canceled all the time because it took too long. The "test failed" and "failed" status were when I tried to make the Concurrency Control on with 20 degree of parallelism. Please let me know if you had any idea or solution! thank you very much!

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

Like (0)

Report

Could you select a specific run (from the timestamp) and share a screenshot of that? That should show which actions were executed and how long each one took.

Was this reply helpful? Yes No
Kuromi102 14 on at

Like (0)

Report

Yes, so it looks like it always stuck at "Apply to each". That's why I wonder if there is any better solution to replace "Apply to each" function.

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

Like (0)

Report

Can you expand "apply to each" to see how many times the script runs?

Unfortunately, I don't think there's a way to avoid using apply to each because a script can only run on a single workbook at a time.

Was this reply helpful? Yes No