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:

name
email
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.

Tagged as: form data, gdata, Google, google api, google docs, Google Documents List Data API, google spreadsheet, PHP, spreadsheet, Zend, Zend Framework

{ 66 comments… read them below or add one }

Jeff June 3, 2009 at 3:35 pm: 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*)]‘;
A Reader June 4, 2009 at 1:20 am: Very very useful article. I’m gonna try this out! Thank you very much!
Dimas June 4, 2009 at 8:08 am: 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.
Jeff June 4, 2009 at 4:48 pm: 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.
dmon June 9, 2009 at 7:30 pm: 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.
Dimas June 9, 2009 at 8:26 pm: 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.
dmon June 9, 2009 at 8:42 pm: 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.
dmon June 10, 2009 at 8:21 pm: 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;
}
Dimas June 10, 2009 at 9:43 pm: Thanks dmon, I appreciate the code, I've made the addition.
dmon June 12, 2009 at 1:00 am: 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.
Dimas June 15, 2009 at 9:34 am: 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 …
dmon June 15, 2009 at 7:43 pm: 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!
nikita_m July 23, 2009 at 2:10 pm: 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.
BillyTheCzech October 20, 2009 at 9:01 am: 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?
sam November 10, 2009 at 1:22 pm: 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………
sam November 10, 2009 at 1:38 pm: i am going to attempt reinstalling wamp or xamp maybe that will fix it
Dimas November 10, 2009 at 1:51 pm: 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.
Alex November 11, 2009 at 9:39 am: 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!
Dimas November 11, 2009 at 10:08 am: 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!
4fthawaiian November 11, 2009 at 11:04 pm: Totally lovin’ you dude. Great post. Exactly what I wanted.
sam November 12, 2009 at 2:16 pm: 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….
sam November 12, 2009 at 2:17 pm: im currently useing
$ss->useWorksheet($_POST["site"]);
but that fails sometimes because of the worksheet not existing
Dimas November 12, 2009 at 2:26 pm: 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.
sam November 12, 2009 at 2:36 pm: alright well how about getting data from a specific cell such as
$cell = $ss->getCell(“B:5″)
can it do that?
Dimas November 12, 2009 at 2:47 pm: 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.
sam November 12, 2009 at 2:52 pm: ok thanks ill go check that out now
sam November 12, 2009 at 8:19 pm: ok umm i just setup my new vps and when i run the script i get this error

Parse error: syntax error, unexpected T_STRING in /home/sam2332/public_html/login.php on line 4

corresponding to this line
set_include_path(get_include_path() . PATH_SEPARATOR . “$_SERVER[DOCUMENT_ROOT]/ZendGdata-1.8.1/library”);

im not that good at php and any help you could supply would be great
sam November 16, 2009 at 10:56 pm: any idea?!?!?!?
still getting the error….
Dimas November 16, 2009 at 11:06 pm: Sam, sometimes PHP errors relate to the previous and/or next lines. Make sure nothing is wrong on the lines on and around line 4 …

Additionally, if you are having problems, try taking things “back to basics”, if you’ve integrated the code above into your own code, try creating a basic example using only the code above … get it working correctly, then start integrating it with your own code.
sam November 16, 2009 at 11:51 pm: it was a semicollen :/ lol

but now im gettin a ssl not installed error O.o (the problems never end)

http://www.sam2332.info/ZendGdata-1.9.5/demos/Zend/Gdata/InstallationChecker.php

i have also included the phpinfo() in the above link to show that openssl is in fact installed and enabled in my php.ini
this is starting to annoy me to death lmao
sam November 17, 2009 at 1:13 am: the error is as follows
Fatal error: Uncaught exception ‘Zend_Gdata_App_HttpException’ with message ‘Unable to Connect to ssl://www.google.com:443. Error #0: Unable to find the socket transport “ssl” – did you forget to enable it when you configured PHP?’

