Google - Spreadsheets API php Zend Tutorial

So you want to customize your google forms and spreadsheet but you do not understand the Google API instructions? Welcome to my club =)

The advantage of using the API instead of PHP CURL is that you do not have to make your spreadhseet data public so it is more secure.

After a few hours I was finally able to piece together everything I needed to get started with the Google API and hopefully I can save you painstaking hours of hair pulling trying to get code that will work on your site.

To get the Google API to work with PHP you need the Zend Framework installed. Don't hypervenilate yet, it's not that difficult.

For detailed instructions on getting the Google API to work with Zend I highly recommend checking out this site.

Google API ZEND PHP Getting Started Tutorial

Now ASSUMING you have zend and php working then congratulations, the difficult part is done!

Let us start with displaying all the data in your spreadsheet

//----------------------------------------------------
// Google Spreadsheets API PHP Tutorial
// Created by Jaz Witham (Jazzerup)
// 2011
// THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND
// Script allows you to connect to a google spreadsheet via
// the google php zend api
//----------------------------------------------------
 
// Gmail email address and password for google spreadsheet
$user = "
 This e-mail address is being protected from spambots. You need JavaScript enabled to view it.
 ";
$pass = "your email password";
 
// Google Spreadsheet ID (You can get it from the URL when you view the spreadsheet)
$GSheetID = "0AqPXsH_13gn4dGsxbVZpc1JvYWJZd3JjWGxxbXlSSnc";
 
// od6 is the first worksheet in the spreadsheet
$worksheetID="od6";
 
// Include the loader and Google API classes for spreadsheets
require_once('Zend/Loader.php');
Zend_Loader::loadClass('Zend_Gdata');
Zend_Loader::loadClass('Zend_Gdata_ClientLogin');
Zend_Loader::loadClass('Zend_Gdata_Spreadsheets');
Zend_Loader::loadClass('Zend_Http_Client');
 
// Authenticate on Google Docs and create a Zend_Gdata_Spreadsheets object.            
$service = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME;
$client = Zend_Gdata_ClientLogin::getHttpClient($user, $pass, $service);
$spreadsheetService = new Zend_Gdata_Spreadsheets($client);
 
// The function will echo out a list of spreadsheets in your account            
function DisplaySpreadsheets($feed)
{
    $i = 0;
    foreach($feed->entries as $entry) {
        if ($entry instanceof Zend_Gdata_Spreadsheets_CellEntry) {
        echo $entry->title->text .' '. $entry->content->text . "
";
    } else if ($entry instanceof Zend_Gdata_Spreadsheets_ListEntry) {
        echo $i .' '. $entry->title->text .' '. $entry->content->text . "
";
    } else {
        echo $i .' '. $entry->title->text . "
";
    }
    $i++;
    }
}
 
//The function will create a table of the data from your spreadsheet  
function DisplayWorksheetData($feed)
{
    echo ''; $rowData = $feed->entries[0]->getCustom(); echo ""; echo ''; foreach($rowData as $customEntry) { echo ""; } echo ""; $i = 2; // the first row of content is row 2 foreach($feed->entries as $row) { echo ""; $i++; $rowData = $row->getCustom(); foreach($rowData as $customEntry) { echo ""; } echo ""; } echo '
<table style="width: 200px;" border="1">
<tbody>
<tr>
<td>Row Number</td>
<td>" . $customEntry->getColumnName() . "</td>
</tr>
<tr>
<td>" . "Row " . $i .' '. $row->title->text . "</td>
<td>" . $customEntry->getText(). "</td>
</tr>
</tbody>
</table>
';
}
 
// First echo a list of your Google Spreadsheets
 
$feed = $spreadsheetService->getSpreadsheetFeed();
echo "
<h1>List of all your Spreadsheets</h1>
";
DisplaySpreadsheets($feed);
 
// echo the content of a specific Spreadsheet/Worksheet
// set a query to return a worksheet and echo the contents of the worksheet
 
$query = new Zend_Gdata_Spreadsheets_ListQuery();
$query->setSpreadsheetKey($GSheetID);
$query->setWorksheetId($worksheetID);
$listFeed = $spreadsheetService->getListFeed($query);
 
echo "
 
";
echo "
<h1>List of data in your Spreadsheet</h1>
";
DisplayWorksheetData($listFeed);