Google+ Photos has replaced Picasa Web now. So Sign up for Google+ Account [access through Google Account]. there are plenty of new features in Google+ Photos, such as: AutoAwsome, Automatic Gif Animations etc..
There are many Google+ features also available for Google Sites.
It is possible to create advanced slideshows with jQuery, jQueryUI within a HTML box. Google Apps Script can also be used to create a more Cloud connected experience.
You can use the following websites and online photo systems to embed a Slideshow into Google Sites. Embed an Image or your Web Album from Flickr, Picasa and Photobucket.