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:
- name
- 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.
Awesome work! I found your helper class very useful.
Is there a way for someone to write a query that contains a regular expression or has some sort of “partial match” operator. See the SQL query below:
SELECT * FROM database WHERE name LIKE ‘%Doe%’
$myQuery = ‘name = [RE:(*Doe*)]‘;
Very very useful article. I’m gonna try this out! Thank you very much!
Jeff, a “LIKE” operator would be nice … I have not been able to find anything about it in the Google documentation. I assume you have already seen this:
http://code.google.com/apis/spreadsheets/docs/2…
As a side note … using Google Spreadsheets for anything more complex is probably not recommended, I've had performance issues with larger data sets, primarily when retrieving data and like you, trying to filter that data.
Using the API is very useful for IN operations, like in the case of storing form data.
Your class was so well written that it was little effort to extend it to perform a partial match search exploiting your class methods and a brute-force use of PHP stristr( ) function.
Your point is well taken on complexity. I have been thinking about doing some EC2 cloud stuff, but using the Google spreadsheet is really refreshing and unexpected approach to databasing on small jobs.
Thanks again for your article and code tips.
Very simple and easy to use. For a while I was having a hard time making it work. First I had to uncomment the openssl enable in php.ini. Then still didn't work when I realized that I had to provide the correct username, password and document title. Then it worked! Silly me.
Anyways, your helper class was designed for PHP 5. Is it possible to convert it to PHP 4 so as to make it work in our server? thanks.
dmon, I've been there before, sometimes you're just stuck with PHP4 on a given host (and of course you have to make the best of it).
Anythings possible … to convert, I'm guessing, use CURL and PHP4 to translate the XML responses, the helper class is limited and specific, so it probably wouldn't be difficult to figure out someway to port the different methods … with that said, unfortunately, I don't have any plans on doing a PHP4 conversion, sorry.
I have read the Zend framework document and it is specified there taht it requires PHP 5 to work.
Yes, I've tried XML responses, if I just want to read them. But to add data or change something, I guess there's no other way through it.
Well, the only thing to do is to either upgrade to PHP5 or use a 3rd party site.
Thanks for your help, I really appreciate the simplicity in it.
I noticed that there was no delete function in the helper class you developed. I tried to edit your helper class and basing my code from your updaterow function, I created a deleteRow function. Just want to share to everyone
add this function in the google spreadsheet helper class:
function deleteRow($search)
{
if ($this->client instanceof Zend_Gdata_Spreadsheets AND $search)
{
$feed = $this->findRows($search);
if ($feed->entries)
{
foreach($feed->entries as $entry)
{
if ($entry instanceof Zend_Gdata_Spreadsheets_ListEntry)
{
$this->client->deleteRow($entry);
if ( ! ($entry instanceof Zend_Gdata_Spreadsheets_ListEntry)) return FALSE;
}
}
return TRUE;
}
}
return FALSE;
}
Thanks dmon, I appreciate the code, I've made the addition.
hi, it's me again.
Just want to ask if is it possible in your updateRow function to specify a certain row using multiple conditions? In your example you only used one condition which was the ID.
thanks again.
On line 197: if ($search) $query->setSpreadsheetQuery($search);
if you change it to: if ($search) $query->setQuery($search);
this will basically allow you to do an open query vs a structured query … see: http://code.google.com/apis/gdata/docs/2.0/refe… (the “q” parameter)
If you make that change you'll be able to do queries like the following:
$rows = $ss->getRows('John'); // partial match on “John Doe”
$rows = $ss->getRows('example.com -dennis'); // contains examp.com but not dennis
I'll be updating the class later tonight with some of these new insights …
Oh thanks. But I think I hadn't made my question clear.
Let's say there are 3 John Doe's in your table and you just want to search for the one with an email of iam@johndoe.com
Anyways, I have found the solution. You'll just have to use “AND” in your query.
Ex:
$rows = $ss->getRows('name=”John Doe” AND email=”iam@johndoe.com” ');
Thanks again. Keep it up!
Thank you, very good tool.
One strange thing:
try to update row by new value:
$row = array
(
“name” => “=sum(F:F)”
);
Open spreadsheet and see #NAME? and explanation – unknown range name F
If I copypaste cell value to another cell, I get correct calculation.
Kindly advise what could be.
Hi i am trying to add row to spreadsheet,
however it is not working, anytime i try to add row… occures error
PHP DOMException: Namespace Error in
/srv/www/htdocs/magento/lib/Zend/Gdata/App/Base.php on line 211
i was tracking this and found this:
DOMDocument->createElementNS('http://schemas....', 'gsx:0')
can anybody help me?
ok here it goes ive had problems from the start with this and it only worked ever so often here are the errors
Warning: require_once(Zend/Loader.php) [function.require-once]: failed to open stream: No such file or directory in C:\wamp\www\test\Google_Spreadsheet.php on line 172
Fatal error: require_once() [function.require]: Failed opening required ‘Zend/Loader.php’ (include_path=’.;C:\php5\pear;C:/wamp/www//ZendGdata-1.8.1/library’) in C:\wamp\www\test\Google_Spreadsheet.php on line 172
mind u i just downloaded it straight from the site and everything
please help………
i am going to attempt reinstalling wamp or xamp maybe that will fix it
Sam,
Make sure that you also have the supporting libraries … you will need the Zend GData library. I provide the links above to get this … depending on where you put the library files you may have to change the path to the files includes. You are getting the errors because PHP can not find the supporting files to properly include them.
Hey!
So, I installed the libraries, ran the first php example you put up, and I got this error message:
Fatal error: Uncaught exception ‘Zend_Gdata_App_Exception’ with message ‘A spreadsheet key must be provided for worksheet document queries.’
any idea what’s causing this? thanks!
Hi Alex, by the looks of the error, it seems like there is something wrong with the spreadsheet connectivity …
1) Make sure you use a proper username and password for your own Google account in the code example.
2) Make sure that you indeed have created a spreadsheet on google docs and gave it a name. Also confirm that you are using the correct name of the spreadsheet in the code example.
3) Also make sure that you have created the fields on row #1 as per the example: name, email, comments
Alex, saw both of your sites … awesome stuff!
Totally lovin’ you dude. Great post. Exactly what I wanted.
ok i found out why my problem it was my free host they blocked files bigger then 500 kb lol
and another question i have multiple sites i need this script to input post data in the spread sheet and i need to know how to get a list of worksheets and if it doesn exist create one….
im currently useing
$ss->useWorksheet($_POST["site"]);
but that fails sometimes because of the worksheet not existing
Sam, currently the class is very basic … it’s a quick way for a programmer like yourself to setup a custom data capture.
My recommendation to you is to create the spreadsheet and all the worksheets that you need before hand, then manage the list of worksheets in your script with an array or similar.
Another idea is to create a new field ‘website’ and log the website that the request came from. You then will have a record of the request and you will be able to sort your data accordingly.
alright well how about getting data from a specific cell such as
$cell = $ss->getCell(“B:5″)
can it do that?
Sam, great questions!
There is no current “getCell” implementation, however it probably could be added. Do take a look at the PHP class source itself and you can see what is available, additionally, take a look at the Zend GData documents. My class provides a simple wrapper for the Google and Zend interactions, you’ll get a basic idea of how the current functionality functions and you should be able to extend it further for your needs.
Additionally, take a look at the “getRow” example above. Traditionally I am of a relational DB background, hence my example above uses a ID to identify a row.