Google Spreadsheet Data API

I gave a presentation to the U. of Delaware WebDev group and IT-CS&S (RDMS) on the topic of using data from Google Docs Spreadsheets for consumption in a scripted workflow. I'm using this in the UD Map for automatically incorporate rendering decisions from OCM.

The gist is that you open an https stream with headers for
authorizing, capture an authorization token on the response, open
another stream returning an xml of the table, and parse that. In
addition, you could form the url for the second stream with parameters
that query the column of interest, for example.

Here is the presentation:

https://docs.google.com/present/view?id=dx7rb3j_104dxjkq8c6

Here is some sample code (excuse references to my database/tables):

function do_post_request($url, $data, $optional_headers = null)
{
 echo "Begin do_post_request \n";
 $params = array('http' => array(
    'method' => 'POST',
    'content' => $data
  ));
 if ($optional_headers !== null) {
$params['http']['header'] = $optional_headers;
 }
 $ctx = stream_context_create($params);
 $fp = @fopen($url, 'rb', false, $ctx);
 if (!$fp) {
throw new Exception("Problem with $url, $php_errormsg");
 }
 $response = @stream_get_contents($fp);
 if ($response === false) {
throw new Exception("Problem reading data from $url, $php_errormsg");
 }
 return $response;
}

function doFromGoogle(){
echo "Begin doFromGoogle \n";
define("GURL", "https://www.google.com/accounts/ClientLogin");
define("SPREADSHEETREFKEY", "***YOURKEYHERE***");

//creates post to google auth, creates $auth string from google
$dataArr = array();
$dataArr['accountType'] = 'GOOGLE';
$dataArr['Email'] = '***YOUREMAILHERE***';
$dataArr['Passwd'] = '***YOURPASSWORDHERE***';
$dataArr['service'] = 'wise'; //name for spreadsheet data service
$dataArr['source'] = '***YOURSORUCENAMEHERE***'; //this may be optional

$dataStr = http_build_query($dataArr);

$opt_headers = 'application/x-www-form-urlencoded';

$responseStr = do_post_request(GURL, $dataStr, $opt_headers);
$responseArr = explode("\n",$responseStr); //breaking apart request to get auth token
$auth = explode("=", $responseArr[2]);
$auth = $auth[1];

$authStr = 'Authorization: GoogleLogin auth=' . $auth;
$url = 'https://spreadsheets.google.com/feeds/list/' . SPREADSHEETREFKEY . '/1/private/full';


$opts = array(
 'http'=>array(
'method'=>"GET",
'header'=>"$authStr"
 )
);

$context = stream_context_create($opts);

// ini_set('Authorization', "$authStr");

$handle = fopen($url, "r", false, $context);
$contents = stream_get_contents($handle);
fclose($handle);

$xml = new SimpleXMLElement($contents);

$fullquery = null;


$count = 0;
foreach ($xml->entry as $entry){
$count = $count + 1;
$id = $entry->children('http://schemas.google.com/spreadsheets/2006/extended')->osmid;
$render = $entry->children('http://schemas.google.com/spreadsheets/2006/extended')->dorender;
$label = $entry->children('http://schemas.google.com/spreadsheets/2006/extended')->dolabel;
$labelname = $entry->children('http://schemas.google.com/spreadsheets/2006/extended')->labelname;
$maxzoom = $entry->children('http://schemas.google.com/spreadsheets/2006/extended')->maxzoom;
$minzoom = $entry->children('http://schemas.google.com/spreadsheets/2006/extended')->minzoom;
$labelname = addslashes ($labelname);

//$color = $entry->children('http://schemas.google.com/spreadsheets/2006/extended')->color;

//print "$id, $count \n $render \n $label \n $labelname";
print "$id  $maxzoom $minzoom\n";

$query = null;

if($render <> ''){
 $query .= "UPDATE master SET dorender = '" . $render . "' where osm_id = $id and dorender <> '" . $render ."';\n";
}
if($label <> ''){
 $query .= "UPDATE master SET dolabel = '" . $label . "'where osm_id = $id and dolabel <> '" . $label ."';\n";
}
if($labelname <> ''){
 $query .= "UPDATE master SET labelname = '" . $labelname . "'where osm_id = $id and labelname <> '" . $labelname ."';\n\n";
}
if($maxzoom <> ''){
 $query .= "UPDATE master SET maxzoom = '" . $maxzoom . "'where osm_id = $id;\n\n";
}
if($minzoom <> ''){
 $query .= "UPDATE master SET minzoom = '" . $minzoom . "'where osm_id = $id;\n\n";
}

$fullquery .= $query;
}

//print($query);
$result = pg_query($fullquery) or die('Query failed: ' . pg_last_error());
}