This is something that medieval historians also struggle with, so you're definitely not alone. Let me start off by summarizing very briefly: the perception that the Pope was all-powerful and unquestionable is definitely incorrect, but at the same time, the church did end up winning this dispute with Frederick, for the most part.

First of all, the idea that the Papacy is all-powerful and unquestionable is what the church would have liked everyone to believe, but it wasn't really true. Since the 11th century the Popes had been attempting to increase their authority, not only over the church, but also in the political sphere. This started with Pope Gregory VII, so we call them the “Gregorian reforms” (or the “Hildebrandine reforms” after Gregory’s real name, Hildebrand). The reforms were intended to centralize the church around the Pope in Rome. The pope would be responsible for appointing bishops and archbishops and they would all answer to him, rather than leaving appointments up to the local secular ruler (which is called “lay investiture”). Church disputes could be settled locally but the “Supreme Court” (in a sense) was the pope, and theoretically, the Pope’s decision was final and unquestionable, at least in church matters. There were other issues as well - the church tried to clamp down on clerical marriage and buying clerical posts (simony), and bishops and archbishops were supposed to periodically meet at councils, among other things.

This worked, mostly, and Gregory’s successors were definitely helped by the crusades, which demonstrated that the church had the power and the authority to organize international military expeditions. Urban II, who organized the First Crusade, was a big proponent of the reforms. In the 13th century the reforms were perfected by Innocent III, Honorius III, Gregory IX, and Innocent IV, and at the time the church was probably more powerful than it had ever been and maybe as powerful as it would ever be. There was a clear hierarchy of authority from the Pope down to the local parish priests. Gregory IX also reorganized church law, and developed the code of canon law that lasted right up until the 20th century.

But not everyone agreed with this. The Pope was symbolically important and traditionally the leader of the church, but why should everyone else listen to him? Why was he more important than any other bishop or archbishop? Secular rulers also did not appreciate their rights being taken away, and they continued to try to appoint their own local church officials. The Holy Roman Emperor (Frederick II’s predecessors) often supported an Antipope, another claimant to the papacy, who didn’t support the reforms and instead supported secular control over the clergy. There were antipopes for almost the whole 12th century, up to 1180 - although at the same time the popes also supported “anti-kings”, rival claimants to the empire. So it’s really just a historical convention based on hindsight that we recognize a “real” line of popes and emperors today - at the time it was much less clear.

One of the tools that the Papacy tried to use against uncooperative secular states was excommunication. I’m sure there are endless examples of medieval people being excommunicated, but some examples of monarchs, aside from Frederick, are Philip I of France and John of England. Philip was excommunicated because he rejected his wife and shacked up with the Count of Anjou’s wife. If he promised to return to his wife, the excommunication would be lifted, but he never did. So the church could use it as a punishment, but was it an all-powerful deterrent? Philip, at least, didn’t seem to think excommunication was much of a threat.

A more severe form of excommunication was an interdict. Instead of excommunicating just one person, a whole parish or a whole diocese or even an entire country could be placed under interdict, so church services would be suspended for the whole area. One of the most notable examples of this in the Middle Ages is when all of England was placed under interdict from 1208 to 1213, because King John and the Pope couldn’t agree on who to appoint as Archbishop of Canterbury. Under the Gregorian reforms, John had no right to appoint an archbishop - but John disagreed, so maybe we can say that the Pope’s authority was not quite all-encompassing. But the Pope did manage to stop church services in England for 5 years, so the church was pretty powerful after all.

I’ll come back to Frederick again later, but he was excommunicated for delaying his crusade, and then when he eventually did go on crusade, he managed to take back Jerusalem by treaty rather than by force. But the church was unhappy about that - wasn’t the point of a crusade to take Jerusalem by military force? And could an excommunicated ruler be allowed to take back Jerusalem? According to the church, no, he could not - so Jerusalem itself was placed under interdict, just to make everything Frederick did meaningless. This was an extremely bizarre situation! Jerusalem, the holiest site in Christianity, the whole point of the crusades, couldn’t even hold any Christian church services.

One more example, since it’s something I was working on anyway so I happen to have the info at hand - in Brittany in the early 13th century, the Duke was excommunicated by the Pope because he kept appropriating church lands and revenues whenever a bishop died. In Brittany (and elsewhere in France) they had accepted the idea that secular rulers couldn’t appoint new bishops, but the Duke of Brittany continued to claim that, when a bishop’s see was vacant, the Duke would govern church territory and collect rents and other profits from the diocese. The church, of course, disagreed. In Brittany, at least, this conflict dragged on for decades. The Pope could excommunicate the Duke, but did it really have any effect?

The other problem was that the church itself was a political states, and under the reformist popes they were politicians as much as clerics. The church’s political power came from the “Donation of Constantine” which was supposedly from the 4th century, and although we now know it was an 8th-century forgery, in the 12th and 13th centuries everyone assumed it was real (well, there were some suspicions, but it wasn’t proven to be a forgery until the 15th century). So not only did the Pope claim authority over the entire Catholic Church structure throughout all of Europe, he also ruled an actual political state. Everyone could see that this was an unusual situation, but the reality was that the Papal States were a powerful entity in central Italy.

So the Papal States were bordered by the Holy Roman Empire to the north, although the cities of northern Italy were effectively independent, and could ally with either the Empire or the Pope, depending on the circumstances (the “Lombard League”). To the south the Papal States’ neighbour was the Kingdom of Sicily, which covered both the south of Italy and the island of Sicily itself. The problem in the 13th century was that both Sicily and the Empire happened to be inherited by one man - Frederick II. His father was the emperor and his mother was the queen of Sicily. This seemed to be entirely too much power for one man, not just according to the pope, but according to other secular rulers as well. There were attempts to replace Frederick as emperor or as king of Sicily, or both.

The Lombard League, the Pope, and the Emperor weren't the best of friends, but things worked well enough until 1227, when Gregory IX became Pope. Different historians will look at this period differently and assign blame to either side, and I suppose I have my own bias as well, but the most neutral way I can describe it is that both Gregory and Frederick were stubborn, ambitious, and intractable - they were both die-hard believers in their respective rights and had the power and the will to enforce them.

“With his election, cooperation between pope and emperor gave way to the idea of the subordination of emperor to pope. Gregory was all the more able to insist on these points because he was an exceptionally able orator and propagandist, 'forceful in word and deed’…The new pope was determined to demonstrate to the world the fickleness of Frederick. His opportunity was provided by the crusade. Gregory's first letter to Frederick actually says: do not put yourself in a position where I have to take action against you; go on crusade as promised, or else. But there are signs Gregory expected Frederick to fail in his undertaking. He assumed the emperor would not depart at the promised time. In that case, it would be his solemn duty to excommunicate Frederick for flagrant breach of his oath.” (Abulafia, 164-65)