Skip to main content

Notifications

Announcements

🌸 Community Spring Festival 2025 Challenge & Updates🌸
Replay: Power Platform Community AMA - Copilot Studio | April
Welcome to the Power Platform Communities
News and Announcements icon
Community site session details

Community site session details

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

Bad Gateway 504 Timed out

(0) ShareShare
ReportReport
Posted on 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 at
    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

🌸 Community Spring Festival 2025 Challenge & Updates🌸
Replay: Power Platform Community AMA - Copilot Studio | April
Welcome to the Power Platform Communities

Quick Links

🌸 Community Spring Festival 2025 Challenge 🌸

WIN Power Platform Community Conference 2025 tickets!

Markus Franz – Community Spotlight

We are honored to recognize Markus Franz as our April 2025 Community…

Kudos to the March Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 146,524 Most Valuable Professional

#2
RandyHayes Profile Picture

RandyHayes 76,287 Super User 2024 Season 1

#3
Pstork1 Profile Picture

Pstork1 65,906 Most Valuable Professional

Leaderboard

Featured topics

Restore a deleted flow

Product updates

Microsoft Power Platform Community release plans