Is it possible to get the contents of a Google Docs Spreadsheet from within a GMail gadget?

Question

Here is what I figured so far:

To get content of the spreadsheet "od6" with the ID "0Aj3x4n7SOPMRdFA2VmJuampIUDFzdlAwRUwtSEJacmc" you have to acces this URL: https://spreadsheets.google.com/feeds/cells/0Aj3x4n7SOPMRdFA2VmJuampIUDFzdlAwRUwtSEJacmc/od6/private/full

(see API)

This works fine, if I put the URL into my browser (because I'm logged in to GDocs and the cookies are set).

If I like to access the URL above from within my GMail Gadget, I guess I have 2 options.

1. Load it via Ajax
2. Load it directly in the PHP that generates the gadget

With the first approach I run into the problem, that the cookies are not known in the Ajax call (I guess this is because of different Domains involved).

Background Info: My Gadget-PHP-Script runs at my host (https://sslsites.de), this Gadget is loaded by GMail and provided trough some kind of proxy (so the Host of my Gadget within the IFrame is http://kipb0cvv7sa9gunc3o5eii57sr1eoem5-a-gm-opensocial.googleusercontent.com/gadgets). Where the URL in the location bar (The IFrames parent) is https://mail.google.com/mail

So it seams an Ajax call from this IFrame can not send cookies to the domain https://spreadsheets.google.com . Right? The response is empty.

Then I tried the second approach. If I like to access the Spreadsheet from PHP I will need OAuth. Which works fine with this code:

<?php
function make_api_call($url, $token) {
  $ch = curl_init();
  curl_setopt($ch, CURLOPT_URL, $url);
  curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
  $curlheader[0] = sprintf("Authorization: AuthSub token=\"%s\"/n", $token);
  curl_setopt($ch, CURLOPT_HTTPHEADER, $curlheader);
  $output = curl_exec($ch);
  curl_close($ch);
  return $output;
}

function get_session_token($onetimetoken) {
  $output = make_api_call("https://www.google.com/accounts/AuthSubSessionToken", $onetimetoken);
  if (preg_match("/Token=(.*)/", $output, $matches)) $sessiontoken = $matches[1];
  else {
    echo "Error authenticating with Google.";
    exit;
  }
  return $sessiontoken;
}

if ($_GET['token'] and !$_COOKIE['token']) {
  $_COOKIE['token'] = get_session_token($_GET['token']);
  setcookie("token", $_COOKIE['token']);
}

if ($_COOKIE['token']) {
  $accountxml = make_api_call("https://spreadsheets.google.com/feeds/cells/tP6VbnjjHP1svP0EL-HBZrg/od6/private/full", $_COOKIE['token']);
  print_r($accountxml);
}
?>
<a href="https://www.google.com/accounts/AuthSubRequest?scope=https%3A%2F%2Fspreadsheets.google.com%2Ffeeds%2F&amp;session=1&amp;secure=0&amp;next=https%3A%2F%2Fsslsites.de%2Fknox.orgapage.de%2Fdocget.php" target="_blank">Sign in</a>

But again, I have the issue with the domains here. This code runs well on https://sslsites.de . But from GMail I have the issue, that I can not pass the $_COOKIE['token']. If I register https://sslsites.de with OAuth, my browser won't pass the cookie through GMails gadget proxy. If I register the proxy (http://kipb0cvv7sa9gunc3o5eii57sr1eoem5-a-gm-opensocial.googleusercontent.com/gadgets) it selfe for OAuth the Cookie is there, but my PHP script does not know of it as well.

So, is there any way I can pass the Cookie trough the proxy (which seams like a black box to me, cause I can not find any documentation on it)?

Or are there other APIs to access spreadsheets? The spreadsheets I'm talking about are public to everyone, so I would not expect one to need to authenticate.

However the Spreadsheet is public: https://spreadsheets.google.com/ccc?key=0Aj3x4n7SOPMRdFA2VmJuampIUDFzdlAwRUwtSEJacmc&hl=en&authkey=CJ2ppJsP

But not the Data API call to the same: https://spreadsheets.google.com/feeds/cells/0Aj3x4n7SOPMRdFA2VmJuampIUDFzdlAwRUwtSEJacmc/od6/private/full

Answer 1

I finaly figured out how to use OAuth within the gadgets.

You have to define it within the gadgets XML, like this:

<OAuth>
  <Service name="google">
    <Access url="https://www.google.com/accounts/OAuthGetAccessToken" method="GET" />
    <Request url="https://www.google.com/accounts/OAuthGetRequestToken?scope=https://spreadsheets.google.com/feeds/" method="GET" />
    <Authorization url="https://www.google.com/accounts/OAuthAuthorizeToken?oauth_callback=http://oauth.gmodules.com/gadgets/oauthcallback" />
  </Service>
</OAuth>

Then you will be able to query the spreadsheet feeds:

function initSSData() {
  var params = {};
  params[gadgets.io.RequestParameters.CONTENT_TYPE] = gadgets.io.ContentType.JSON;
  params[gadgets.io.RequestParameters.AUTHORIZATION] = gadgets.io.AuthorizationType.OAUTH;
  params[gadgets.io.RequestParameters.OAUTH_SERVICE_NAME] = "google";
  params[gadgets.io.RequestParameters.OAUTH_USE_TOKEN] = "always";
  params[gadgets.io.RequestParameters.METHOD] = gadgets.io.MethodType.GET;

  gadgets.io.makeRequest("https://spreadsheets.google.com/feeds/spreadsheets/private/full?title=GIdea&alt=json", function(response) {
    // Show sign in
    if (response.oauthApprovalUrl) {
      var popup = shindig.oauth.popup({
        destination: response.oauthApprovalUrl,
        windowOptions: null,
        onOpen: function() { document.getElementById('approval').style.display = "none"; document.getElementById('waiting').style.display = "block"; },
        onClose: function() { initSSData(); }
      });
      var personalize = document.getElementById('personalize');
      personalize.onclick = popup.createOpenerOnClick();
      var approvaldone = document.getElementById('approvaldone');
      approvaldone.onclick = popup.createApprovedOnClick();

      document.getElementById('approval').style.display = "block";

    // Show result
    } else if (response.data) {
      // Access the spreadsheet with response.data
    }
  }
}