It is possible that closed cell foam is the only way to achieve the building code required min. R value without furring the rafters and losing headroom. Can't say more than that without seeing the design.

spray foam is a good thing, when it is correctly installed.
an unvented attic (foam @ roofline) puts ductwork in a semi conditioned space, and really adds to the air tightness of the house.
read thru a few posts about upstairs being hot/cold
and you will see what difficulty folks have with comfort
in rooms that are built in attic space. the problem is the air leakage and heat/cold gain from attic space into these rooms. the walls, the floor and ceiling of these rooms are all surrounded by attic temp air. when you move the thermal boundry to the roofline, and make it air tight
then the leaks in the walls ceilings and floors are not
pulling extreme attic conditions into the house.
things like recessed cans, oversized cuts at bath fans
and even duct leakage become less of an issue because the
air and thermal boundry is at the roofline.
visit www.buildingscience.com and look for unvented attics for your climate.

best of luck.

If the room/room in the attic isnt used too much, nor is it a priority when the whole house is taken into consideration, and with the assumption everything will be done correctly.

In general, could/will the rest of the house (all the non attic rooms) benefit from spray foam?

If the HVAC is up there, you will see a whole house benefit. Whether it is worth the cost is another question but you will definitely see a benefit. It is also unlikely that you have adequate attic insulation already so this will just add to your effective attic insulation.

Will there be insulation in the floor between the guest room and the main living spaces? If so, how much?

what kind of spray foam? open or closed? I typically never recommend closed foams in rafters as it prevents the water from coming through if there was a leak in the roof. With closed cell, your roof could be leaking and you will never know it until your sheathing rots away.
Open cell will be cheaper as well, with a slightly less r per inch.

Yes the rest of the home will benefit as well.
I also recommend open cell less R-value means you have
to put more, but on the roofline unless you have a cathedral ceiling this isn't an issue.

ROI is higher, but still much less than if you had
foamed the walls (a waste IMO) as well.
you'll prolly have a 10-12 ROI depending on roof height
and size of house. You do know that you don't foam over porches or garage? foam installer, builder or you can build a wall even attaching felt paper for them to foam against to exclude these areas.

From what I have heard, foam insulation is a really effective solution. As long as the foam is applied properly it should work as you need it to. I have been doing research and foam insulation has become pretty popular lately. http://www.armstrong1234.com/roofing/foam_roofing/residential/