Power Platform Community Forum Thread Details

Hello everyone. I'm running a very simple script, that basically updates a table with values that I get from Planner. My issue is that form Planner I download the Ids, and then I run a script to convert those Ids into names and then send a message to users on teams. Issue is that I'm getting an error at that point every single time. I've seen some people with a similar issue, apparently it has to do with taking longer than 120s, however when I run the script on my pc it doesn't even take 10 seconds to run. How can I fix this?

```

{

"error": {

"code": 504,

"source": "flow-apim-europe-002-westeurope-01.azure-apim.net",

"clientRequestId": "249aa6f7-effd-4b2e-9130-84450708ed01",

"message": "BadGateway",

"innerError": {

"status": 504,

"message": "Your Office Script has timed out. Please try again.\r\nclientRequestId: 249aa6f7-effd-4b2e-9130-84450708ed01",

"error": {

"message": "Your Office Script has timed out. Please try again."

"source": "excelonline-ne.azconn-ne-003.p.azurewebsites.net"

}

```

And here is the script:

```

function main(workbook: ExcelScript.Workbook) {

let table = workbook.getWorksheet("Datos").getTables()[0]

let rows = table.getRangeBetweenHeaderAndTotal().getRowCount() + 1;

let lookupUserRange = workbook.getWorksheet("Ids").getTable("UserIds").getRange();

let lookupBucketRange = workbook.getWorksheet("Ids").getTable("BucketsIds").getRange();

let inputAssignedRange = table.getColumn(4);

let inputCreatorRange = table.getColumn(3);

let inputBucketRange = table.getColumn(2);

let inputAssignedValues: string[] = inputAssignedRange.getRange().getValues().map(cell => cell.toString());

let inputCreatorValues: string[] = inputCreatorRange.getRange().getValues().map(cell => cell.toString());

let inputBucketValues: string[] = inputBucketRange.getRange().getValues().map(cell => cell.toString());

for (let k = 1; k < rows; k++) {

let lookupAssignedResult = performLookupLimited(inputAssignedValues[k], lookupUserRange);

let rangeToChangeAssigned = table.getRange().getCell(k,6);

rangeToChangeAssigned.setValue(lookupAssignedResult);

let lookupCreatorResult = performLookupLimited(inputCreatorValues[k], lookupUserRange);

let rangeToChangeCreator = table.getRange().getCell(k, 5);

rangeToChangeCreator.setValue(lookupCreatorResult);

let lookupBucketResult = performLookupLimited(inputBucketValues[k], lookupBucketRange);

let rangeToChangeBucket = table.getRange().getCell(k, 9);

rangeToChangeBucket.setValue(lookupBucketResult);

}

function performLookupLimited(inputString: string, lookupRangeAddress: ExcelScript.Range😞 string {

let result = '';

if (inputString == ""){

return "Nadie";

}

let values = lookupRangeAddress.getValues();

for (let i = 0; i < inputString.length; i += 36) {

const substring = inputString.substr(i, 36);

let lookupValue = lookupRangeAddress.find(substring, {completeMatch: false});

if (lookupValue !== undefined) {

let rowLookup = lookupValue.getRowIndex();

result += lookupRangeAddress.getCell(rowLookup, 1).getValue() + ', ';

}

if (result.endsWith(', ')) {

result = result.slice(0, -2);

}

return result;

}

```

Categories:

Using flows

Hi @njnunes - sorry to hear you're running into this issue! I believe that scripts often take a bit longer to run through Power Automate than they would within Excel, so we can probably start by trying to optimize the code. At first glance, I'd suggest the following:

Instead of passing an entire Range object into your performLookupLimited function, pass in only the values. That way, you only need to call getValues once per lookup range.
Instead of calling table.getRange() multiple times within the for loop, save the table range in a variable outside the loop.

So the updated script would look like this:

function main(workbook: ExcelScript.Workbook) {

 let table = workbook.getWorksheet("Datos").getTables()[0]
 let rows = table.getRangeBetweenHeaderAndTotal().getRowCount() + 1;
 let lookupUserRange = workbook.getWorksheet("Ids").getTable("UserIds").getRange().getValues();
 let lookupBucketRange = workbook.getWorksheet("Ids").getTable("BucketsIds").getRange().getValues();
 let inputAssignedRange = table.getColumn(4);
 let inputCreatorRange = table.getColumn(3);
 let inputBucketRange = table.getColumn(2);


 let inputAssignedValues: string[] = inputAssignedRange.getRange().getValues().map(cell => cell.toString());
 let inputCreatorValues: string[] = inputCreatorRange.getRange().getValues().map(cell => cell.toString());
 let inputBucketValues: string[] = inputBucketRange.getRange().getValues().map(cell => cell.toString());

 let tableRange = table.getRange();

 for (let k = 1; k < rows; k++) {

 let lookupAssignedResult = performLookupLimited(inputAssignedValues[k], lookupUserRange);
 let rangeToChangeAssigned = tableRange.getCell(k, 6);
 rangeToChangeAssigned.setValue(lookupAssignedResult);


 let lookupCreatorResult = performLookupLimited(inputCreatorValues[k], lookupUserRange);
 let rangeToChangeCreator = tableRange.getCell(k, 5);
 rangeToChangeCreator.setValue(lookupCreatorResult);


 let lookupBucketResult = performLookupLimited(inputBucketValues[k], lookupBucketRange);
 let rangeToChangeBucket = tableRange.getCell(k, 9);
 rangeToChangeBucket.setValue(lookupBucketResult);


 }



 function performLookupLimited(inputString: string, values: (string | number | boolean)[][]): string {

 let result = '';


 if (inputString == "") {
 return "Nadie";
 }

 for (let i = 0; i < inputString.length; i += 36) {

 const substring = inputString.substr(i, 36);

 // Find the row index of the cell that contains the substring
 let rowLookup = values.findIndex(row => row.findIndex(value => value.toString().indexOf(substring) >= 0) >= 0);

 if (rowLookup >= 0) {
 result += values[rowLookup][1] + ', ';
 }
 }



 if (result.endsWith(', ')) {
 result = result.slice(0, -2);
 }


 return result;

 }
}

Let me know if that helps? If you're still running into timeout issues, would you be able to share a sample workbook and your entire flow configuration (with sensitive data removed as needed) so that we can troubleshoot in more detail?