Office Script Timeout(s)

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.`
  );
}