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.
@Abbas, you can, but unfortunately not with this class, you should look into the Google Spreadsheet API itself.
Thanks for all the help. I really appreciate it
Hey Dimas,
Great helper class, I’m having all kinds of ideas for using this.
One question:
If we wanted to specify rows by more than one parameter, how is this done?
i.e. let’s say I have a spreadsheet with the columns “campaign”, “action”, and “total”. My script gets the campaign value and the action value and I want to increment the total column for the row that matches BOTH campaign and action columns.
So how do I specify, in the getRows and updateRows functions, that I only want the row that matches both?
hello dimas,
thx for api!
but… i found a bug,
your api only work when the worksheet name is the same of spreadsheet, ex:
it works:
$ss->useWorksheet(“Hillodb”);
$ss->useSpreadsheet(“Hillodb”);
it not works:
$ss1->useWorksheet(“Hillodb”);
$ss1->useSpreadsheet(“Aprovadas”);
Error:
Uncaught exception ‘Zend_Gdata_App_Exception’ with message ‘A spreadsheet key must be provided for list queries.’ in /home/content/22/3917722/html/dann/hillo/canvas/Zend/Gdata/Spreadsheets/ListQuery.php:264
why?
can u helpme?
thx in advance.
I want to find the worksheet Id from spreadsheet itself. Any Way in PHP?
I want to retrieve last worksheet Id from a spreadsheet .
I manually give worksheet Id’s as ‘od6′,’od7′,’od4′ , then column values can retrieved ,but after that I cant get anymore… because no worksheet id .
How will I find worksheet id?
Code:
$spreadsheetKey = ” “;//key of spreadsheet
$query = new Zend_Gdata_Spreadsheets_DocumentQuery();
$query->setSpreadsheetKey($spreadsheetKey);
$feed1 = $spreadsheetService->getWorksheetFeed($query);
$worksheetId = ‘od6′;
$query = new Zend_Gdata_Spreadsheets_ListQuery();
$query->setSpreadsheetKey($spreadsheetKey);
$query->setWorksheetId($worksheetId);
$listFeed = $spreadsheetService->getListFeed($query);
$customEntry = $listFeed->entries[1]->getCustomByName(‘stay’);
$customEntry->getColumnName() . ” = ” . $customEntry->getText();
Greetings,
Does anyone knows how to add columns?
Thanks in advanced for your help.
heloo guru,
I am taking data from google spread sheet and shown it to web page now i wana add quick search option for adding quick search i need html of spread sheet how can i get html of spread sheet or any other solution for adding quick search.
thanx in advance
Dear Sir,
You are awesome.
That is all.
I have a google form for collecting the customer visit details of each sales person. I wish to have separate spread sheet for each sales person upon selection of sales person name from the list ( google form) the data must go to the particular sheet. can any one tel me how to do this
I really liked the new version…
btw, the heigh of each row might be seen high for you, if this happens just change from the options.
Hello Dimas, thanks for sharing this. I’ve got a question: how do we get all rows in a specific column?
Thanks.
Hola excelente el ejemplo, mira tengo un problema, en mi casa me funciona normal el ejemplo, pero en mi trabajo me sale error 500.
No se a que se pueda deber .. pero en mi trabajo tengo proxy, no sé si tenga que ver eso.
Espero alguna respuesta, gracias.
Does this need features like curl, fopen etc to be enabled on my hosting server or that is not required?
Hello Sir/Maam,
I need some help in a problem I am having with spanish special characters. A brief summary of what I am doing first. I have written a PHP script which fetches data from Google Spreadsheet using Zend Google PHP API. This data is then put into MySql database.
$row=$Col->getText(); //getText is the Google API that returns the value in the row.
print “$row”; // I print the content
But the problem is that some spanish characters such as ñer appear as ñer. Later on in the PHP code I go on to save these values in MySql. And even in the tables the same problem.Don’t know where the problem is.
I tried various different things such as edit the PHP file and add:
mbstring.language = Neutral ; Set default language to Neutral(UTF-8) (default)
mbstring.internal_encoding = UTF-8 ; Set default internal encoding to UTF-8
mbstring.encoding_translation = On ; HTTP input encoding translation is enabled
mbstring.http_input = auto ; Set HTTP input character set dectection to auto
mbstring.http_output = UTF-8 ; Set HTTP output encoding to UTF-8
mbstring.detect_order = auto ; Set default character encoding detection order to auto
mbstring.substitute_character = none ; Do not print invalid characters
default_charset = UTF-8 ; Default character set for auto content type header
mbstring.func_overload = 7 ; All non-multibyte-safe functions are overloaded with the mbstring alternatives
Added in the below in MySql
init_connect=’SET collation_connection = utf8_unicode_ci; SET NAMES utf8;’
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
Changed Database and Table properties
ALTER DATABASE db_name
CHARACTER SET utf8
DEFAULT CHARACTER SET utf8
COLLATE utf8_general_ci
DEFAULT COLLATE utf8_general_ci
;
ALTER TABLE tbl_name
DEFAULT CHARACTER SET utf8
COLLATE utf8_general_ci
;
Call SETNAMES etc just after mysql open connection.
$q=”SET NAMES ‘utf8′”;
$r=mysql_query($q);
mysql_query(“SET CHARACTER SET utf8″);
But nothing seems to work. Please help
I’m having a problem trying your solution, but i’m having a problem with zend gdata, would you be able to help me out please?
Here’s the error:
Fatal error: Uncaught exception ‘Zend_Gdata_App_HttpException’ with message ‘Expected response code 200, got 400 We're sorry, a server error occurred….
I was able to integrate your solution like a charm. Thanks a zillion.
I have created a general purpose form that has a feature to save submitted data into Google Spreadsheet, that is available for sale on CodeCanyon.
Let me know if you have any licensing issues with it?
I had a quick read through the posts here and noticed some were having the same issues as I did when I set it up.
You have to have the extension openssl enabled in your php.ini file.
You also have to have magic_quotes_gpc turned off.
You also have to place the Zend folder in the root.
And correct the path to the Zend Library, the numbers are for a later version now.
Hope that helps.
I am trying to send data from phpmyadmin database to google spreadsheet. How can i send this data using zend framework.
Can anyone help.
thanks
Thanks for this master piece of Google Spreadsheet code. It saved my hours of work.
I would like to exchange data from my android code with google spreadsheet thro’ ofcourse java. Can any body suggest…
I recently visited a script i produced using your class and now it does not work anymore. I cannot seem to login.
Anyone having the same problem?
I am getting this error ‘Fatal error: Cannot redeclare class Zend_Loader in’ and have been advised to change a file in the application.ini file – could anybody explain howw to do this?
Is the library using any reference to Google Spreadsheets API v1 or v2? If it is the case, the code won’t work from October 20th (see https://developers.google.com/google-apps/spreadsheets/)
Thanks for this amazing code, saved to me tons of hours!
$service = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME;
$http = Zend_Gdata_ClientLogin::getHttpClient($user,$pass,$service);
$this->client = new Zend_Gdata_Spreadsheets($http);
This is Really nice, is there any way to format the rows. Namely, I want to change the background color of the row.