I think what a lot of people tend to miss with this discussion is that TDD is Test-Driven development. It doesn't have to be unit tests. The point is that you think first about what a valid specification is and you write a test for it.

There's an anecdote from Djikstra that I'll paraphrase:

Djikstra was working on a problem where two programs running on a shared memory computer were not allowed to enter their critical sections at the same time. He tasked his graduate students with finding the algorithm that would guarantee this. A student would provide a program. Djkistra would review it and find that it contained errors. The student would take the feedback and produce a longer, more complicated program.

Tired and unable to find more time to review the increasingly complicated programs, Djikstra tasked his students to submit with their program a proof of its correctness. Djikstra then need only verify that he understood the proof and that the program implemented it faithfully.

Shifting the burden of proof from the reviewer to the author made Djikstra's work much easier and the programs more robust.

I'm not suggesting we need to start writing proofs. We're practical industry programmers who aren't working with such high-assurance software most of the time. However unit tests, weak as they are, are at least a form of proof. Proof by example. For trivial code where a few examples would suffice to convince you of its correctness I would say they are quite useful.

Overrated though? I don't think so. They're also useful as a design tool. The OP gives an example of testing business logic that makes a bunch of HTTP API calls. The author claims unit tests are of little value here.

Well how would you test that?

Me, I would defunctionalize the calls the execute the HTTP requests. I'd provide an interpreter that the user can run their program in. The production code could use an algebra that makes the HTTP requests. The test code could use an algebra that stores requests made and returns canned responses.

It might seem like "extra code," but it gives us the ability to decouple our logic from how its executed. Not only can we test this without having to mock our HTTP library (which is, itself quite well tested) but this decoupling opens new avenues for managing our program. We can imagine affixing to our algebras some logging actions. We could write a development version of our interpreter algebra that logs out everything and a production version which masks secrets and sensitive information from the logs.

I've met programmers who can "just write the code," and they manage well for themselves. However I've also worked with such programmers who don't. The former are quite rare. And working with either group is difficult to say the least. If I am reviewing a piece of code I need to check whether your thinking is sound: did you consider the essential properties, edge cases, and did you spend time proving that you've thought about them? I don't want to read 600 lines of code and try to understand it... it's too much. But a proof, even an incomplete hand-waving one, I can understand.

That being said... sorry for the wall of text. Unit tests aren't the be-all-and-end-all of testing. They're a beginning. And they can be a useful tool when you're starting out. Look to property based tests. Think about the tests before you write the code. And run the tests frequently and often.

And refactor, refactor, refactor.