i could not find out how to fix it
im running centos and dont have much unix experience -_-
sorry for asking so many questions
Dimas November 17, 2009 at 7:26 am: Sam, please email me at dimas3 [at] farinspace [dot] com and I can help you further … or if you use IM you can contact me with AIM:junkyut or YahooIM:farinspace
sam November 17, 2009 at 12:44 pm: ok ill i.m. u
Paulo Bueno November 17, 2009 at 9:11 pm: Thank you so much!!!!

The $_SERVER[DOCUMENT_ROOT] save me 2 days of work. No other site have it related not even google cover this fundamental aspect of setting the include_path dinamically.

Best regards,
Paulo Bueno
Stephen Akins December 5, 2009 at 2:00 pm: Great article!

Here is an article that I thought some users may find useful:

http://stephenakins.blogspot.com/2009/04/google-docs-server-monitoring_8546.html

It uses a Google Spreadsheet and some PHP to create a Server Monitor. It doesn’t use the Zend GData Client Library, but fairly relevant none-the-less.
Chris January 14, 2010 at 1:16 pm: HI,

great code! The idea is brilliant, sadly I am having trouble getting it to work, when trying out the first example you are using.

Notice: Undefined variable: wk in C:\Programme\EasyPHP5.3.0\www\gdocs\Google_Spreadsheet.php on line 38

Fatal error: Uncaught exception ‘Zend_Gdata_App_Exception’ with message ‘A worksheet id must be provided for list queries.’ in C:\Programme\EasyPHP5.3.0\www\gdocs\library\Zend\Gdata\Spreadsheets\ListQuery.php:270 Stack trace: #0 C:\Programme\EasyPHP5.3.0\www\gdocs\library\Zend\Gdata\Spreadsheets.php(258): Zend_Gdata_Spreadsheets_ListQuery->getQueryUrl() #1 C:\Programme\EasyPHP5.3.0\www\gdocs\library\Zend\Gdata\Spreadsheets.php(332): Zend_Gdata_Spreadsheets->getListFeed(Object(Zend_Gdata_Spreadsheets_ListQuery)) #2 C:\Programme\EasyPHP5.3.0\www\gdocs\Google_Spreadsheet.php(62): Zend_Gdata_Spreadsheets->insertRow(Array, ‘tSzqciO8vStqxKh…’, false) #3 C:\Programme\EasyPHP5.3.0\www\gdocs\gtest.php(30): Google_Spreadsheet->addRow(Array) #4 {main} thrown in C:\Programme\EasyPHP5.3.0\www\gdocs\library\Zend\Gdata\Spreadsheets\ListQuery.php on line 270

Did I forget something?
Dimas January 15, 2010 at 11:00 am: Chris, good catch … a previous user had a similar problem, I’ve updated the helper class above, so download it again and replace your existing copy with the new one. Let me know if that clears it up for you…
Chris January 15, 2010 at 12:57 pm: HI Dimas,

thank you for your quick reply, but sadly the problem now is a different one:

Fatal error: Uncaught exception ‘Zend_Gdata_App_Exception’ with message ‘A worksheet id must be provided for list queries.’ in C:\Programme\EasyPHP5.3.0\www\gdocs\library\Zend\Gdata\Spreadsheets\ListQuery.php:270 Stack trace: #0 C:\Programme\EasyPHP5.3.0\www\gdocs\library\Zend\Gdata\Spreadsheets.php(258): Zend_Gdata_Spreadsheets_ListQuery->getQueryUrl() #1 C:\Programme\EasyPHP5.3.0\www\gdocs\library\Zend\Gdata\Spreadsheets.php(332): Zend_Gdata_Spreadsheets->getListFeed(Object(Zend_Gdata_Spreadsheets_ListQuery)) #2 C:\Programme\EasyPHP5.3.0\www\gdocs\Google_Spreadsheet.php(62): Zend_Gdata_Spreadsheets->insertRow(Array, ‘tSzqciO8vStqxKh…’, false) #3 C:\Programme\EasyPHP5.3.0\www\gdocs\gtest.php(24): Google_Spreadsheet->addRow(Array) #4 {main} thrown in C:\Programme\EasyPHP5.3.0\www\gdocs\library\Zend\Gdata\Spreadsheets\ListQuery.php on line 270

