As many have pointed out before, Barclay's social awkwardness and timidness was a nice change of pace from the usual Trek norm. His fear of the transporter would be a healthy one today, and not unfounded since folks get fried, aged, infected, cloned, thrown into a parallel universe, etc. every other year, and that's already after trying to stomach the idea that you're destroyed and reconstituted routinely. That he didn't get along with the cast made him endearing -- the rest of them were near perfect and rarely frustrated with each other, but we've all had superiors frustrated with us at work.
Also, that our heroes didn't quite know what to do with him aside from the fact they were all on the same side was really one of the few points of conflict, and thus story development, from within the crew itself.
And he needed therapy! He needed to work through his issues, he needed to find his self-worth and confidence, a far cry from Data who had no insecurities, the self-assured and almost cocky Riker, the experienced Picard, the powerful Worf. Finally, something for Troi to do besides state the obvious or be psychic. I so dislike the stigma in the US about needing therapy. It's medicine for a reason, after all.
We know the future isn't perfect, but the utopia of TNG gave it lip service. Here we have someone who has personal flaws, and those flaws almost always play into the story; quite different than our heroes, whose struggles are almost always external, and rarely from a struggle of responsibility or just being -- the Pegasus being a rare example, but even then that episode came many years after Barclay's debut. I'd like to think that later Trek writers learned a thing or two from Barclay. TNG gave us plenty of perfect heroes, and we got someone who was imperfect for a change, and I liked him for that.
According to Schultz himself, Roddenberry conceived Barclay as someone with truly human faults, to make him relatable, a normal human being. An average joe with a laundry list of personal problems but with a good heart nonetheless. We all try to do the right thing at work, but it's unrealistic to simply fly by with the best of our ability without screwing up here and there due to personal foible, and I think Barclay does a good job of showing that in the super-advanced 24th century.
Secondly, how can someone socially awkward be packed-in with hundreds of other people, like a sardine and expect them to function like that for years and years? He seemed substandard to me, when the rest of the crew is the best the Federation has on offer - the best fit, mentally, physically, people who were up to the challenge of the ENTERPRISE mission.
The thing is, there are so many ways to measure those attributes, and even then, how does one accurately do so, anyway? What about skills that are useful but don't fall under mental/physical fitness? How does Picard evaluate his staff? One important thing to note is that sometimes it's the right person for the right job, even if they don't hit every single ideal, because nobody's perfect but they still want - and can - contribute. And standardized testing never hits a tester's full potential, too. Indeed, Barclay episodes tend to have him playing a key role in unfolding whatever mystery of the week the crew are facing, which is bound to get some positive notices on his record, thus proving himself to his superiors.
Something to note: if only the most mentally fit were called on to serve on the Enterprise, two things would most definitely happen:
- more than a couple crew would be bound to crack under the pressure of trying to maintain that ideal. It happens in the real world US military, which seems a hell of a lot stricter than Starfleet, and that's without the deep space missions and the occasional cosmic threat
- The counselor's role narratively speaking would be drastically reduced from the expectation of having so many "hardened professionals" on board, but in reality, such a move would be horrible for crew morale and mental health
So it's not just the best and the brightest that serve on the Enterprise, but also a mix of people who are promising or at least dedicated, who have the heart to do their best. Wesley failed his first Starfleet exam, after all. Sonia Gomez had trouble starting. Taitt in Descent was full of inexperience and lack of confidence. The main characters from Lower Decks were worried about their future. People have to start somewhere. Getting the details through a fuller picture outside of test scores is precisely why jobs and advanced degree programs require cover letters and letters of personal intent beyond a mere resume or transcript.