Over the last few years, I have been writting scipts for a lot of functionality. I often find it a pain to use a database. Most often the database are not even databases in true sense but really a table I need to store and refer to. The tables are not more than 40-50 rows and never exceed few hundreds rows. It was usually accessed from one program at a time and did not need concurrancy. Using a database is like trying to kill a mosquito with a bazooka :-).
In past I have used simple CSV files that I have stored in my local file system. The problem with using a CSV file on the local or remote server is that it becomes very difficult to edit manually or een view it. (I am old fashinoned and like coding on command line most of the times)
Over the last couple of projects I have found using GoogleDocs Spreadseheet as very useful.
1. Kept on the cloud and hence always available
2. Built in Version control :-)
3. Can be editted and viewed on UI
4. Easy for the layman to see and work on it.
This can come especially useful in cases like when you are trying to store a list of users who have filled up the form etc. However looking at Google Data API was a pain. There was no direct way of easily accessing the data. What I needed was that I should be
Here is a simple API. Hope this helps and let me know if there is something I can do to improve
<?php
/**
* @see Zend_Loader
*/
require_once 'Zend/Loader.php';
/**
* @see Zend_Gdata
*/
Zend_Loader::loadClass('Zend_Gdata');
/**
* @see Zend_Gdata_ClientLogin
*/
Zend_Loader::loadClass('Zend_Gdata_ClientLogin');
/**
* @see Zend_Gdata_Spreadsheets
*/
Zend_Loader::loadClass('Zend_Gdata_Spreadsheets');
/**
* @see Zend_Gdata_App_AuthException
*/
Zend_Loader::loadClass('Zend_Gdata_App_AuthException');
/**
* @see Zend_Http_Client
*/
Zend_Loader::loadClass('Zend_Http_Client');
class gSS {
private $email = "****@*********.com";
private $password = "***************";
public function __construct($gDocName,$sheet) {
try {
$client = Zend_Gdata_ClientLogin::getHttpClient($this->email, $this->password,
Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME);
} catch (Zend_Gdata_App_AuthException $ae) {
exit("Error: ". $ae->getMessage() ."\nCredentials provided were email: [$email] and password [$password].\n");
}
$this->gdClient = new Zend_Gdata_Spreadsheets($client);
// Find spreadsheet Key
$feed = $this->gdClient->getSpreadsheetFeed();
//var_dump($feed); exit;
$i =0;
foreach($feed->entries as $entry) {
if ($entry->title->text == $gDocName) break;
$i++;
}
if ($feed->entries[$i]->title->text == $gDocName) {
$currKey = explode('/', $feed->entries[$i]->id->text);
$this->ssKey = $currKey[5];
} else {
exit("Error: ". $ae->getMessage() ."Document $gDocName not found\n");
}
//Assuming that worksheet will be always 0
$query = new Zend_Gdata_Spreadsheets_DocumentQuery();
$query->setSpreadsheetKey($this->ssKey);
$feed = $this->gdClient->getWorksheetFeed($query);
$currWkshtId = explode('/', $feed->entries[$sheet]->id->text);
$this->ssWkshtId = $currWkshtId[8];
}
public function gSSGetRows() {
//Get Data
$query = new Zend_Gdata_Spreadsheets_ListQuery();
$query->setSpreadsheetKey($this->ssKey);
$query->setWorksheetId($this->ssWkshtId);
$dataFeed = $this->gdClient->getListFeed($query);
// var_dump($dataFeed);exit;
$i = 0;
foreach($dataFeed->entries as $entry) {
if ($entry instanceof Zend_Gdata_Spreadsheets_ListEntry) {
$rowData = $entry->getCustom();
foreach($rowData as $customEntry) {
$rows[$i][$customEntry->getColumnName()] = $customEntry->getText();
}
}
$i++;
}
//var_dump($rows);
return $rows;
}
public function gSSSetRow($index,$row) {
echo "gSSSetData\n";
//Get Data
$query = new Zend_Gdata_Spreadsheets_ListQuery();
$query->setSpreadsheetKey($this->ssKey);
$query->setWorksheetId($this->ssWkshtId);
$dataFeed = $this->gdClient->getListFeed($query);
{
$entry=$this->gdClient->updateRow($dataFeed->entries[$index], $row);
if ($entry instanceof Zend_Gdata_Spreadsheets_ListEntry) {
//echo "Success! \n";
$entry->save();
}
}
return $row;
}
public function gSSSetRows($rows) {
echo "gSSSetData\n";
//Get Data
$query = new Zend_Gdata_Spreadsheets_ListQuery();
$query->setSpreadsheetKey($this->ssKey);
$query->setWorksheetId($this->ssWkshtId);
$dataFeed = $this->gdClient->getListFeed($query);
// var_dump($dataFeed);exit;
$i = 0;
foreach($rows as $row) {
var_dump($row);
$entry=$this->gdClient->updateRow($dataFeed->entries[$i], $row);
if ($entry instanceof Zend_Gdata_Spreadsheets_ListEntry) {
echo "Success! \n";
$entry->save();
}
$i++;
}
//var_dump($rows);
return $rows;
}
public function gSSInsertRow($row) {
var_dump($row);
$entry = $this->gdClient->insertRow($row,$this->ssKey,$this->ssWkshtId);
if ($entry instanceof Zend_Gdata_Spreadsheets_SpreadsheetsEntry) {
echo "Success! \n";
$entry->save();
}
}
}
/*
$ss = new gSS("Interval Log",0);
$rows = $ss->gSSGetRows();
$ss->gSSSetData($rows);
*/
?>
