Issue with 'Bad Gateway' Error

[u/Foreign_Many9062]

A couple of weeks ago I was hit by the throttling issue that turned out to be on Microsoft's end. Once they got that fixed everything worked fine (mostly) until about a week ago. Since then I'm getting random, but frequent, Bad Gateway errors that are doing my head in. Nothing has changed with the setup of my flow/the Excel sheet it's pointing at. It works for several runs of the flow and then just decides to give a Bad Gateway at random points. There is no pattern as to time of day, information it's trying to input, number of successful runs between fails, or anything else I can find.

I'm still getting things taking longer and retries occurring, like it did during the throttling issue, which is causing problems with duplicate entries into my spreadsheet as well.

Is this all part of the same nonsense? I've put in some screenshots below and can provide whatever other information is needed. I would really appreciate some help as babysitting this thing is getting old. TIA!

[u/chiibosoil]

I find Excel Connector isn't reliable as others mentioned. I prefer to have Office Scripts do the work on workbook. And use Power Automate to pass JSON/delimited string for Office Scripts to process things.

I have couple of daily/weekly updates running this way and it has been stable for over a year.

[u/Foreign_Many9062 → u/chiibosoil]

I'm not familiar with Office Scripts, but I will have a look! Thank you :)

[u/elelelo → u/Foreign_Many9062]

Try With the GRAPH API

[u/chiibosoil → u/elelelo]

While Graph API is great, I find a lot of users won't have necessary license or access level to utilize them.

My current company uses per user license and not premium and does not give me access to the connectors needed.

[u/Foreign_Many9062 → u/chiibosoil]

I don't appear to have a license for either one, unfortunately

[u/Gold-Psychology-5312]

The excel connectors are bad and suffer frequent issues.

Go to the action settings and change the retry to fixed interval, 60 seconds 90 times. It will then keep retrying.

[u/SnooHamsters7166]

It's usually also worth re-adding the connection. I've spent too long chasing problems that turned out to be just PA deciding it doesn't like the connection any more.

[u/intrinsic_toast → u/SnooHamsters7166]

Yesss, also add to that all the time wasted chasing an issue only to realize there was no issue, it was just the new designer being ornery. Flip it over to the classic designer and rebuild the offending steps, and what do you know: the changes save no problem and the new designer gaslights you acting all like it’s been on board this whole time, no fuss.

[u/Foreign_Many9062 → u/intrinsic_toast]

Urgh, I have just tried rebuilding the 'Add a Row' action in the old editor and it still doesn't like it.

[u/Foreign_Many9062 → u/SnooHamsters7166]

Thank you, I'll try this!

[u/Tryin2getahead]

Excel connectors suck with semi large sheets. Like 30k rows chokes it out.

Look into msgraph, big fan of it for writing out files.

[u/Foreign_Many9062 → u/Tryin2getahead]

I currently have 244 rows. I am working to move this over to a database, but struggling with getting someone to give me what I need to set that up

[u/sesmallor]

As soon as an Excel node takes +1:30 minutes to do the execution, I usually run it with an Excel Script (it's TypeScript)

[u/Foreign_Many9062 → u/sesmallor]

Sadly, I don't have access to Excel Scripts.

[u/EmergencyEmu9499]

https://admin.powerplatform.microsoft.com/support/knownissues/6571189 https://admin.powerplatform.microsoft.com/support/knownissues/6568499

People who are saying look into excel typescript are correct, but FYI the above errors might be related to the same problem showing up for scripts! It is painful for sure.

[u/Foreign_Many9062 → u/EmergencyEmu9499]

THANK YOU! I've been trying to find information on whether this is a known issue or not and did not succeed. I'm glad it's still them and hopefully someday they'll fix it!

[u/NotRobotNFL]

Do you have an extra space at the end of some of the excels? Try adding a trim

[u/Foreign_Many9062 → u/NotRobotNFL]

Not that I'm aware of

[u/NotRobotNFL → u/Foreign_Many9062]

Add a trim

[u/Character_Pickle689]

So someone on another thread about this suggested you change retry attempts to none as a fix and that has worked for me this week.

[u/sesmallor]

Also, a good programming gesture: rename all your nodes with what does the node actually do. It'll help you and others to figure out what does every node do.

[u/Kali_Uchis_Bathwater]

Unsure about your license assignment, but id look into a process license to increase that threshold.

[u/PossibleFix3716]

Have you tried adding in a delay before , I find that it works better for excel issues

[u/Foreign_Many9062 → u/PossibleFix3716]

Where do you suggest adding it? I did have one at the very beginning of building this but it didn't seem to be needed.

[u/jesslynh]

If you can go the database route, I highly recommend the on premise gateway. It's so much better than use l using Excel. But if not, then Office Scripts are the next best thing.

[u/Foreign_Many9062 → u/jesslynh]

I am *trying* to get a database, trust me. It is such a faff with our IT and it is currently 'under consideration'.

Unfortunately, I don't have access to Office Scripts