Jump to content

Using data from Google Spreadsheet to populate webpage?


piblondin

Recommended Posts

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!

Link to comment
Share on other sites

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);
}

Link to comment
Share on other sites

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;
			}
		}
	}
}

Link to comment
Share on other sites

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());
}
?>

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.