At the consumer grade/size, they are mostly good for reconnaissance and “guerilla tactics”
The former is what they have been used for by militaries for probably over a decade at this point. Take a quad rotor out of someone’s pack, send it up, see what is over the hill or around the corner. Significantly less risk than poking your head around the corner but not stealthy at all. As such, attrition is closer to soldiers breaking stuff in the field than a disposable item.
The latter is what all the popular videos out of Ukraine (… and Australian youtube) are about. Duct tape some C4 to a drone or rig up a release for a hand grenade. This has worked rather well against russia… mostly because they are so incredibly un-disciplined and un-trained.
Against anything even close to a “near peer”: supply convoys will be nowhere near as over extended because that is just idiotic tactically. At which point you have a few soft targets but mostly are going up against armor and APCs (there is a reason the US is buying a new one and it isn’t just because the military industrial complex sends really nice xmas cards). At which point you just have sentries listening for rotors or even mics hooked up to computers to listen for the frequencies. Hear a motor? Button up
Same with during combat where sending armor into a city is either post-battle oppression or suicide.
Which leaves outposts and bases. Where even just having some suspended netting goes a long way toward negating the precision and damage of those drones. And, combined with the aforementioned detection systems, greatly negates their impact.
As for why consumer grade drones as offensive weapons is mostly a “guerilla tactic” as it were? Because artillery has just as much accuracy and considerably more range. But nations around the world have been intentionally limiting the munitions going into Ukraine to prolong this war and prevent “taking a side”. Because, if the enemy is close enough that you can drop a grenade on them? They are close enough that you can fire a few mortars or call in an artillery strike.
And… I honestly expect to see further restrictions on drones following this. Most “western” countries already have rules about what size they can be. So there might not be enough profit to really justify mass production by new firms.