piblondin Posted February 4, 2010 Share Posted February 4, 2010 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! Quote Link to comment Share on other sites More sharing options...
MadTechie Posted February 4, 2010 Share Posted February 4, 2010 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); } Quote Link to comment Share on other sites More sharing options...
piblondin Posted February 4, 2010 Author Share Posted February 4, 2010 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. Quote Link to comment Share on other sites More sharing options...
MadTechie Posted February 4, 2010 Share Posted February 4, 2010 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; } } } } Quote Link to comment Share on other sites More sharing options...
piblondin Posted February 4, 2010 Author Share Posted February 4, 2010 You overestimate me! Totally lost. How to specify the cell and echo its value? Quote Link to comment Share on other sites More sharing options...
MadTechie Posted February 4, 2010 Share Posted February 4, 2010 Okay let me see if i can create a live example Quote Link to comment Share on other sites More sharing options...
piblondin Posted February 4, 2010 Author Share Posted February 4, 2010 Wow! That would be awesome. Let me know if you're able to throw something together. Quote Link to comment Share on other sites More sharing options...
MadTechie Posted February 5, 2010 Share Posted February 5, 2010 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()); } ?> Quote Link to comment Share on other sites More sharing options...
MadTechie Posted February 5, 2010 Share Posted February 5, 2010 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] Quote Link to comment Share on other sites More sharing options...
piblondin Posted February 5, 2010 Author Share Posted February 5, 2010 Very cool--thanks much, MadTechie! I'm going to test it out tonight. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.