Google Spreadsheets is not only a basic spreadsheet product,but also a very powerful tool for web applications. By using the
Spreadsheets Data API it can be easily performed as a web-based database to generate a web form, a online shopping cart, a Google Map mashup .... etc.
Dojo DataGrid is a JavaScript library with spreadsheet view function , it supports
datastores like Csv, Json, XML, Googlefeeds... etc. However, there is no Google Spreadsheets datastore for Dojo yet. In order to let Dojo Grid easily read Google Spreadsheets cells data, I created a PHP program to generate the CSV file from Google Spreadsheet as a dojox.data.CsvStore.
The
PHP Zend Gdata 1.10.0 library is used to talk to the Google Data API, it takes care the client login, file listing, spread sheet files and worksheet tokens retrieving and cells data feed. retrieve the spreadsheets file list and key tokens, once user decide which file to read,
The first part of the PHP is for client login :
<div style="color: blue;">
<span style="font-size: x-small;"><i>set_include_path(get_include_path() . PATH_SEPARATOR . '/var/www/html/ZendGdata-1.10.0/library');
require_once 'Zend/Loader.php';
Zend_Loader::loadClass('Zend_Http_Client');
Zend_Loader::loadClass('Zend_Gdata');
Zend_Loader::loadClass('Zend_Gdata_ClientLogin');
Zend_Loader::loadClass('Zend_Gdata_Spreadsheets');</i></span>
<span style="font-size: x-small;"><i>
$user = $_GET["id"];
$pass = $_GET["pass"];
if(isset($_GET["key"]))
$key = $_GET["key"];
else
$key="";</i></span></div>
To read the spreadsheets list :
<div style="color: blue;">
<span style="font-size: x-small;"><i>function promptForSpreadsheet1($gdClient)
{
$feed =$gdClient->getSpreadsheetFeed();
$i=0;
$arr = array();
foreach( $feed->entries as $entry) {
$currKey = split('/', $entry->id->text);
$token = $currKey[5];
$arr[] = array("name" => $entry->title->text, "id" => $token);
$i++;
}
$jsonStr = json_encode($arr);
echo "{'identifier':'id','label':'name','items':$jsonStr}";
}</i></span></div>
To read the worksheets list of select spreadsheet :
<span style="color: blue; font-size: x-small;"><i>function getFormWorksheet($gdClient)
{
global $key, $wkid;
$query = new Zend_Gdata_Spreadsheets_DocumentQuery();
$query->setSpreadsheetKey($key);
$feed = $gdClient->getWorksheetFeed($query);
echo printFeed($feed);
$input = getInput("\nSelection"); */
$currWkshtId = split('/', $feed->entries[0]->id->text);
$wkid = $currWkshtId[8];
}</i></span>
To read the header and row data and convert them into a CSV formatted file :
<span style="color: blue; font-size: x-small;"><i>function cellsGetCsv()
{
global $gdClient, $key, $wkid;
$arr = array();
$query = new Zend_Gdata_Spreadsheets_CellQuery();
$query->setSpreadsheetKey($key);
$query->setWorksheetId($wkid);
$feed = $gdClient->getSpreadsheetCellFeedContents($query);
$jsonStr = json_encode($feed);
$pre_key="A";
$columns=0;
$rows=0;
$header = array();
foreach($feed as $key=>$entry){
if (substr($key, -1) <> "1") {
$columns = convertAlphabetToInt(substr($pre_key, 0, -1));
$jsonStr = json_encode($header);
break;
}
$header[] = $entry["value"];
$pre_key=$key;
}
$lastkey = end(array_keys($feed));
for ($k=1; $k<=strlen($lastkey); $k++){
if ( is_numeric(substr($lastkey, strlen($lastkey)-$k, 1))==false){
$rows=(int)substr($lastkey, strlen($lastkey)-$k+1);
}
}
$head = "";
$data = "";
for ($i = 0; $i< $columns; $i++){
$head .= $header[$i] . ",";
}
$arr = array();
for ($j=2; $j<=$rows; $j++){
$line = '';
for ($m=1; $m<=$columns; $m++){
/* $value = $feed[convertIntToAlphabet($m)+$j]->value; */
if(isset($feed[convertIntToAlphabet($m). (string)$j]))
$value = $feed[convertIntToAlphabet($m). (string)$j]["value"];
else
$value = "";
if ((!isset($value)) OR ($value == "")) {
$value = ",";
} else {
$value = str_replace('"', '""', $value);
$value = '"' . $value . '"' . ",";
}
$line .= $value;
}
$data .= trim($line)."\n";
}
$data = str_replace("\r", "", $data);
echo "$head\n$data";
}
function convertAlphabetToInt($alpha_string) {
$int_wert=0;
$potenzcounter=0;
for ($i=strlen($alpha_string);$i>0;$i--) {
$ordinalwert=(ord(substr($alpha_string,$i-1,1))-64);
$int_wert+=$ordinalwert*pow(26,$potenzcounter);
$potenzcounter++;
}
return $int_wert;
}
function convertIntToAlphabet($int_wert) {
$alpha_string="";
if($int_wert%26>=1) {
$alpha_string=chr(($int_wert%26)+64).$alpha_string;
$alpha_string=convertIntToAlphabet($int_wert/26).$alpha_string;
}
return $alpha_string;
}</i></span>
The demo program - MAP123.html which interacts with the PHP file (ssheet.php) thru Ajax will need your GMail(or Google Apps account) id and password -
If successfully login, then it will show the list of available spreadsheets for you to choose -
Once you decide the spreadsheet to use, click at "get spreadsheet" button to retrieve, however, since the program does not offer you the choice of worksheets (Google Spreadsheets supports multi-worksheet), it will always grab the first available worksheet from the spreadsheet.
A spreadsheet of exact layout of original Google Spreadsheet will be displayed on top of the page!
The bottom part of the program is to display the Google map with the locations(addresses) information on the spreadsheet.As long as there is a header called "ADDRESS" the Google Map will be triggered and display all addresses on the "ADDRESS" column. Click at any row, it will diplay the information windows for the selected row on the Google Map.
You can try the demo program
here, or you can download the whole files
here.
