Is there a way to bypass Excel's 2 minute Script runtime limit?

Question

I'm currently facing a challenge with an Excel script designed for processing a large dataset. Each time I attempt to run this script, I encounter a timeout error; "Range getCell: Timeout", see picture attached: Timeout Error. This issue comes from the 2-minute execution limit for Excel scripts, which my script obviously exceeds since its a large dataset.

I've tried optimizing the script in many ways:

• Making it only run for specific sections, and repeating the process to cover the entire dataset. This requires a number of script executions, making my task tedious (and I mean A LOT of executions).
• Another thing I tried doing was changing the script itself to be more time efficient. But the dataset is simply way too large that this change is negligible and 2 minutes is nowhere near enough time to do what I need to do.

Given the constraints and my attempts at optimization, is there a way to bypass or extend this 2-minute limit?

Edit

Here is the script;

async function main(workbook: ExcelScript.Workbook): Promise<void> {
  let table = workbook.getActiveWorksheet().getTables()[0];
  let dataBodyRange = table.getRangeBetweenHeaderAndTotal();
  let tableValues = dataBodyRange.getValues();

  // Loop through each row of the table
  for (let i = 0; i < tableValues.length; i++) {
    // Process Dietary Restrictions
    let restrictions = tableValues[i][6] ? tableValues[i][6].toString() : "";
    processDietaryRestrictions(restrictions, dataBodyRange, i);

    // Process Hearing
    let hearing = tableValues[i][14] ? tableValues[i][14].toString() : "";
    processHearing(hearing, dataBodyRange, i);
  }

  console.log("Processed both Dietary Restrictions and Hearing columns.");
}

