Flow takes HOURS to run

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

[MichelleRanMSFT]

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?

[Kuromi102]

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

}

[MichelleRanMSFT]

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!

[MichelleRanMSFT]

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

[Kuromi102]

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? 

[MichelleRanMSFT]

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.

[Kuromi102]

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!

[MichelleRanMSFT]

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.

[Kuromi102]

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. 

[MichelleRanMSFT]

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.

[Kuromi102]

I'm not sure why but "apply to each" can't be expanded from my end. I tried to click multiple times but it just doesn't expand. 😞

[MichelleRanMSFT]

That's strange... Is there any error? Can you expand "list files in folder" and share the full output of that action (sensitive data removed as needed)?

[Kuromi102]

For some reasons my replies disappeared even if I tried to repost. But this is the expanded "list files in folder". I appreciate your help! 

[MichelleRanMSFT]

That output seems correct. Could you try to test the following?

1. Replace the run script action, e.g., with sending an email to yourself (or no action at all), and verify that the flow successfully loops through all the files in a reasonable time
2. Create a flow that runs the script on a single workbook and verify that the script succeeds

I'm hoping that can help us narrow down the issue.