The National Service of Thanksgiving at St. Giles Cathedral is the crown jewel of the couple's first Royal Week in Scotland