function processDietaryRestrictions(restrictionsString: string, dataBodyRange: ExcelScript.Range, rowIndex: number) {
  let parsedRestrictions = restrictionsString
    .replace(/^\["/, '')
    .replace(/"\]$/, '')
    .split('","')
    .map(s => s.trim())
    .map(s => s.replace(/^ \t/, ''));

  let dairyValue = parsedRestrictions.some(r => r.includes("Dairy") || r.includes("Produits laitiers")) ? "✔" : "";
  let peanutsValue = parsedRestrictions.some(r => r.includes("Peanuts") || r.includes("Cacahuètes")) ? "✔" : "";
  let eggsValue = parsedRestrictions.some(r => r.includes("Eggs") || r.includes("Œufs")) ? "✔" : "";
  let glutenValue = parsedRestrictions.some(r => r.includes("Gluten")) ? "✔" : "";
  let treeNutsValue = parsedRestrictions.some(r => r.includes("Tree nuts") || r.includes("Fruits à coque")) ? "✔" : "";
  let soyProductsValue = parsedRestrictions.some(r => r.includes("Soy products") || r.includes("Produits à base de soja")) ? "✔" : "";

  // For 'Other', find any entry that doesn't match known categories
  let otherEntries = parsedRestrictions.filter(r =>
    !r.includes("Dairy") && !r.includes("Produits laitiers") &&
    !r.includes("Peanuts") && !r.includes("Cacahuètes") &&
    !r.includes("Eggs") && !r.includes("Œufs") &&
    !r.includes("Gluten") &&
    !r.includes("Tree nuts") && !r.includes("Fruits à coque") &&
    !r.includes("Soy products") && !r.includes("Produits à base de soja")
  ).join(", ");
  let otherValue = otherEntries ? otherEntries : "";

  dataBodyRange.getCell(rowIndex, 7).setValues([[dairyValue]]);
  dataBodyRange.getCell(rowIndex, 8).setValues([[peanutsValue]]);
  dataBodyRange.getCell(rowIndex, 9).setValues([[eggsValue]]);
  dataBodyRange.getCell(rowIndex, 10).setValues([[glutenValue]]);
  dataBodyRange.getCell(rowIndex, 11).setValues([[treeNutsValue]]);
  dataBodyRange.getCell(rowIndex, 12).setValues([[soyProductsValue]]);
  dataBodyRange.getCell(rowIndex, 13).setValues([[otherValue]]);
}


function processHearing(hearingString: string, dataBodyRange: ExcelScript.Range, rowIndex: number) {
  if (hearingString.startsWith('[') && hearingString.endsWith(']')) {
    let cleanedHearingString = hearingString.replace(/'/g, '"');
    let parsedResponses: string[] = JSON.parse(cleanedHearingString);

    let ciponetValue = parsedResponses.includes("CIPOnet") || parsedResponses.includes("OPICnet") ? "✔" : "";
    let cipoinfoValue = parsedResponses.includes("CIPOinfo") || parsedResponses.includes("OPICinfo") ? "✔" : "";
    let colleagueValue = parsedResponses.includes("Colleague") ? "✔" : "";
    let managerValue = parsedResponses.includes("Manager") ? "✔" : "";
    let directorValue = parsedResponses.includes("Director") ? "✔" : "";
    let interConnexValue = parsedResponses.includes("InterConnex") ? "✔" : "";
    let cipoconnexValue = parsedResponses.includes("CIPOConnex") || parsedResponses.includes("OPICConnex") ? "✔" : "";

    // For 'Other2', find any entry that doesn't match known categories
    let otherEntries = parsedResponses.filter(r =>
      !r.includes("CIPOnet") && !r.includes("OPICnet") &&
      !r.includes("CIPOinfo") && !r.includes("OPICinfo") &&
      !r.includes("InterConnex") &&
      !r.includes("Colleague") &&
      !r.includes("Manager") &&
      !r.includes("Director") &&
      !r.includes("CIPOConnex") && !r.includes("OPICConnex")
    ).join(", ");
    let other2Value = otherEntries ? otherEntries : "";

    dataBodyRange.getCell(rowIndex, 15).setValues([[ciponetValue]]);
    dataBodyRange.getCell(rowIndex, 16).setValues([[cipoinfoValue]]);
    dataBodyRange.getCell(rowIndex, 17).setValues([[interConnexValue]]);
    dataBodyRange.getCell(rowIndex, 18).setValues([[cipoconnexValue]]);
    dataBodyRange.getCell(rowIndex, 19).setValues([[colleagueValue]]);
    dataBodyRange.getCell(rowIndex, 20).setValues([[managerValue]]);
    dataBodyRange.getCell(rowIndex, 21).setValues([[directorValue]]);
    dataBodyRange.getCell(rowIndex, 22).setValues([[other2Value]]);
  } else {
    console.log(`Row ${rowIndex + 1}: Invalid hearing string format`);
  }
}

The script takes the input from Dietary Restriction column and Hearing column and places check marks in the corresponding answer columns.

Example output: Hearing Column

Answer 1

I don't think there's any way to overcome the two minute limit.

That being the case, I have two suggestions for you.

Firstly, if you find that the script is taking too long to run AND you're running it from PowerAutomate/LogicApps, I suggest chunking up the calls via a loop in PowerAutomate to only process a portion of the rows at once, i.e. 1 to 500, 501 to 1000, 1001 to 1500, etc. It's a pain but it's a pattern that should work for you consistently. You'd need to factor in the 1600 calls per day limit though.

Secondly, in relation to your script specifically, you could change blocks like this ...

dataBodyRange.getCell(rowIndex, 15).setValues([[ciponetValue]]);
dataBodyRange.getCell(rowIndex, 16).setValues([[cipoinfoValue]]);
dataBodyRange.getCell(rowIndex, 17).setValues([[interConnexValue]]);
dataBodyRange.getCell(rowIndex, 18).setValues([[cipoconnexValue]]);
dataBodyRange.getCell(rowIndex, 19).setValues([[colleagueValue]]);
dataBodyRange.getCell(rowIndex, 20).setValues([[managerValue]]);
dataBodyRange.getCell(rowIndex, 21).setValues([[directorValue]]);
dataBodyRange.getCell(rowIndex, 22).setValues([[other2Value]]);

... to be something like this ...

dataBodyRange.getRow(rowIndex).getUsedRange().setValues(
  [
    [null],
    [null],
    [null],
    [null],
    [null],
    [null],
    [null],
    [null],
    [null],
    [null],
    [null],
    [null],
    [null],
    [null],
    [ciponetValue], 
    [cipoinfoValue], 
    [interConnexValue], 
    [cipoconnexValue], 
    [colleagueValue], 
    [managerValue], 
    [directorValue], 
    [other2Value]
  ]
);

... and then reason for that is because calls back and forth to the worksheet/workbook use up large amounts of time.

When writing back, using null will make the value in the cell remain as is. Given you only want to effect columns 15 to 22, you'd need to do something like that unless you break down the range to do that, which is also possible and would look something like this ...

let updateRange = workbook.getActiveWorksheet().getRange(
  dataBodyRange.getCell(rowIndex, 15).getAddress() + ":" + 
  dataBodyRange.getCell(rowIndex, 22).getAddress());

updateRange.setValues([
  [ciponetValue],
  [cipoinfoValue],
  [interConnexValue],
  [cipoconnexValue],
  [colleagueValue],
  [managerValue],
  [directorValue],
  [other2Value]
]);

There are a few ways to skin this cat, but, the main concern is to reduce the amount of calls being made to the worksheet.