I've always liked this method too. It is fun for the character to develop their spells based on the context of the story and what they are exploring. But, I have also had players explain it like this: The mysteries of magic unfold themselves in the unlikeliest of circumstances. So it may be in the middle of a battle, or in the middle of casting another spell, or in the middle of the night that they have a revelation on how to tweak one spell and make it another or are inspired on how to cast something different. And, who am I (other than the DM) to say no. Especially in a setting like D&D, where magic is sooooooo prevalent.
I have found a unique compromise. I make new spells and have them find the scroll or book. This way it relates to the setting and the context of the story. So these get added to their spellbook, but they are not necessarily ones they have to take.
That sounds challenging. You must be doing a good job as a DM to balance between the two. Can I ask a question? Are the players vocal about what the wizard should take or do they just try to persuade? I imagine if I was playing a harsh and gritty campaign, I'd want PCs that were useful too.
Usually it’s one or two players who are vocal about it. “You should take X or Y“, based on what is going on in the campaign, what they have already encountered, etc. The reaction of the Wizard usually depends on how experienced they are with the class - sometimes they’ll be like “okay, I’ll take fireball.” Rather than Tiny Servant or something. If the Wizard knows what they want to do, or is going for theme or something, they’ll push back and say “It’s my character, I’ll pick what I want.” Which I fully endorse.
Ultimately, I’m doing the best I can, and its easier as I get older (playing with the same group for 40+ years, cause I pretty much know all about how they’re going to approach something), but it still is tough when the optimiser(s) start to skew the game - which I had in 5e, and have a lot less of in OSE.
Regarding your comment about tweaking spells and inspiration, I could subscribe to that if the magic system used something like the old Ad&d “Path Magic” system from Dragon Mag 216. In it, you selected “Paths” which followed one from the other. Like the Lesser Fire Magic path where you started with fire burst, then could learn flaming sphere, then fireball, and so on. That, to me, would make “naturally” learning spells make more logical sense (as much as magic in a game is logical
For spells, though, I wonder if DDB has data on what spells at what levels make character sheets. They do it for species, class, subclass, etc. That might be interesting to see if there is anything that is “never” taken. Those wold be the ones to target to drop, or add into supplementary material.