Using data from Google Spreadsheet to populate webpage?

[piblondin]

Hello,

 

Can someone point me towards a solution for using values from a Google Spreadsheet to populate a webpage? I would like to be able to insert values from specific cells into the HTML that's generated by PHP. So, something like $R2C4 would bring up the value of the cell in row 2, column 4. Google offers a way to display specific cells using iframes, but I need something that will allow me to use the cell values without breaking the flow of html, as you would with PHP variables. I would want to be able to have something like

 

"Your name is " . $R2C4 .

 

display Your name is followed by whatever is in that cell of the spreadsheet. I don't need to run any sort of loop. I just want to be able to consistently reference specific cells in the HTML. Any idea if this is possible with something that's already out there, be it Javascript or PHP? I imagine that there must be a way to generate feed urls for the specific cells referenced using PHP and to parse the spreadsheet feeds using something like SimplePie.

 

Thanks!

[MadTechie]

Maybe reading Google Spreadsheets APIs (Developer's Guide: PHP) would be a good place to start!

 

basically you use Zend Framework and use the class Zend_Gdata_Spreadsheets_CellQuery

 

ie

public function cellsGetAction(){
    $query = new Zend_Gdata_Spreadsheets_CellQuery();
    $query->setSpreadsheetKey($this->currKey);
    $query->setWorksheetId($this->currWkshtId);
    $feed = $this->gdClient->getCellFeed($query);
    $this->printFeed($feed);
}

[piblondin]

Yikes! I've been around the documentation on code.google.com and their user groups. I'm guessing that this maybe beyond my current abilities, which are, really, next to nothing.

[MadTechie]

okay less control but try a good old webscrape 

$key = "THE KEY FOR YOUR SPREADSHEET";
$url = "http://spreadsheets.google.com/feeds/cells/$key/1/public/values";
$ch = curl_init ();
// set URL and other appropriate options
curl_setopt ( $ch, CURLOPT_URL, $url );
curl_setopt ( $ch, CURLOPT_HEADER, 0 );
curl_setopt ( $ch, CURLOPT_RETURNTRANSFER, TRUE );
// grab URL and pass it to the browser
$google_sheet = curl_exec ( $ch );
// close cURL resource, and free up system resources
curl_close ( $ch );
$doc = new DOMDocument ( );
$doc->loadXML ( $google_sheet );
$nodes = $doc->getElementsByTagName ( "cell" );
if ($nodes->length > 0) {
	foreach ( $nodes as $node ) {
		// 2nd row
		if ($node->getAttribute ( "row" ) == 2){
			if (preg_match('/^[.+_a-z0-9-]+@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,6})$/', $node->nodeValue)) {
				echo $node->nodeValue;
			}
		}
	}
}

[piblondin]

You overestimate me! Totally lost. How to specify the cell and echo its value?

[MadTechie]

Okay let me see if i can create a live example

 

[piblondin]

Wow! That would be awesome. Let me know if you're able to throw something together.

[MadTechie]

Sorry for the delay, I got called way,

I had some issules with the cURL route so, I created a trimmed down version of the Zend Framework route, your need to get Zend GData, but other than that this should work, i have tested it locally

 

<?php
/* 1 .Get Zend GData from here http://framework.zend.com/download/gdata
 * 2. open zip and move the Zend folder from the libary to the same folder as this script
 * 3. update the line at the bottom
 * $gDoc = new gSpreadSheet('GOOGLE ACCOUNT -EMAIL ', 'PASSWORD');
 */
class gSpreadSheet{
private $gdClient;
private $currKey;
private $Cells;

function __construct($username, $password){
	//GData setup
	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');	

	//Login
	//Single-user "installed" client authentication (via HTTP)
	$authService = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME;
	$httpClient = Zend_Gdata_ClientLogin::getHttpClient($username, $password, $authService);
	$this->gdClient = new Zend_Gdata_Spreadsheets($httpClient);
}

public function ShowWorkSheets(){
    $feed = $this->gdClient->getSpreadsheetFeed();
    return $this->printSS($feed);
}
public function printSS($feed){
    foreach($feed->entries as $K => $entry) {
        if (!$entry instanceof Zend_Gdata_Spreadsheets_CellEntry && !$entry instanceof Zend_Gdata_Spreadsheets_ListEntry ) {
            print "<p><a href=\"?WS=$K\">{$entry->title->text}</a></p>\n";
        }
    }
}

public function getCells($feed){
    foreach($feed->entries as $entry) {
        if ($entry instanceof Zend_Gdata_Spreadsheets_CellEntry) {
            $this->Cells[$entry->title->text] = $entry->content->text;
        }
    }
}

public function getSpreadFeed($id){
	$feed = $this->gdClient->getSpreadsheetFeed();
	$currKey = split('/', $feed->entries[$id]->id->text);
	$this->currKey = $currKey[5];
	return $feed->entries[$id]->id->text;
}

public function setWorkSheet($id){
	$this->currWkshtId = $id;
}

public function getCell($cell){
	$cell = strtoupper($cell);
    $query = new Zend_Gdata_Spreadsheets_CellQuery();
    $query->setSpreadsheetKey($this->currKey);
    $query->setWorksheetId($this->currWkshtId);
    $feed = $this->gdClient->getCellFeed($query);
    $this->getCells($feed);
    return $this->Cells[$cell];
}
public function getAllCell(){
    $query = new Zend_Gdata_Spreadsheets_CellQuery();
    $query->setSpreadsheetKey($this->currKey);
    $query->setWorksheetId($this->currWkshtId);
    $feed = $this->gdClient->getCellFeed($query);
    $this->getCells($feed);
    return $this->Cells;
}
}

$gDoc = new gSpreadSheet('GOOGLE ACCOUNT -EMAIL ', 'PASSWORD');
if(!isset($_GET['WS'])){
$gDoc->ShowWorkSheets();
exit;
}else{
$gDoc->getSpreadFeed($_GET['WS']);
$gDoc->setWorkSheet(1);
var_dump($gDoc->getCell('A2'));
echo "<HR />";
var_dump($gDoc->getAllCell());
}
?>

[MadTechie]

My test doesn't have SSL installed so i can't publish it their (until i setup openssl on it)

I have created a quick video (MP4/quicktime) to show how it should look,

 

 

[attachment deleted by admin]

[piblondin]

Very cool--thanks much, MadTechie! I'm going to test it out tonight.