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.
ok thanks ill go check that out now 😀
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 😀
any idea?!?!?!?
still getting the error….
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.
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
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
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
ok ill i.m. u
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
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.
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?
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…
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….
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
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
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
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???
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.
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.
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.
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.
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”;
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.
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.
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???…
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!
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)
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.
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!
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!
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.
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.Geoff, I don’t have a solution for you. My initial tests inserting
=1+1work … 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}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!
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
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”)
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.Dear Dimas,
Thank you for the code and tutorial you wrote, clearly explained!
Regards,
Jackson
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
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!
I export my spreadsheets from Google Docs using CSV files this way
Hi Dimas,
Wonderful post, worked like a charm.
However had query, is it possible to specify the number of rows that the “getRows” function is to return?
My problem is the file that I am trying to read has about 2000 rows, and it gives the “Fatal error: Allowed memory size…”, I believe the solution to this to restrict the number rows that is returned.
Is it possible to do so?
Regards
Subha
Hi, i saw that the Google_Spreadsheet.php had a getColumnNames() function. How do i get the names of the headers of the rows using this function? I’m sorry, I’m new at this. 🙂 please help. 🙂
ok, here’s the thing.
when i try getting all the data from a certain row, it prints this
Array ( [0] => Array ( [column text1] => rowdata1 [column text2] => rowdata2 [column text3] => rowdata3 ) )
can you please teach me how to get the column text and it’s corresponding data without it having to be in an array? like placing it in a variable maybe?
i’m planning on placing it in a table in this form:
column name 1 | column name 2| column name 3|
data | data | data |
please, please, please help! 🙂
I am having the same problem as TOM was having. Did anyone ever find a way to fix it? Doesn’t seem to get past the:
$ss = new Google_Spreadsheet($u,$p);
Thanks!
I think i may have found the problem. It seems to be the workings of the Zend Loader.php file. It seems to not pass this part of the login code:
if(!@file_exists(‘Zend/Loader.php’) ) {
echo ‘can not include’;
} else {
require_once(‘Zend/Loader.php’);
}
It keeps showing “can not include” but i am able to move to the file by using the normal http://www.mysite.com/blah/ZendGdata-1.10.8/library/Zend/Loader.php (although its just a blank page)
Anyone know a fix for not being able to find the Loader.php file?
for those getting the formula #name? or namespace error look here
http://lifeinzeroone.tumblr.com/post/1177909602/zend-gdata-api-spreadsheet-add-formula
@David
Your include path is wrong
Place this on top of your script to see the errors generated, also the only line required is
require_once(‘Zend/Loader.php’);
I’m trying to use your helper to add and/or update many rows (right now there are 140). I can see the spreadsheet being updated with the new information but the script stops before completion. The server error states that there was a premature end of script.
Basically, my app is pulling data from a mysql database and then using the db array, each row is added/updated to the spreadsheet.
Any ideas how to speed things up or prevent the error?
Pangulo, this is a problem I have also had with the class, the combination of using Google API/Zend Gdata all seem to be really slow … I would actually like to try to only use CURL for the requests and do away completely with the need for Zend Gdata and see if I can get any speed gains that way.
After extend use of the class, I have concluded that it definitely needs to be sped up and that it is best suited for inserts, additionally very large spreadsheets will run into time issues as well.
I have the project on github and I will try to update it when I have some spare time.
Thanks for your answer. I’ve been doing some googling around and found:
http://stackoverflow.com/questions/1216037/optimizing-google-calendar-php-code-for-speed-zend-gdata
Pangulo, if your interested, I’ve started to develop a version of the class (see github, Google_Ss.php) that does not rely on Zend GData, it only uses CURL and SimpleXML (PHP5) … it is still and early version, but I am far enough along that you could continue developing something for yourself, if you really needed it.
Much thanks Dimas. I’ll let you know how it goes.
COOLEST THING EVER! Thank you so much! This helper class is just what I needed!
This was very helpful–got me away from using an iframe to post to a Google Docs form, which caused me no end of grief (but it is way slow on posting this way, I’m afraid)
In any case, I tried to write all these basic steps as functions, but when I use them I get an error that does not happen when I just use the straight code
STRAIGHT CODE (which works):
if ($ss->addRow($_SESSION[‘proofedrecipe’])) echo “Your recipe has been successfully stored”;
function call (which produces error):
Function:
Error:
I’m not sure how much more economical the functions might be, but can anyone see where I went astray?
Thanks!
Tim, try confirming that the value coming into your function is indeed correct?
function addtoGS($row) { print_r($row); ...Hey,
I was just wondering if you could possibly show me how to change the ACL List Feed of the worksheet? and is it possible to delete a particular spreadsheet or worksheet using PHP? cause surely it’ll be a great help in my project.. 🙁
thanks,
bryne
Hi Dimas,
I’ve been having trouble with intermittant timeout errors while posting to google spreadsheets so I’ve changed my appraoch and now instead of posting to google spreadsheet live, I write to a temp file and then regularly run a script over the temp files to post them, retrying if it times out.
Works great, except I’ve started getting the following error on my server.
Fatal error: Allowed memory size of 33554432 bytes exhausted (tried to allocate 40 bytes) in path/lib/ZendGdata/library/Zend/Gdata/App/Base.php on line 279
This doesn’t happen in my dev box, and can be fixed on the server by finding the right setting no doubt (it’s not memory_limit in php.ini as that is set to 128M, and the error says the limit is 32M)
However the thing I want to raise here is that adding a row to a google spreadsheet shouldn’t need more than 32M of memory!
I found the following post relating to this and a work-around to fix it, but my attempt to implement the work-around failed (I’m not very expert).
http://stackoverflow.com/questions/3004631/google-spreadsheet-api-problem-memory-exceeded
I’d like your thoughts on the problem and the potential fix, and whether you should look at that approach for your library?
If the memory required to add a row is somehow related to the number of rows in the spreadsheet, plenty of people are going to also run into this problem even if they do increase the memory limit.
BTW I tried using the latest Zend release 1.11.0 and it had the same issue.
Cheers,
Mark
Mark, I’ve run into similar issues, think it was more of a script timeout limit vs memory. How large is your Google Spreadsheet getting, how many rows?
I am actually still working on version of the class that does not use Zend Gdata (which I think is rather bulky), I’ve got a dev version on github.
@Bryne, at the moment the class does not handle spreadsheet delete, worksheet delete or the ACL. I’ve got a basic new version in the works which does not use Zend, see link in my previous comment, you may be able to use the current code to expand and build in the functionality that you need.
Dimas,
My spreadsheet is 300-400 rows, but I’ve had the timeout happen on much fewer rows. I’ve also had a lot of 500 returned when expecting 200, but the post happens fine in that case so I just ignore that error.
DImas, thanks for the code, looks like a great solution.
I get the same ssl issue though:
Fatal error: Uncaught exception 'Zend_Gdata_App_HttpException' with message 'Unable to Connect to ssl://www.google.com:443. Error #: ' in /[path]/ZendGdata-1.11.0/library/Zend/Gdata/ClientLogin.php:141
It is running on a shared host – maybe that i blocking the SSL connection?
Ok, I heard from my host, they found the fix:
Enabled that and it works 🙂
@Alex : your life looks pretty epic!
Dimas,
Your class is awesome.
I am finding the current Zend API connectivity a little slow. Any progress on the CURL method that you have been testing?
Thanks for the great code.
imageprophet, glad you found the code useful. The CURL version is coming along I have the basics working on github (dev), when i have a little bit more time i will finish it off.
this is pure genius!
thanks!
happy to read you are willing to get rid of zend
Hi,
I’ve definitely got a correlation between the the size of the spreadsheet and the amount of memory php needs to run.
Above 1000 rows (10-15 columns), to add a row I need more than 256M to avoid the memory error.
If I reduce the number of rows I can reduce the amount of memory required.
I’m hoping your new version has an addRow that is not sensitive to the size of the existing sheet. Am I right in thinking you haven’t implemented an addRow() yet though?
Mark
Mark, as this class seems to have lots of interest, I will continue work on the non-zend version of it. I am hoping that it will be faster and more efficient.
Hello,
I am getting the same problem as Tom and others. I am getting:
Expected response code 200, got 400 We're sorry, a server error occurred. Please wait a bit and try again.I have found that I am getting this because I am not the “owner” of the Spreadsheet, it has only been “Shared” with my user.
Any ideas?
Also I would be very interested in the non-zend version of this as well! 🙂
After much pain and toil, I figured out what was happening. It wasn’t the fact that the spreadsheet was shared to the user I was trying to authenticate with. It was that the column headings where on row 2 and not row one. Hope this helps…
Mark, I used this method insted for adding a row.
http://stackoverflow.com/questions/3004631/google-spreadsheet-api-problem-memory-exceeded
Shouldn’t be that hard to add multiple rows.
Fantastic Pascal,
I was unable to get it to work before , but the comment added on 9th Dec made all the difference.
I have modified Dimas’s code to use that approach, and it works well.
I will send it to him, so he can share it here if others want it.
Mark
A word of caution.
Today I’ve been finding that the new method fails to add a row when there is no blank row in the worksheet.
If I add extra blank rows to the bottom of the worksheet (via a
browser), then it can add rows again, so perhaps it’s not “inserting”
a row it’s writing on the first blank one. That needs to be fixed before this is a good solution.
Thank you for this class.
Do not work with cyrillyc letters
Sorry – thats my ****up. Charset for handler document must be utf-8
WOW,
Thank god I found this before I started re-inventing the wheel!
I started using the gData lib and playing with a few things but never had the time to make something out of it. However your helper class is great! Simple and Easy!
As for all the people above who are complaining that your helper class doesn’t turn Google Spreadsheets into a de-facto Mysql database server: Really? You’re going to make applications that do two way communications calls to/from Google Spreadsheets? Man that’s ballz! Just save the data from a form and share with your client and be happy.
dear Dimas,
thanks very much for your article! I encounter a problem however:
copy all your code and fill in the account and pasword and upload the Gdata fiels and google_spreadsheet.php i get this error when opening the page with your code (my gdata package is in the map Gdata)
Warning: require_once(Zend/Loader.php) [function.require-once]: failed to open stream: No such file or directory in /home/myuser/public_html/mysite.com/facebook/Google_Spreadsheet.php on line 216
Fatal error: require_once() [function.require]: Failed opening required ‘Zend/Loader.php’ (include_path=’.:/usr/lib/php:/usr/local/lib/php:/home/myuser/public_html/mysite.com/Gdata/library’) in /home/myuser/public_html/mysite.com/facebook/Google_Spreadsheet.php on line 216
I think it has somethign to do with the (include_path=’.:/usr/lib/php:/usr/local/lib/php: which is two times or something?
Do you have any idea?
Thanks very much!
Using ZendFramework-1.11.2, and the getRows function from the Google_Spreadsheet example above, I get:
Fatal error: Uncaught exception ‘Zend_Gdata_App_HttpException’ with message ‘Expected response code 200, got 400 Parse error: null’
Parse error: null. I would be very relieved to be able to trace this and get anything_zend finally working. Thanks…
The trouble seems to be with the spreadsheet url:
http://spreadsheets.google.com/feeds/list/t2ZbJy98cZh7lfwG8HaFcSw/od6/private/full?sq=id%3D0AtfwL4XihrlfdDJaYkp5OThjWmg3bGZ3RzhIYUZjU3c
Now, in the line in Google_Spreadsheet.php:
$rows = $ss->getRows(“id=zd92cd4a8f7a001c343a5144ad3570668”);
I assume that the id= should be the key of one’s own spreadsheet. Or is it the id of the workbook, and if so how does one get that?
Re my last email, I have now re-read with morning eyes and got it. The id is the unique value in the id column, not the spreadsheet or worksheet, which is of course already in the code by key and name.
I have not got this line to work with the double inverted commas:
$rows = $ss->getRows(‘name=”John Doe”‘);
Fatal error: Uncaught exception ‘Zend_Gdata_App_HttpException’ with message ‘Expected response code 200, got 400 Parse error: Invalid token encountered
though this line works, with single inverted comma:
$rows = $ss->getRows(‘id=John’);
url: http://spreadsheets.google.com/feeds/list/t2ZbJy98cZh7lfwG8HaFcSw/od6/private/full?sq=name%3D%22John+Doe%22
As per the zend documentation, it will not return beyond a completely blank row.
Dimas, Thanks a lot for providing this simple working code, very useful.
Would you be able to publish a version with a cells feed rather than a list feed. My target has unorganised data, not well formatted, and I want to get it and parse it separately from the original cells layout. Eg first line is not all the headings, there are random blank lines etc.
I think getColumnNames() does not return any column names if there is only one row in the worksheet. Specifically if the second row is blank. getcolumnNames was coming back empty, but by putting a single character in the first cell of the second row, so it wasn’t blank, it returned the column names as expected. Can anyone else confirm this, should be easy to duplicate.
Great tutorial…very helpful. I do have a question though. Is there any way to add rows while ignoring a pre-filled first column. I am trying to integrate this process with a spreadsheet we use as a queue to assign form-uploaded documents to consultants. The consultants need to be assigned in advance (e.g., listed in column A) and then the form data would ideally fill in beginning in column B. I realize this might not be possible, but I would be grateful for any solutions you or others might be able to suggest.
I have used the script above and it was working and I had adapted it for cells feed to get the spreadsheet into a database table without failing on blank rows or cells…. However, I found memory problems and to solve this was trying to fetch one cell at a time as each is an object.
Current problem however is that I notice, on error, prints out my log in name and password for google, and sometimes has CAPTCHA challenge response. Something to avoid if referring page may be public or non-secure. How do you turn off error reporting?
I have also failed to get code to access a public spreadsheet using the id from the spreadsheets feed, instead of name and password. I try:
$spreadsheetService = new Zend_Gdata_Spreadsheets();
$spreadsheetKey=”https://spreadsheets.google.com/feeds/spreadsheets/aaaabbbccc_SpreadSheetKeyFrom_id_tagOfSpreadsheetsFeed”;
$query = new Zend_Gdata_Spreadsheets_DocumentQuery();
$feed = $spreadsheetService->getWorksheetFeed($spreadsheetKey);
and I get ‘DOMDocument cannot parse XML’ etc. This is a public spreadsheet. Grateful for any scripts on accessing public spreadsheet so that I could get a test to work.
hi Dimas,
im trying to use ur foofle helper library, but i get this mistake using Zend 1.11.3
Parse error: parse error, unexpected T_ARRAY, expecting ‘&’ or T_VARIABLE in D:\wos_1.1.1\www\Zend\Http\Response.php on line 151
i need to get some information from my spreadsheet in google docs, so plz help me
THX IN ADVANCED!!!!
Thanks for the code! Working great!
Found out that if my column name has an underscore, e.g.: my_id, then I will get “Expected response code 200, got 400 Parse error: Invalid column name ” error. As soon as I remove the underscore from the $where clause, then it works!
Very awesome work! Was looking at the spreadsheets API and scratching my head. Great helper class you have that makes things sooo much simpler. 🙂
Hi,
Yesterday my spreadsheet stopped updating!? Does anybody else have the same problem? Has Googles APIs changed?
Thanks in advance!
I have found if you don’t have “extra blank rows” at the bottom of the spreadsheet, the updates fail. You can add rows with the tool at the bottom of the actual spreadsheet.
Hi Tim,
It has worked for me for a couple of months. I tried to add a blank row att the bottom of the spreadsheet but it didn’t change anything. Still doesn’t work. 🙁 The weird thing is we didn’t change anything but from one day to another it just stopped writing to the spreadsheet.
How many rows does your spreadsheet have? I have personally found that my current solution does not scale well as the spreadsheet grows. One immediate solution is to archive the current spreadsheet and begin writing to a new one with the same header fields.
I hope to solve this issue with w new version of the class which will do away with using Zend_Gdata completely, just need to find the time to get it finished.
Great class, very useful and it actually works (you’d be surprised how many code snippets like this don’t work). I was particularly impressed at the age of this post and that people (including the author!) are still commenting on it.
This is exactly what I needed to get a client job done. Great work!
11-Apr-2011 16:20:17] PHP 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 reloading your spreadsheet.’ in /home/almoblux/public_html/ZendGdata-1.11.5/library/Zend/Gdata/App.php:709
Stack trace:
#0 /home/almoblux/public_html/ZendGdata-1.11.5/library/Zend/Gdata.php(219): Zend_Gdata_App->performHttpRequest(‘POST’, ‘https://spreads…’, Array, ‘performHttpRequest(‘POST’, ‘https://spreads…’, Array, ‘post(‘insertEntry(‘<atom:entry xml…', 'https://spreads…', 'Zend_Gdata_Spre…') in /home/almoblux/public_html/ZendGdata-1.11.5/library/Zend/Gdata/App.php on line 709
This is the error that I receive from my site.
Hi Dimas,
Everything works great but when I look at my source code in the browser I get all these errors yet my form writes to my spreadsheet. What could be causing the problem?
ncaught exception ‘Zend_Gdata_App_HttpException’ with message ‘Expected response code 200, got 500
Internal Error’ in /home3/vmcadedu/public_html/ryan-test/Zend/Gdata/App.php:709
Stack trace:
#0 /home3/vmcadedu/public_html/ryan-test/Zend/Gdata.php(219): Zend_Gdata_App->performHttpRequest(‘POST’, ‘https://spreads…’, Array, ‘<atom:entry xml…’, ‘application/ato…’, NULL)
#1 /home3/vmcadedu/public_html/ryan-test/Zend/Gdata/App.php(900): Zend_Gdata->performHttpRequest(‘POST’, ‘https://spreads…’, Array, ‘<atom:entry xml…’, ‘application/ato…’)
#2 /home3/vmcadedu/public_html/ryan-test/Zend/Gdata/App.php(975): Zend_Gdata_App->post(‘<atom:entry xml…’, ‘https://spreads…’, NULL, NULL, Array)
#3 /home3/vmcadedu/public_html/ryan-test/Zend/Gdata/Spreadsheets.php(336): Zend_Gdata_App->insertEntry(‘<atom:entry xml…’, ‘https://spreads…’, ‘Zend_Gdata_Spre…’)
#4 /home3/vmcadedu/public_html/ryan-test/Google_Spreadsheet.php(74): Zend_Gdata_Spreadsheets->insertRow(Array, ‘tM_ugkW5V35FgTj…’, ‘od6’)
#5 /home3/ in /home3/vmcadedu/public_html/ryan-test/Zend/Gdata/App.php on line 709
Hi,
I get the same problem as Tom did:
Fatal error: Uncaught exception ‘Zend_Gdata_App_HttpException’ with message ‘Unable to Connect to ssl://www.google.com:443. Error #48857704: Unable to find the socket transport “ssl” – did you forget to enable it when you configured PHP?’
Did you find a solution back then?
hi all…
I have used this method for 4 months now… but suddenly I started having an error:
Fatal error: Uncaught exception 'Zend_Gdata_App_HttpException' with message 'Expected response code 200, got 500 Internal Error' in (... lots of text ...) (E:\vhosts\gelpalhano.org\httpdocs\libs\ZendGData\library\Zend\Gdata\App.php on line 709)well well well… any ideas? maybe zend does things in a way and the framework has changed? any guesses?
Dimas:
Thanks for this tool. Very helpful.
I noticed that the getRows function doesn’t like certain characters, like @ and % symbols. I tired using an email address as an Id, but kept getting and invalid token error. I ended up hashing the email address as the id to create a unique value that could be consistently looked up and not have the special character issue. Wanted to post this for anyone else seeing this error. Took me a little while to figure it out.
Hi, there, great script! I am trying to do this exact thing for my company’s seminars form submissions. I’ve got everything set up and working like the example with this code in the head:
// Zend library include path set_include_path(get_include_path() . PATH_SEPARATOR . "ZendGdata-1.11.6/library"); include_once("Google_Spreadsheet.php"); $u = "MY@EMAIL.COM"; $p = "MYPASSWORD"; $ss = new Google_Spreadsheet($u,$p); $ss->useSpreadsheet("form-submission"); // 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";But it’s using the array with static data. I’ve added a form to my body, but I cannot figure out how tie the form data into the script and have THAT data pulled into the SS. Can someone who has done it successfully above please post their code example? I am not a PHP expert!!
Thanks!
–Jason
@Jason, depending how your form is submitting data, you can use the
$_POSTor$_GETvars. A basic example is to pass the data directly:You may want to look into, doing some data validation. Zend has some classes for that also.
Thanks for the quick response, Dimas! I’ve tried altering my code like your example, and I get the following errors:
[18-May-2011 05:01:02] PHP Notice: Undefined index: name in /Users/jasonand/Sites/www-dreamlocalfb/form-submission/form-submission.php on line 19 [18-May-2011 05:01:02] PHP Notice: Undefined index: email in /Users/jasonand/Sites/www-dreamlocalfb/form-submission/form-submission.php on line 20 [18-May-2011 05:01:02] PHP Notice: Undefined index: comment in /Users/jasonand/Sites/www-dreamlocalfb/form-submission/form-submission.php on line 21 [18-May-2011 05:01:04] PHP Fatal error: Uncaught exception 'Zend_Gdata_App_HttpException' with message 'Expected response code 200, got 500 Internal Error' in /Users/jasonand/Sites/www-dreamlocalfb/form-submission/ZendGdata-1.11.6/library/Zend/Gdata/App.php:709 Stack trace: #0 /Users/jasonand/Sites/www-dreamlocalfb/form-submission/ZendGdata-1.11.6/library/Zend/Gdata.php(219): Zend_Gdata_App->performHttpRequest('POST', 'https://spreads...', Array, 'performHttpRequest('POST', 'https://spreads...', Array, 'post('insertEntry('<atom in /Users/jasonand/Sites/www-dreamlocalfb/form-submission/ZendGdata-1.11.6/library/Zend/Gdata/App.php on line 709Here’s the code I am using for my form:
Doesn’t work. Seems like it could be a cool thing. But just like alot of other things out there, its way too much and too complicated, not for average users. If you are a rocket scientist and have alot of time to waste then this may be for you.
I tried once on my development server and once on my main server, both gave me different errors and wasted my time.
@drooh, sorry to hear that you couldn’t get it working. I think working with Zend makes it a bit cumbersome and difficult for some. I’ve started a PHP/Google only integration which should remove Zend out of the mix and make things easier. Hopefully I will have the time to finish it soon.
thanks for this – works perfectly
Simple to use and works perfectly. Just download the Zend and Google zip files, unzip and make sure the correct files are in the path. Then use and smile.
got the basic inserts working after recompiling my apache/php to support openssl.
On of the fields I populate to the spreadsheet is an email address. I have adapted your code to try to insert only new rows so it tries to do a check in the spreadsheet :
$querystring = “Email=george@geolaw.com”;
$rows = $ss->getRows($querystring);
I have tried just about every combination on this – running it through htmlspecialchars, urlencode, etc … escaped double quotes around the email address, etc …
Using your delete example, I wrapped my getRows with try {} and it is throwing the following exception:
Expected response code 200, got 400 Parse error: Invalid token encountered
i’m not supposed to edit anything in Google_Spreadsheet.php, right?
I got always the same error message which doesn’t mean anything to me :
Fatal error: Uncaught exception 'Zend_Gdata_App_HttpException' with message 'Unable to Connect to ssl://www.google.com:443. Error #808863644: Unable to find the socket transport "ssl" - did you forget to enable it when you configured PHP?' in /volume1/web/ZendGdata/library/Zend/Gdata/ClientLogin.php:141 Stack trace: #0 /volume1/web/master/farinspace-google-spreadsheet-0a21310/Google_Spreadsheet.php(223): Zend_Gdata_ClientLogin::getHttpClient('tartemolle@gm...', 'FFFFFF', 'wise') #1 /volume1/web/master/farinspace-google-spreadsheet-0a21310/Google_Spreadsheet.php(40): Google_Spreadsheet->login('tartemolle@gm...', 'FFFFFF') #2 /volume1/web/essaiGdata.php(15): Google_Spreadsheet->__construct('tartemolle@gm…', 'FFFFFF') #3 {main} thrown in /volume1/web/ZendGdata/library/Zend/Gdata/ClientLogin.php on line 141Some one knows how to manage this error ?
Many thanks
Bronson
You need to have the php openssl extension installed.
So if you run your own server and you have rolled your own PHP, recompile with –with-openssl
# ./configure –help |grep openssl
–with-openssl[=DIR] Include OpenSSL support (requires OpenSSL >= 0.9.6)
–with-openssl-dir[=DIR] FTP: openssl install prefix
–with-openssl-dir[=DIR] SNMP: openssl install prefix
If you use the RPM versions of the PHP extensions, this is also included in the php-common RPM package.
If you are hosted through a hosting company, ask them to enable php-openssl
HTH
George
RE: searching with getRows and an email address
To update my problem with searching for an email address, I found another posting early on suggesting that you do a MD5 on the email address and then use that as an ID field – since this is plain text, the getRows() works fine.
—
George
Hi,
I have check for openssl.
OpenSSL support enabled
OpenSSL Version OpenSSL 0.9.8e-fips-rhel5 01 Jul 2008
But still i am getting same error again ……
Fatal error: Uncaught exception ‘Zend_Gdata_App_HttpException’ with message ‘Unable to Connect to ssl://www.google.com:443. Error …….
I Spock to hosting provider they told me to give IP address of site that attempts to use…
Anybody know the ip address that uses to connect google doc API
Thanks,
Anil
Anil,
I forgot that some hosts block outgoing http/https as well:
Google docs looks like they use round robin DNS across 15 different IPs.
74.125.227.0/28
#> host docs.google.com
docs.google.com has address 74.125.227.1
docs.google.com has address 74.125.227.0
docs.google.com has address 74.125.227.9
docs.google.com has address 74.125.227.3
docs.google.com has address 74.125.227.7
docs.google.com has address 74.125.227.13
docs.google.com has address 74.125.227.14
docs.google.com has address 74.125.227.15
docs.google.com has address 74.125.227.11
docs.google.com has address 74.125.227.8
docs.google.com has address 74.125.227.12
docs.google.com has address 74.125.227.2
docs.google.com has address 74.125.227.5
docs.google.com has address 74.125.227.10
docs.google.com has address 74.125.227.4
docs.google.com has address 74.125.227.6
HTH
George
Found this article with google! thank You very much for this lib, im gona build my blog app using this approach, no mysql involved 🙂
@Mark Yes I had issues with path to zend lib, I started to think that it doesnt work for me to or something else… but i fixed it and it works fine (sun)
@wolf3d, this lib is no substitute for a good db like mysql or sqlite. My experience so far using this is that for high traffic apps which will make lots of entries, using google spreadsheet is not the best option.
@Dimas You are right, but it’s just for my research project im working on, I like to experiment!
I think it’s incredible how many people here are all like “This is impossible, you must be some kind of rocket scientist…”
Or maybe you must be some kind of computer programmer! It’s a whole discipline and it takes time, effort, and energy to learn. Just because you’re on Facebook all day doesn’t make you a Zend developer. Sheesh. Kudos to you for accommodating these people but if this is too hard, the reason might just be that you’re not a programmer.
Hello,
First of all thank you for this information! I have build quite an elaborate stock analysis spreadsheet which needs only one input, the ticker symbol, and then retrieves data from several websites and performs multiple calculations and analyses. In the end it gives a simple answer to the question “Should I buy? YES or NO?”
I would like to build a PHP script which can input one ticker after the other in the Gdocs spreadsheet and save all the tickers which lead to a YES answer (a buy recommendation) in a separate worksheet.
My question: is the Google spreadsheet API capable of performing these actions?
It will save me a lot of time if I find out upfront by one of your answers whether this is possible or not. Thanks!
Nick, interesting question …
what I know:
you can target specific a CELL with the API
what I don’t know:
whether google will run the macros outside of the browser, when going through the API to get the final output (I am guessing not)
What I recommend:
1) If it is at all possible to translate the spreadsheet into a self contained PHP script, then this might provide the best flexibility / automation.
2) I have been toying around with selenium (browser automation), you might be able to have it automatically open a browser, enter in values, retrieve values, and save it.
Thank you for this!
It works for my needs. Unfortunately there is some annyoing “bug” in this, actually the problem comes from the Zend Library itself (Zend_Gdata_Spreadsheets::updateRow) I think.
When you have formulas in your worksheet and use the updateRow method, the formula is overwritten and the actual value of the cell is inserted instead.
Nice class anyway.
This is a very cool helper class! It worked for me right away. Just a question however, I’m no security expert so I’m wondering what security issues I might be exposed to by including my Google password in the page source. I know the PHP gets processed before rendering the HTML, but is there any threat short of a hacked server account that I should be worried about? Does anyone have a way to encrypt/decrypt the password in the source in case the server does get hacked?
Thanks!
I started using this today. Few things:
1) You need OpenSSL to use it. Check your php.ini file and uncomment
;extension=php_openssl.dll
If it does not exist, typeit in manually and make sure php_openssl.dll is in the c:/xampp/php/ext folder.
2) If you’re using php strict and get pass by reference errors, you’ll need to change this in the Google_Spreadsheet class. You’ll need to do it twice, once for getSpreadsheetId() and once for getWorksheetId().
$wk_id = array_pop(explode(“/”,$entry->id->text))
to
$entry_array = explode(“/”,$entry->id->text);
$wk_id = array_pop($entry_array);
3) Errors. It seems that google docs is extremely strict with errors. If anything wrong happens, it may lock you out of the spreadsheet for 5-10 minutes, even from google docs itself. Not sure what to do about that except debug extensively before release. And pray for the best.
Right now i am experimenting and this feature seems really cool but i am not able to get this work all the time it just submits perhaps some screen shots will be much helpful
Works great, thank you.
I had another block with the error ‘Unable to Connect to ssl’…
It needed a change at the hsphere server:
”
It appears this is an issue with zend using a function that was in our disabled functions list — stream_socket_client specifically. I have removed that from the list and the page now works. This should not be an issue to security as it is just a client function and not a listening
”
Hope that helps someone.
Hi,
I was just wondering if its possible to write to a specific cell by specifying the row and column, instead of writing to the last row in the specified name. Also, thanks for the instructions. I was able to get it working right away 🙂
@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.
Hi, first off, great tutorial. It was really helpful and very well written. However, I would also like to know how to addColumn rather than a row. Cheers
How to add auto increment ID (with numbers only) and timestamp to spreadsheet ? Is it possible? THanks in advance!
Hi!
I need to make this thing work..
But I get this error:
Warning: require_once(Zend/Loader.php) [function.require-once]: failed to open stream: No such file or directory in /home/mydomain/public_html/mydomain.in/register/Google_Spreadsheet.php on line 216
Fatal error: require_once() [function.require]: Failed opening required ‘Zend/Loader.php’ (include_path=’.:/usr/lib/php:/usr/local/lib/php:zend/library’) in /home/mydomain/public_html/mydomain.in/register/Google_Spreadsheet.php on line 216
I guess I haven’t installed ZEND properly, can you suggest how to install it?
Thanks for such a great tutorial and helper class !
I have used this and it works fine for me. I am using PHP: 5.3.5. One problem is that it is giving one warning like
Strict Standards: Only variables should be passed by reference in C:\xampp\htdocs\usolver\Google_Spreadsheet.php on line 258
Strict Standards: Only variables should be passed by reference in C:\xampp\htdocs\usolver\Google_Spreadsheet.php on line 289
I am unable to hide this warning or solve this or solve this warning. Please help me for this warning.
Can you provide an example of this code with oauth 2.0? I don’t want to use google user name password
Hey,
I use this in some use cases. But went to a problem, when the Spreadsheet is bigger then some size, the memory on server for PHP is out. Is there any option to work with Google Spreadsheet in more memory efficient way? Any ideas? I think that Zend GData works with Spreadsheet in memory…and the spreadsheet is (for sure) constantly increasing…
Very interesting and useful article, thanks a lot for this helper!
I’m using it on a French site and unfortunately, the cleanKey() function removes the accentued characters (é à è ï…), any idea how to avoid that?
I do have some columns with accent in their headers, so I can’t get to write in them properly :/
Anyway, thanks again!
Actually, please ignore my comment, I found a solution to allow accents 😉
function cleanKey($k)
{
return strtolower(preg_replace(‘/[^A-Za-z0-9àáâãäåçèéêëìíîïðòóôõöùúûüýÿ\-\.]+/’,”,$k));
}