Leaving the world of flight decks for a moment, consider the automation used by single-handed round-the-world sailors. From Sir Francis Chichester onwards, no sailor could have managed to circumnavigate the globe without the use of automatic self-steering gear. It was that gear which made solo circumnavigation possible in the first place, for everyone needs to sleep at least occasionally. That's not to say the self steering gear performed the feat, it simply made it possible for a human operator to manage the voyage, paying full attention to navigation and weather conditions and tending to the sails. It would seem therefore that the ideal amount of automation is that which permits the operator to direct and manage the voyage or flight to a successful conclusion.
The ideal amount of automation for an aircraft then, would enable an aircraft to automatically take-off, fly en-route, avoid bad weather and other aircraft then approach and land at its destination. The pilot's function would be to direct his whole attention to overseeing the process, intervening by adjusting the auto-flight control system settings only when necessary.
We aren't far off that capability now, but I don't see how we could go further than that. I suppose that too much automation for a passenger aircraft would be that which eliminated the human pilot altogether, but that's never going to happen, if only for psychological reasons.