I tried going throu the code but could not clear things….
Dimas January 15, 2010 at 1:10 pm: Please email me at dimas3 [at] farinspace [dot] com and I can help you further … or if you use IM you can contact me with AIM:junkyut or YahooIM:farinspace
Mychal January 22, 2010 at 5:24 pm: I getting the following error in my script:

Warning: require_once(Zend/Http/Response/Stream.php) [function.require-once]: failed to open stream: No such file or directory in /nfs/c05/h02/mnt/73236/domains/srslawfirm.com/html/ZendGdata-1.9.7/library/Zend/Http/Client.php on line 50

Fatal error: require_once() [function.require]: Failed opening required ‘Zend/Http/Response/Stream.php’ (include_path=’.:/usr/local/php-5.2.6-1/share/pear:/home/73236/domains/srslawfirm.com/html/ZendGdata-1.9.7/library/’) in /nfs/c05/h02/mnt/73236/domains/srslawfirm.com/html/ZendGdata-1.9.7/library/Zend/Http/Client.php on line 50

And I looked and there is no /response/stream.php in the zend framework file. Any suggestions?

Thanks,
Mychal
Dimas January 24, 2010 at 8:40 pm: Mychal, go to Zend and grab the latest Framework files, look in “library/Zend/Http/Response”, you should see “Stream.php”, make sure your library has the latest files … hope that helps …

http://framework.zend.com/download/latest
Andrew February 10, 2010 at 7:57 pm: Dimas, thanks so much for this. Worked perfectly first time on my hosted site. Tried it on my local XAMPPinstallation and got a similar SSL error to @Sam. Maybe my XAMPP installation is missing something???
Fiifi Baidoo February 20, 2010 at 5:01 pm: Nice tutorial. You can use a cron to be pulling the data into you desired database like mysql and do all your OUT (SQL Select queries) operations
there instead. That is the procedure I used to pull data from a Google Spreadsheet using Dimas’s method. I first of all pull the data and then flag it with an update statement in the Spreadsheet, I do likewise in my mysql database so that when my cron runs the next time, it does not pick that data again unless there was an update on that particular row in the spreadsheet.
Tom February 22, 2010 at 4:02 pm: Hi,
Great tutorial!
I do encounter the below error msg. It talks about getting a response code 400, but I get this msg always and I can access the spreadsheet with a browser.
Fatal error: Uncaught exception ‘Zend_Gdata_App_HttpException’ with message ‘Expected response code 200, got 400 We're sorry, a server error occurred. Please wait a bit and try again.’ in /home/folder/public_html/ZendGdata-1.10.1/library/Zend/Gdata/App.php:700 Stack trace: #0 /home/folder/public_html/ZendGdata-1.10.1/library/Zend/Gdata.php(219): Zend_Gdata_App->performHttpRequest(‘POST’, ‘http://spreadsh…’, Array, ‘performHttpRequest(‘POST’, ‘http://spreadsh…’, Array, ‘post(‘insertEntry(‘<atom:entry xml…', 'http://spreadsh…', 'Zend_Gdata_Spre…') #4 /h in /home/folder/public_html/ZendGdata-1.10.1/library/Zend/Gdata/App.php on line 700

