183 responses to “Saving Form Data to Google Spreadsheets Using PHP and the Google Docs API”

  1. Jeff

    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*)]’;

  2. A Reader

    Very very useful article. I’m gonna try this out! Thank you very much!

  3. Jeff

    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.

  4. dmon

    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.

  5. dmon

    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.

  6. dmon

    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;
    }

  7. dmon

    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.

  8. dmon

    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! 😉

  9. nikita_m

    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.

  10. BillyTheCzech

    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?

  11. sam

    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………

  12. sam

    i am going to attempt reinstalling wamp or xamp maybe that will fix it

  13. Alex

    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!

  14. 4fthawaiian

    Totally lovin’ you dude. Great post. Exactly what I wanted.

  15. sam

    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….

  16. sam

    im currently useing
    $ss->useWorksheet($_POST[“site”]);
    but that fails sometimes because of the worksheet not existing

  17. sam

    alright well how about getting data from a specific cell such as
    $cell = $ss->getCell(“B:5”)
    can it do that?

  18. sam

    ok thanks ill go check that out now 😀

  19. sam

    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 😀

  20. sam

    any idea?!?!?!?
    still getting the error….

  21. sam

    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

  22. sam

    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

  23. sam

    ok ill i.m. u

  24. Paulo Bueno

    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

  25. Stephen Akins

    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.

  26. Chris

    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?

  27. Chris

    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….

  28. Mychal

    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

  29. Andrew

    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???

  30. Fiifi Baidoo

    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.

  31. Tom

    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.

  32. caesar

    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.

  33. caesar

    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.

  34. Tom

    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”;

  35. caesar

    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.

  36. caesar

    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.

  37. Tom

    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???…

  38. Johnny

    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!

  39. Geoff

    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.

  40. Mark

    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

  41. Mark

    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”)

  42. Dan

    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!

  43. Erik

    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!

  44. Mark

    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.

  45. Mark

    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!

  46. Jackson

    Dear Dimas,
    Thank you for the code and tutorial you wrote, clearly explained!

    Regards,
    Jackson

  47. Ramakant Yadav

    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

  48. Joe Engel

    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!

  49. Mike

    I export my spreadsheets from Google Docs using CSV files this way

  50. Subha

    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

  51. Sherry Nikolette Edmilao

    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. :)

  52. Sherry

    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! :)

  53. David

    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!

  54. David

    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?

  55. marios88

    for those getting the formula #name? or namespace error look here

    http://lifeinzeroone.tumblr.com/post/1177909602/zend-gdata-api-spreadsheet-add-formula

  56. marios88

    @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’);

  57. Pangulo

    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?

  58. Pangulo

    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

  59. Pangulo

    Much thanks Dimas. I’ll let you know how it goes.

  60. Landon

    COOLEST THING EVER! Thank you so much! This helper class is just what I needed!

  61. Tim at IDG

    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):

    addtoGS($_SESSION[‘proofedrecipe’]);

    Function:

    function addtoGS($row) //send array to spreadsheet row
    {
    global $u;
    global $p;
    global $ss;

    if ($ss->addRow($row)) echo “Form data successfully stored using Google Spreadsheet”;
    else echo “Error, unable to store spreadsheet data”;
    } //end add function

    Error:

    Warning: Invalid argument supplied for foreach() in /home/user/public_html/xxx/Google_Spreadsheet.php on line 72

    Fatal error: Uncaught exception ‘Zend_Gdata_App_HttpException’ with message ‘Expected response code 200, got 400 Blank rows cannot be written; use delete instead.’ in /home/user/php/ext/ZendGdata-1.11.0/library/Zend/Gdata/App.php:709 Stack trace: #0 /home/user/php/ext/ZendGdata-1.11.0/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…') #4 /home/user/public_html/xxx/Google_Spreadsheet.php(74 in /home/user/php/ext/ZendGdata-1.11.0/library/Zend/Gdata/App.php on line 709

    I’m not sure how much more economical the functions might be, but can anyone see where I went astray?

    Thanks!

  62. Bryne

    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

  63. mark

    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

  64. Mark

    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.

  65. Molecule

    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?

  66. Molecule

    Ok, I heard from my host, they found the fix:

    It appears this is an issue with zend using a function that was in our disabled functions list — stream_socket_client specifically.

    Enabled that and it works :)

    @Alex : your life looks pretty epic!

  67. imageprophet

    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.

  68. Jerome

    this is pure genius!
    thanks!

    happy to read you are willing to get rid of zend

  69. Mark

    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

  70. Landon

    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! :)

  71. Landon

    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…

  72. Pascal Gagneur

    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.

  73. Mark

    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

  74. 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.

  75. Shkur

    Thank you for this class.
    Do not work with cyrillyc letters

  76. Shkru

    Sorry – thats my ****up. Charset for handler document must be utf-8

  77. Rob

    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.

  78. Sjaak

    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!

  79. John

    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?

  80. John

    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.

  81. John

    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.

  82. Mark

    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.

  83. Derek

    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.

  84. John

    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.

  85. Pablo

    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!!!!

  86. kelf

    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!

  87. Kian Ann

    Very awesome work! Was looking at the spreadsheets API and scratching my head. Great helper class you have that makes things sooo much simpler. :)

  88. Victor

    Hi,

    Yesterday my spreadsheet stopped updating!? Does anybody else have the same problem? Has Googles APIs changed?

    Thanks in advance!

  89. Tim

    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.

  90. Victor

    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.

  91. Phillip Copley

    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!

  92. Christian11

    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.

  93. Ryan

    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

  94. Peter

    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?

  95. Marquito

    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?

  96. ian

    Dimas:
    Thanks for this tool. Very helpful.

  97. Lucas

    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.

  98. Jason Andreoni

    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

  99. Jason Andreoni

    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 709

    Here’s the code I am using for my form:

    <form method="post" action="" id="contactform">
    Name (required)
    Email (required)
    Message (required)
    
  100. drooh

    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.

  101. Paul

    thanks for this – works perfectly

  102. Mark

    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.

  103. George

    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

  104. tee

    i’m not supposed to edit anything in Google_Spreadsheet.php, right?

  105. bronson

    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 141

    Some one knows how to manage this error ?

    Many thanks

  106. George

    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

  107. George Law

    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

  108. anil

    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

  109. George Law

    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

  110. wolf3d

    Found this article with google! thank You very much for this lib, im gona build my blog app using this approach, no mysql involved :)

  111. wolf3d

    @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)

  112. wolf3d

    @Dimas You are right, but it’s just for my research project im working on, I like to experiment!

  113. Dave Kaye

    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.

  114. Nick

    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!

  115. Marc Christenfeldt

    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.

  116. Ryan

    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!

  117. amn

    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.

  118. Bharath

    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

  119. Franck

    Works great, thank you.

  120. molecule

    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.

  121. Abbas

    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 :)

  122. Abbas

    Thanks for all the help. I really appreciate it :)

  123. Matt

    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?

  124. Danilo

    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.

  125. jusnit

    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();

  126. Douglas Machado

    Greetings,

    Does anyone knows how to add columns?

    Thanks in advanced for your help.

  127. Usman

    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

  128. Rob

    Dear Sir,

    You are awesome.

    That is all.

  129. sumesh

    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

  130. Metaksan

    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.

  131. Mei

    Hello Dimas, thanks for sharing this. I’ve got a question: how do we get all rows in a specific column?
    Thanks.

  132. Martin Grados Salinas

    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.

  133. Sumit Agrawal

    Does this need features like curl, fopen etc to be enabled on my hosting server or that is not required?

  134. Suresh V

    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

  135. evlj

    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….

  136. Ahmed

    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?

  137. Ron

    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.

  138. skumar

    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

  139. Saleem

    Thanks for this master piece of Google Spreadsheet code. It saved my hours of work.

  140. Dr. Uday Wad

    I would like to exchange data from my android code with google spreadsheet thro’ ofcourse java. Can any body suggest…

  141. pixeline

    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?

  142. Martin

    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?

  143. Alfonso

    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!

  144. Hmm

    $service = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME;
    $http = Zend_Gdata_ClientLogin::getHttpClient($user,$pass,$service);
    $this->client = new Zend_Gdata_Spreadsheets($http);

  145. Vincent Toms

    This is Really nice, is there any way to format the rows. Namely, I want to change the background color of the row.

  146. Patrick Taylor

    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

  147. Vlajke

    How to add auto increment ID (with numbers only) and timestamp to spreadsheet ? Is it possible? THanks in advance!

  148. Donnie Ashok

    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?

  149. Amar Ranjan Das

    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.

  150. david

    Can you provide an example of this code with oauth 2.0? I don’t want to use google user name password

  151. JT

    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…

  152. Pierre Saikali

    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!

  153. Pierre Saikali

    Actually, please ignore my comment, I found a solution to allow accents 😉

    function cleanKey($k)
    {
    return strtolower(preg_replace(‘/[^A-Za-z0-9àáâãäåçèéêëìíîïðòóôõöùúûüýÿ\-\.]+/’,”,$k));
    }