Yes and no. If you use the modern 4 person adventuring party, one is probably going to be useless except for that one fight where he uses sleep.
But back in the day if you had 6 to 12 people in the party, he could hide in the back throwing darts or whatnot until sleep was needed.
Agree in spades. Then add light sources. Sure, that 12 person party probably has the "link boy" to handle that, but as the hired help goes down, someone has to hold a torch or lantern. Is it the thief trying to sneak, the fighter with sword & board or a 2-handed weapon or a ranger with a bow? Nope. Sure don't want your cleric to do it when he could be using a shield and staying up. Heck, that's even true when you have a 5-person party, which was more typical of the "small" group. Hand-wave all the light source stuff, and that all goes out the window.
Then as mentioned, there's the wand of magic missiles. But you don't even need to go that far. The GM can just use the treasure tables, where the majority of early magic is going to be magic arrows, potions, and scrolls--most of the latter being wizard scrolls. By third or fourth level, the wizard has not only picked up a few more spells, he's also likely got several consumable things that might come in really handy.
However, I think the biggest roadblock to that kind of play is not embracing the style--whether because can't or won't doesn't really matter. It can suck to be the wizard until about 5th level in that kind of game. OTOH, giving a few hired NPC body guards to keep him out of the front line and playing smart, the wizard player gets a little dopamine hit every time they get a new spell, wand, or scroll. What really sets it apart is the playing smart part, though. If you stay at levels 1-3 perpetually, because the party keeps getting wiped out, then there's not enough payoff.
It's been very eye-opening for some of my moderately experienced but younger players in one of my new groups. Even with my system allowing multiple charges of spells, having random cantrips to lead off is not what they are used to in a caster. (Also, not being able to heal certain kinds of damage easily.) Just last session I had a lightly-armored healer volunteer to hold the torch in a desperate running night fight, and he was reveling in it. His decisions probably made the difference in the party not losing anyone. And he knew it. His announced reasoning was the was position in the middle so that everyone could see, he had a hand free, and the torch was a beacon for anyone hurt to make their way to him (or be dragged by someone else or for him to see to go to them). That's in a party of 10 players, no one able to see in the dark. He never even tried to attack, but did use tactical movement to stay out of melee until the last few rounds, where upon a couple of other PC's immediately converged on him to get him out of melee again.
Comment after the game, immediately echoed by several other players: "This is so much more tactically rich than 5E". None of them had ever played AD&D before.