So I really dislike the spell myself, for similar reasons to what others have said. I'll go ahead and explain my first (and so far, only) encounter with it.

I played for the first time just under a year ago. It was an adventurer's league game at a local game shop, though run as a standard campaign rather than through AL rules. I started at level 5, and we hit level 6 at the end of my first session. By the time of this story, we'd hit level 7. This was what I would consider my first "major" combat encounter.

I wanted to play a really good healer. My first character was a life cleric. Nowhere near optimized, focused primarily on healing. Heck by this point I hadn't even understood the concept of bonus actions well, so I hadn't prepped healing word or mass healing word. Ironically, I also had dropped my cleric into a party that had two other clerics, but that meant the others could focus on some more damaging spells. In theory.

The fight was, well, nasty. We triggered it in a terrible positioning, and only made it worse for ourselves going in. I'd also blown a few spell slots on an earlier encounter, but I still had a 3rd level and my 4th level slot left. We had two melee types with a cleric behind each healing them, and me attempting to get or keep the rest of the (large) party conscious. If I hadn't cast the spell beacon of hope off at the very beginning of combat while we were still clustered together, we'd have probably lost multiple party members and possibly even TPKed (at least one paladin with a cleric focusing just on him was knocked out once, as were all 3 I was protecting). We had to end the session in the middle of combat. I had only a few spell slots left (and no channel divinity) and thought for sure we were gonna wipe the next week.

Next week, one player who had missed the previous session returned (bringing us up to 9 players at the table IIRC). He was a ranger, and the DM had him join at the end of initiative order outside the room where the combat was taking place. Over the next 3 rounds, we got the combat mostly under control, but in the process I used my last 3 spells bringing people back to consciousness. I'd also taken a few blows myself and lost concentration, I actually had become a major target due to positioning and came close to going down myself.

Then the ranger, who had missed the previous session, cast healing spirit. On the same turn as he downed one of our foes with his two attacks. And it just stays around healing over and over, keeping or bringing people up, and switching targets while he kept attacking. I didn't know how it worked, I just knew that there existed a healing over time spell and I wanted it. I'd built my character to be "healer" after all, and he was essentially doing my job and a ton of damage on top of it.

And then when combat ended, he got to use it to heal people 5 more times. I felt super jealous of that spell. I felt totally outclassed in that moment because of just how good that spell was at healing. And here's the thing. He wasn't using the spell right. He treated it as a heal on ONE person per turn, including the turns outside of combat. 10 1d6 ticks of in combat healing, while he was free to attack twice with his bow every turn including the one he cast it, while still being able to control who he targets for heals (and reviving the unconscious) felt like it was doing everything I was and then some.

Had he been playing it optimally, and restored that 1d6 (or more) hp to EVERYONE out of combat (and multiple people in combat) with a second level spell? I probably would have considered my character completely superfluous to the group, and maybe not even come back the next week.