Thanks for any help!
Tom.
caesar February 25, 2010 at 10:45 pm: I had Zend../App.php and the HttpException response code errors and found that Google Spreadsheets does not like UPPERCASE column names and column names with _ underscores. Once I changed all my column names to lowercase and took out underscores, it worked. Go figure. I actually think UPPERCASE and underscores specifically effect the google XML which looks like what’s going on in your error.
caesar February 25, 2010 at 10:51 pm: sorry, I posted and didn’t even mention the tutorial. I’m still blown away I guess. Great, great work. This is going to be sooooo much fun playing with this code.
Tom February 26, 2010 at 4:08 am: Can’t say this is the case here as I was only using the below test code I took from the above example.
Tracing the problem with “echo” commands shows the code gets stuck in the first login made in the class constructor when executing the first line “$ss = new Google_Spreadsheet($u,$p);”

$ss = new Google_Spreadsheet($u,$p);
$ss->useSpreadsheet(“test1″);
// if not setting worksheet, “Sheet1″ is assumed
$ss->useWorksheet(“Sheet1″);
$row = array
(
“name” => “John Doe”
, “email” => “john@example.com”
, “comments” => “Hello world”
);
echo “working…”;
if ($ss->addRow($row)) echo “Form data successfully stored using Google Spreadsheet”;
else echo “Error, unable to store spreadsheet data”;
caesar February 26, 2010 at 6:40 am: Definitely something wrong with your HttpClient getting set. First, I’d make sure the spreadsheet is being shared. You need to share it and get a url for it like you’re going to share it, but just don’t send the url to anyone. I’m not sure how necessary that is, but then that would mimic my setup.

dimas wrote:
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

dmon wrote:

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.
caesar February 28, 2010 at 10:28 am: So, I’ve been messing around with writing custom forms to make viewing and editting my google spreadsheets easier. However, I’m now running into query issues cause it seems I can’t swap worksheets with a single resource I get a 500 internal server error response from google. So, I tried to just make a new variable say $rr instead of $ss for the new class object, but that didn’t work. How do I close the feed so I can open a new one to a different spreadsheet or workbook? I essentially need to search for data in one worksheet and then use that data to pull a matching record on another worksheet. These datasets are in the same spreadsheet so I’ve been only trying to swap worksheets but I assume I’ll run into the same error if I tried to swap spreadsheets. Thanks for any help and suggestions.
Tom March 6, 2010 at 12:22 pm: Thanks, but the share trick did not help. Still get the same error.
u/p are correct.
Indeed something wrong with HttpClient, but what is it???…
Johnny March 12, 2010 at 6:46 pm: Great Tutorial Dimas, thanks a lot! I tried the first two examples and everything was great. I still cant get the read row to function, it gives me the same error as Tom ..App.php on line 700.
I did install the newest Version of Zend, could that be the actual Problem i use 1.10.2.

Thanks a lot for your help and the great work!
Dimas March 12, 2010 at 8:18 pm: Johnny, to be honest … I have not been able to reproduce the errors that some have been experiencing, I’ve tried a few times, but everything seems to be working. If I could, then I would be able to figure out what it is that is going on.

One piece of advise is to do as the examples shows, then start to deviate with what you want to do (e.g. custom field names, using worksheets, etc)
Geoff March 19, 2010 at 8:07 am: Thanks for this post.

My problem is that some of my cells contain formulas. When updating a row, this class overwrites formulas with their current (evaluated) value, destroying the formula. Is there any way to address this?

Thanks again.
Dan April 25, 2010 at 6:49 am: So… no OAuth, or AuthSub? Every time I read the docs about this, I was convinced that the ‘installed client’ authorization was all that was really needed for _posting_ a form…

Great post, great examples. I am definitely going to give this a hack.

Thanks!
Erik May 27, 2010 at 5:31 pm: Thanks for the script, it works nicely. Took me a while to get the search right (I didn’t think there were spaces in my search) but for the rest it’s pretty straightforward. The script takes a lot of work out of hands and the code is very readable!
Mark July 5, 2010 at 8:01 am: Dimas,
Thanks so much for this -was able to get it up and running very quickly.
One refinement I’d like to make is to check that the column headings in the spreadsheet are still what I was expecting. (check nobody has changed them in the doc)
To do this I’d like to read the first row of the spreadsheet (to discover the column headings).
I can see how to get all rows, and then I can just take the first one, but when the spreadsheet is full, that’s fairly inefficien, if I just want the first row. I got lost when I looked at the Zendata code under yours. Have you got a code snippet I could use?
getColumnNames returning an array of names might be quite a handy addition to your code for many people for the same reason I want it

