This is likely over the top
First, note that in spite of the name, the way this is written the caster can hurl themselves, as they can be "a willing creature", so this is not limited to friends.
In summary, I think this is comparable in power to Dimension Door, with Fireball added in on top, which is too much for a level four spell. It also is clearly stronger than alternative options that transport others or transport and deal damage scaled to 4th level.
Compare to Dimension Door
The main spell that comes to mind here as a comparison is Dimension Door, likewise a 4th level spell with exactly the same radius. This has several advantages over Dimension Door:
- The caster does not need to transport themselves to transport the other creature, this is big, if you want to send someone into melee
- This scales to multiple creatures, not just one additional creature (although it costs a higher slot to transport two).
- This is not limited by the size of the target creature; most PC races are medium or small, so you could not take along a Large creature with Dimension door, while this can even catapult a Huge or Gargantuan one
It also has one downside:
- This does not work through obstacles or walls. This is the major downside, especially in dungeons; in the open, it does not detract that much
I personally think that the downside is larger than any of the upsides, but there are multiple upsides, so it is not really clear which of the two is the better spell. And Dimension Door is a top-notch spell already. This is before any damage. Without the damage, I think it could be balanced.
Compare to Fireball
Now, on top you add damage.
The second spell that comes to mind here is Fireball, with the same 20' radius blast, only now it has a 500' range instead of a 150' range. This is huge, essentially no other area damage of similar level has such a range, even Freezing Sphere or Ice Storm only have 300'. Basic Fireball would do 8d6 Fire, which monsters on average have 10% resistance to, and on average 60% fail their Dex save. This does 5d6 Thunder, which on average only 1% are resistant to, and only about 54% fail their Con save. So effectively this is only slightly weaker on the damage front than Fireball against creatures, and clearly better against Objects, where it deals 10d6 immediately, and it has a huge range.
In addition this has the big benefit of when catapuling your melee basher into a group of opponents, you kill off all the weaker ones and remove whatever pack tactics, help actions or additional attacks and opportunity attacks they could provide, leaving only the damaged boss opponents to deal with.
I think adding effectivley a free Fireball on top of Dimension Door is too much. It also economizes on preparation slots, as these are normally two must-have picks, that consume two of your precious slots, and now you can cover both needs with one.
Compare to Thunder Step and Vortex Warp
A comparison that combines transport and area damage is Thunder Step from XgtE. Cast at fourth level, this only has a range of 90 feet, only works to a spot you can see (so you nearly always would be able to throw in a straight line there), only works on the caster themselves, only can bring along one Medium creature like Dimension Door. It deals 4d10, comparable to your spell, but only in 10 foot radius around the area you leave (which is much less useful, IMO, as you cannot use it as a faux Fireball from the back rank). This would indicate that to balance this, your radius should at least be reduced to 10', and your range shoud be cut to 90 feet, and the scaling should go, and then this still would likely be more useful and flexible.
Compared to Vortex Warp from Strixhaven for tranporting others, upcast at 4th level1: this transports another creature (including unwilling, but only onto surface, as you have it -- you could use the language from there) 150 feet to an unoccupied space you can see. So this is essentially your spell minus being able to transport yourself, minus 250 foot range, minus scaling to multiple targets, minus any damage, with sight requirement, all in exchange for waiving willingness. I think it is clear that your version is overpowered in comparison. Scatter, from XgtE can do it for 5 creatures, but only 120 feet and is 6th level. In fact, either is so close in what it does at its core, if you wanted a balanced version you could just use it and rename it "Catapult Creature".
Wording
There are some wording issues:
- This does not describe that you need to see the target area. Is that intentional? (You comment yes, which makes this also stronger than most of the comparable spells, which require line of sight).
- This does not state that it damages objects. You would need to add this (it only is implied by the vulnerability).
- The spell does not clarify if damage happens multiple times if you throw multiple creatures, I would assume not, as this would clearly be even more overpowered, but this would need to be spelled out.
- There will be awkward issues with multiple creatures ending up in the same space, which is possible, as you are moving them, not they are willingly moving, but still will cause all kinds of ruling headaches.
- There is little need for concern about a flat target surface to avoid shenanigans with opponents, because as written, this requires a willing creature and charming makes this more costly both in actions and resources. If you want this, take a look at the wording in Vortex Warp.
I would recommend to either entirely remove the damage, or significantly reduce the amount and/or radius of the damage, to reduce the range, and to remove the ability to throw multiple creatures.
1 A second level spell that can be upcast to fourth, generally is preferable as you can pick it earlier and so you get more use out of it in a real campaign where you level up from first level. But the goal here is to compare their power at fourth level.