I suspect that the broad glamourisation of drugs in music is down to journalists making things more fabulous than they are. "Ooh look at then being naughty, they are getting away with it and are epically cool. I wish that was me".
Ask anyone who has experience of heroin (other drugs are available) addiction either personally or within their family, and I can guarantee they will not be saying "yeah, but the songs made it all worth it". Have some people produced interesting stuff while using drugs? Yes. Would they produce interesting stuff anyway? More than likely, this was just a different flavour of whatever would be. We only read about the tiny percentage of people who have "done the thing" and " produce the thing". The rest of the people who have "done the thing" and produced "not the thing" are not eulogised by those who write about it - the tastemakers/gatekeepers.