Cheers, and thanks for sharing this very useful code.
Dimas July 5, 2010 at 12:18 pm: Mark, I’ve updated the class with a getColumnNames() method. It returns an array of the column names. The column names are returned “clean”, meaning Google will strip disallowed characters in the column names, but this should still allow you to check for expected fields as needed.
Dimas July 5, 2010 at 12:48 pm: Geoff, I don’t have a solution for you. My initial tests inserting =1+1 work … the spreadsheet shows the evaluated value, but the underlying formula is still present.

The problem I am having is during insert, knowing which row number is being currently worked on to the able to write a proper formula, =B14+C14 … I don’t know enough about spreadsheet formulas to know if there is a SELF variable that I can use, such as =B{row}+C{row}
Mark July 5, 2010 at 1:00 pm: Wow,
What a fantastic response!. That is brilliant.
I’ve tried it out and it works perfectly (I just had to strip out the punctuation characters in my column names when I did the matching, as you alerted me to).
Thanks so much!
Mark July 8, 2010 at 2:35 am: This latter part is the same problem I’m working on I think.
I add a row to a worksheet “feed”, and have another sheet “report” that mirrors the feed row for row, but uses formulas to make new columns.
For example:
The “feed” has a score in column A, and report has in cell A2
=if(feed!a2 >5, “Good”,”Bad”)
So the problem is when I add a row to “feed”, I also need to add a row to “report”, but I don’t know what row number to use in the formula.
How do I find out what row number the addRow will add to?
I tried pre-filling the formulas in “Report”, but because I’m adding a row to “Feed”, that’s inserting a row, and so the formulas in “Report” then have their row numbers increased by one and so are wrong.
I’ve thought about using update row instead of add, or adding a row number column, but those approaches seem a bit clunky.

Anyone got a nice solution to this (like being able to get the current row number)?

Mark

, and adding new columns using formulas.links to that data. They do not update
Mark July 8, 2010 at 3:51 am: The answer I found to my problem above is to use row() to get the current row, so the formulas for every row can be the same.
=if(index(feed!a:a,row()) >5, “Good”,”Bad”)
Dimas July 8, 2010 at 8:44 am: Mark, thanks for the tips, this has me moving in the right direction, however doing simple things like: =INDEX(B:B,ROW())+INDEX(C:C,ROW()) still seem to produce unexpected results. The weird thing is that when I use the google docs UI and enter in the same formula it works.
Jackson July 11, 2010 at 4:37 am: Dear Dimas,
Thank you for the code and tutorial you wrote, clearly explained!

Regards,
Jackson
Ramakant Yadav July 19, 2010 at 11:36 pm: Hi
Awesome work done by you.
your Google spreadsheet class help me a lot.
thanks for great work, keep it up.

Thanks and Regards
Ramakant Yadav
Joe Engel July 30, 2010 at 5:59 pm: Totally rad dude. So much easier than programming to the Zend/Google APIs.

I had the error:
‘A worksheet id must be provided for list queries.’
and fixed it by changing the default sheet name from “Sheet1″ to “Sheet 1″ at line 34.

Thanks so much!
Mike August 6, 2010 at 8:21 am: I export my spreadsheets from Google Docs using CSV files this way

Follow the Latest Content With...

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

Download

Related posts

About Dimas

WordPress Meta Box PHP Class

Latest

Comments

Topics

Recommended

Follow the Latest Content With...

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

Download

Related posts

About Dimas

WordPress Meta Box PHP Class

Popular

Latest

Comments

Topics

Recommended