Is it required for heads of state to attend Olympic Games Opening Ceremony?

Other than the head of state of the host country, it's not required, but they are commonly invited and some will attend (being Canadian our head of state is the Queen of England- she did not open the 1988 or 2010 Olympics but instead had our Governor General perform in her absence- she did open the 2012 London games). Though not the head of state, PM Justin Trudeau was in South Korea for the opening ceremony, and PM Harper was in Vancouver for the 2010 Opening ceremony.

Only the biggest head in the host country. In the US, that would be Michael Phelps or Willie Nelson...

The Olympic Charter states "The Olympic Games shall be proclaimed open by the Head of State of the host country". So there you are!

But the Charter can't tell a head of state what to do. It's not law, it can only really say how the IOC would like things to be, so there have been a few occasions when the head of state didn't do it. Then someone almost as senior will represent them and open the games. That will be the vice-president of a republic, or some other member of the royal family if the country is a monarchy.

Or the Governor-General if the country is a Commonwealth realm. To explain this, Queen Elizabeth II is queen of 16 countries, she can hardly be expected to do her duties in person in all of them, so except for the UK itself, she appoints a Governor-General to do her job. The GG does all the Queen stuff like formally calling elections, appointing the Prime Minister, signing off laws, and opening Parliament, unless the Queen pays the country a visit and does that herself while she's there. (Which she's doing a lot less of these days now she's nearly 92!) So that's the appropriate person. Most of these realms are pretty small but Australia and Canada have hosted the games, so most of the time when the games have been there, the GG has opened the games.

The last time the head of state didn't do it was in Rio in 2016, for the very good reason that the President of Brazil had been impeached so the Vice-President did it. But before that, other than Governors-General, the last time was when Walter Mondale did it in 1980 on behalf of Jimmy Carter.

In any case, the Olympic Games don't come to a country very often, so it's a huge thing when it does, and probably the head of state will WANT to be seen on TV all over the world opening the games.