They are indispensable when you need to build queries dynamically. Suppose you have some sort of list UI where users can query for data and filter down on rows they are interested in. They can filter on one of a dozen different columns in any combination. They can also sort on whatever column they want. There's no way you can implement this without building the SQL select statement dynamically, adding to the WHERE clause based on what the user wants to filter things. So you either start appending strings or you reach for an ORM.

I agree that often the full ORM isn't very helpful, I prefer something that's more of a light layer over SQL to enable dynamic query building and that's it.