Saving Form Data to Google Spreadsheets Using PHP and the Google Docs API

The general idea is to read a Google Spreadsheet through PHP and save user submitted form data via the Google Documents List Data API. By doing this, you can quickly view all the submissions at a glance and you are also able to export CSV files of the data. Using Google Docs gives you and your clients a quick and easy interface to interact with form data.

I’ve written a small PHP helper class to assist with the whole process (PHP5). You are going to need the following:

• A working copy of the Zend GData Client Library (1.0.3+)
• A copy of the PHP Google Spreadsheet Helper Class
• A Google account, in order to be able to create a spreadsheet document using Google Docs.

First you should login to Google Docs using your existing Google account. Once logged in you will want to create a new spreadsheet document, you will be immediately taken to a spreadsheet interface. Lets start off by creating some column field names on row #1:

1. name
2. email
3. comments

Make sure to save your spreadsheet and give it a name, you will need to use the spreadsheet name in the code below.

The following is a basic example of using the Google_Spreadsheet PHP helper class.

<?php

// Zend library include path
set_include_path(get_include_path() . PATH_SEPARATOR . "$_SERVER[DOCUMENT_ROOT]/ZendGdata-1.8.1/library");

include_once("Google_Spreadsheet.php");

$u = "username@gmail.com";
$p = "password";

$ss = new Google_Spreadsheet($u,$p);
$ss->useSpreadsheet("My Spreadsheet");

// if not setting worksheet, "Sheet1" is assumed
// $ss->useWorksheet("worksheetName");

$row = array
(
	"name" => "John Doe"
	, "email" => "john@example.com"
	, "comments" => "Hello world"
);

if ($ss->addRow($row)) echo "Form data successfully stored using Google Spreadsheet";
else echo "Error, unable to store spreadsheet data";

?>

In this next example, I’ve added an additional column to the spreadsheet (id). The id column is used as a unique identifier in order to be able to update the row at a later point; you can use any column as the identifier, such as the email column.

<?php

// Zend library include path
set_include_path(get_include_path() . PATH_SEPARATOR . "$_SERVER[DOCUMENT_ROOT]/ZendGdata-1.8.1/library");

include_once("Google_Spreadsheet.php");

$u = "username@gmail.com";
$p = "password";

$ss = new Google_Spreadsheet($u,$p);
$ss->useSpreadsheet("My Spreadsheet");
$ss->useWorksheet("wks2");

// important:
// adding a leading alpha char prevents errors, there are issues
// when trying to lookup an identifier in a column where the
// value starts with both alpha and numeric characters, using a
// leading alpha character causes the column and its values to be
// seen as a strictly a strings/text

$id = "z" . md5(microtime(true));

$row = array
(
	"id" => $id // used for later lookups
	, "name" => "John Doe"
	, "email" => "john@example.com"
	, "comments" => "Hello world"
);

if ($ss->addRow($row)) echo "Form data successfully stored";
else echo "Error, unable to store data";

$row = array
(
	"name" => "John Q Doe"
);

if ($ss->updateRow($row,"id=".$id)) echo "Form data successfully updated";
else echo "Error, unable to update spreadsheet data";

?>

If you need to get row data …

<?php

// Zend library include path
set_include_path(get_include_path() . PATH_SEPARATOR . "$_SERVER[DOCUMENT_ROOT]/ZendGdata-1.8.1/library");

include_once("Google_Spreadsheet.php");

$u = "username@gmail.com";
$p = "password";

$ss = new Google_Spreadsheet($u,$p);
$ss->useSpreadsheet("My Spreadsheet");
$ss->useWorksheet("wks2");

$rows = $ss->getRows("id=zd92cd4a8f7a001c343a5144ad3570668");

if ($rows) print_r($rows);
else echo "Error, unable to get spreadsheet data";

// double quotes must be used for values with spaces
$rows = $ss->getRows('name="John Doe"');

if ($rows) print_r($rows);
else echo "Error, unable to get spreadsheet data";

?>

If you need to delete row data (courtesy of dmon) …

<?php

// Zend library include path
set_include_path(get_include_path() . PATH_SEPARATOR . "$_SERVER[DOCUMENT_ROOT]/ZendGdata-1.8.1/library");

include_once("Google_Spreadsheet.php");

$u = "username@gmail.com";
$p = "password";

$ss = new Google_Spreadsheet($u,$p);
$ss->useSpreadsheet("My Spreadsheet");

// if not setting worksheet, "Sheet1" is assumed
// $ss->useWorksheet("worksheetName");

try
{
	if ($ss->deleteRow('name="John Q Doe"')) echo "Form data successfully deleted";
	else echo "Error, unable to delete data";
}
catch (Exception $e)
{
	echo $e->getMessage();
}

?>

Download

Google Spreadsheet PHP Helper Class, this project is on github.