I am probably on the spectrum and I am almost definitely ADHD - Hyperactive.
It is quite normal, on the spectrum or not to have different levels of social tolerance - this is where - as you probably know - introversion and extroversion come into play - and despite popular usage, this doesn't mean an introvert avoids or can't be in social circumstances.
Many of my friends/acquaintances see me as a social butterfly/quite extroverted - and I do have high openness etc but I have a very limited capacity in terms of social battery for quite a lot of 'normal' situations. This is exactly why I have avoided retail and hospitality work and gone for factory work all my life because the anxiety/energy drain of the performative function of those roles is too much for me, and I would probably end up also having issues in those workplaces as you've mentioned. Having worked now broadly with technical types - it is a similar thing for most of them - they are just some dudes in a basement programming because that's what they can 'cope' with.
In environments where I am simply a 'cog' in a machine or part of a process (factory and tech) I function better because I just have to do the work and the social/human component is cooperating with one another to achieve X goal.
Then it's up to HR and management to build the teams so they work well together and for the most part, I have been 'blessed'/now I am experienced enough as an individual to know what to ask or look for in the interview process to know if I am going to want to be a part of that team.
So I would not be hard on yourself in any way for this.
One-on-one with people I vibe with I have an almost unlimited social battery - but usually it has to be active in some way so I like hiking, or walking with people. In small groups, I am okay too if it is with again people I know and get along well with.
I can't do big groups - I can't do being around people who aren't on my wavelength for extended periods. The closest I've come to this is the local group I hang out with once a month at a cafe for 3-5 hours. That will be it for most social interactions for the weekend/week though if I do this - and even then I only manage so well because they are lovely bunch and they find me hysterical/funny - love how self-effacing I am. So getting to be the clown of the group elevates some of the pressure for me.