Skip to main content
News and Announcements icon
Community site session details

Community site session details

Session Id : MFrK01PZN7RZqZ1ietZOGZ
Power Platform Community / Forums / Power Automate - Using Flows / Bad Gateway 504 Timed out
Power Automate - Using Flows
Unanswered

Bad Gateway 504 Timed out

Like (0) ShareShare
ReportReport
Posted on 21 May 2024 16:08:54 by njnunes

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"
    }
  }
}
```
 
njnunes_0-1716307614431.png

 

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:
Business Process Flows
  • MichelleRanMSFT Profile Picture
    MichelleRanMSFT Microsoft Employee on 31 May 2024 at 21:17:12
    Re: Bad Gateway 504 Timed out

    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:

    1. 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.
    2. 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?

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Welcome to the Power Platform Communities
Announcing our 2025 Season 2 Super Users!

Quick Links

Announcing our 2025 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for…

Paul Stork – Community Spotlight

We are honored to recognize Paul Stork as our July 2025 Community…

Congratulations to the June Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Automate

#1
Tomac Profile Picture

Tomac 986 Moderator

#2
stampcoin Profile Picture

stampcoin 699 Super User 2025 Season 2

#3
Riyaz_riz11 Profile Picture

Riyaz_riz11 577 Super User 2025 Season 2

Last month Overall leaderboard

Featured topics

Restore a deleted flow

Product updates

Microsoft Power Platform Community release plans