Power Platform Community Forum Thread Details

Hello,

I have an excel office script that is running via PowerAutomate. The trigger for this is when a new email arrives and it loops through a number of conditions to pull out the respective document from the email, filter items from SharePoint to pass dynamic variables to the Office Script based on the filtered criteria, and then conduct the office script and send an email with the contained file post the script run. With that being said, the script works well with smaller batches of data but some items have more than 1000 line items which is causing a gateway timeout as the script takes longer than 120 seconds to audit the data.

The office script that I currently have is as follows and my question is how can I streamline this to meet the goals so it does not hit a gateway timeout for larger item sets?

function main(workbook: ExcelScript.Workbook, referenceValueD: string, referenceValueE: string, referenceValueQ: string) {
// Get the current active worksheet
const currentSheet = workbook.getActiveWorksheet();
// Ensure the active worksheet exists
if (!currentSheet) {
console.log("No active worksheet found.");
return;
}
// Get the used range of the worksheet
const usedRange = currentSheet.getUsedRange();
// Check if usedRange is defined
if (!usedRange) {
console.log("No data found in the worksheet.");
return;
}
// Get the row and column count
const rowCount = usedRange.getRowCount();
const columnCount = usedRange.getColumnCount();
// Define the column indices for validation (adjusted for zero-based indexing)
const VALIDATION_COLUMN_S = 18; // Column S
const VALIDATION_COLUMN_J = 9; // Column J
const VALIDATION_COLUMN_I = 8; // Column I
const VALIDATION_COLUMN_Q = 16; // Column Q
const VALIDATION_COLUMN_D = 3; // Column D
const VALIDATION_COLUMN_E = 4; // Column E
// Create or get the "Error Capture" sheet
let errorSheet = workbook.getWorksheet("Error Capture") || workbook.addWorksheet("Error Capture");
// Copy headers to the "Error Capture" sheet
const headers = usedRange.getRow(0).getValues(); // Get the first row (headers)
errorSheet.getRangeByIndexes(0, 0, 1, columnCount).setValues(headers);
// Initialize row index for the error sheet
let errorRowIndex = 1;
// Loop through the rows in the used range (skip header row)
for (let i = 1; i < rowCount; i++) {
const row = usedRange.getRow(i).getValues()[0]; // Get the current row

// Trim spaces and ensure correct data types before comparing
const columnDValue = String(row[VALIDATION_COLUMN_D]).trim();
const columnEValue = String(row[VALIDATION_COLUMN_E]).trim();
const columnQValue = String(row[VALIDATION_COLUMN_Q]).trim();

// Logging for debugging purposes
console.log(`Row ${i}: Column D Value = '${columnDValue}', Reference D = '${referenceValueD}'`);
console.log(`Row ${i}: Column E Value = '${columnEValue}', Reference E = '${referenceValueE}'`);
console.log(`Row ${i}: Column Q Value = '${columnQValue}', Reference Q = '${referenceValueQ}'`);

// Check the value in column S for "Error"
const isErrorInColumnS = row[VALIDATION_COLUMN_S] === "Error";
// Check the value in column J for greater than 40
const isValueGreaterThan40InColumnJ = row[VALIDATION_COLUMN_J] > 40;
// Check the value in column I for greater than 275
const isValueGreaterThan275InColumnI = row[VALIDATION_COLUMN_I] > 275;
// Check if the value in column Q is different from the passed referenceValueQ
const isValueDifferentInColumnQ = columnQValue !== referenceValueQ;
// Check if the value in column D is different from both the passed reference values
const isValueDifferentInColumnD = columnDValue !== referenceValueD ;
// Check if the value in column E is different from both the passed reference values
const isValueDifferentInColumnE = columnEValue !== referenceValueE ;

// If any condition is true, move the row to the Error Capture sheet
if (
isErrorInColumnS ||
isValueGreaterThan40InColumnJ ||
isValueGreaterThan275InColumnI ||
isValueDifferentInColumnQ ||
isValueDifferentInColumnD ||
isValueDifferentInColumnE
) {
// Move the row to the Error Capture sheet
errorSheet.getRangeByIndexes(errorRowIndex, 0, 1, columnCount).setValues([row]);
// Increment the error sheet row index
errorRowIndex++;
}
}
console.log(
`Rows with "Error" in column S, values greater than 40 in column J, values greater than 275 in column I, values in column Q different from the provided reference value, or values in columns D/E different from the provided reference values have been moved to the 'Error Capture' sheet.`
);
}

Categories:

Building flows

Hi @AP-23041346-0,

It's hard to determine which specific step from your Script is taking longer to run, but I would guess it's probably the For loop. In a quick look, it seems that you cannot get rid from your loop, but we can maybe remove a few steps from there, which can save your Script some time.

You're basically capturing the qty of rows from your occupied range, looping it until you get to the total count, and for each index accessing the respective row value. Instead, I would recommend you to get the values from all rows from your worksheet, and then loop through it to execute your validations.

As example, I took an oversimplified scenario to test this approach, where I'm just accessing the values from 693 entire rows and using the map array method to loop through it and print its values in the console. The whole code is highlighted in red. This approach takes around 4 seconds to iterate all rows (in blue):

Then I did the same thing again, but now using a for loop and accessing each row value individually (in red), just like you did. This is the result:

I don't think you won't get a 90%+ improvement in your run time as I got with this veeeery simplified scenario, but it still worth the shot :)

Let me know if it works for you or if you need any additional help!

✅ If this solved your issue, please mark it as Accepted Answer.
👍 If it helped, feel free to give it a like!

🌐 Explore more Power Platform content on my Website or on my ▶️ YouTube
💼 Find me on LinkedIn