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

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

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

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

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

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

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

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

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

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

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

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

  12. Victor

    Hi,

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

    Thanks in advance!

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

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

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

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

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

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

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

  20. ian

    Dimas:
    Thanks for this tool. Very helpful.

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

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

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