Home » Featured

Using Google Spreadsheets to Collect Form Data

by
10. September 2009 More from mjg 2 Comments

Google Spreadsheets recently offered the ability to create a form that will collect customer information into the spreadsheet. This is great for people that don’t have access to storing data in a traditional database (which is our preferred solution). They offer you the code to either link to for this form, email the code (great for emailed surveys) as well as code to embed (via IFrame) the form in your existing web page.

If you’d prefer to have FULL control over the display of the form and control the formatting and manipulation of data with your server-side code here is a handy little guide on how to do this using PHP.

 

  1. Create Google Doc form with all the fields your form needs
    image
  2. Save the form
    image
  3. Email yourself a copy of the form
    image
  4. Pull the necessary fields out of the email to generate the form
    URL for curl to post to
    URL to post form to...
    Names of the various input fields
    Finding the name of the various fields
  5. Insert code below into PHP page
  6. Test solution
   1: //In order to post to Google Spreadsheet Form
   2: //http://spreadsheets.google.com/formResponse?formkey=dFdYSTlzUVJsSomeReallyLongKeyGoesHereqemU2YUE6MA..
   3: //Name "entry.0.single" 
   4: //Email is "entry.1.single"
   5: //Phone is "entry.2.single"
   6: //Comment is "entry.3.single"
   7: //IP address is "entry.4.single"
   8: $url = 'http://spreadsheets.google.com/formResponse?formkey=dFdYSTlzUVJsSomeReallyLongKeyGoesHereqemU2YUE6MA..';
   9: // Create post array to send results to Google Spreadsheets
  10: $fields = array(
  11:                         'entry.0.single'=>urlencode($name),
  12:                         'entry.1.single'=>urlencode($email),
  13:                         'entry.2.single'=>urlencode($phone),
  14:                         'entry.3.single'=>urlencode($comments),
  15:                         'entry.4.single'=>getRealIpAddr(),
  16:                         'submit'=>'submit'
  17:                 );
  18:  
  19: // Begining of code for posting to Google Spreadsheet
  20: $fields_string = '';
  21: //url-ify the data for the POST
  22: foreach($fields as $key=>$value) { $fields_string .= $key.'='.$value.'&'; }
  23: //rtrim($fields_string,"& ");
  24: $fields_string = substr($fields_string, 0, strlen($fields_string)-1); 
  25: $result = "Fields_String: [" . $fields_string . "]<br />";
  26:  
  27: //set POST variables for Google Spreadsheets
  28: //open connection
  29: $ch = curl_init();
  30:  
  31: //set the url, number of POST vars, POST data
  32: curl_setopt($ch,CURLOPT_URL,$url);
  33: curl_setopt($ch,CURLOPT_POST,count($fields));
  34: curl_setopt($ch,CURLOPT_POSTFIELDS,$fields_string);
  35: curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
  36: curl_setopt($ch,CURLOPT_USERAGENT,'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.13) Gecko/20080311 Firefox/2.0.0.13');
  37:  
  38: //execute post
  39: $result .= "Curl Results: [" . curl_exec($ch) . "]<br />";
  40:  
  41: //close connection
  42: curl_close($ch);

Things to note about the above PHP code (gasp!) are that you need to define the proper URL to the spreadsheet and the correct number and names of  fields (see step 4 above) . The only one that might be a little odd is getRealIpAddr() method. This is defined elsewhere in the page’s code as follows:

   1: // Function to get real IP address in case visitor is behind a proxy server
   2: function getRealIpAddr()
   3: {
   4:     if (!empty($_SERVER['HTTP_CLIENT_IP'])) // Check IP from shared internet
   5:     {
   6:         $ip = $_SERVER['HTTP_CLIENT_IP'];
   7:     }
   8:     elseif (!empty($_SERVER['HTTP_X_FORWARDED_FOR'])) // to check if ip is passed from proxy
   9:     {
  10:         $ip = $_SERVER['HTTP_X_FORWARDED_FOR'];
  11:     }
  12:     else
  13:     {
  14:         $ip = $_SERVER['REMOTE_ADDR'];
  15:     }
  16:     return $ip;
  17: }

 

 

 

Have fun folks! If you want to rip into my code and/or have suggestions on better PHP coding styles, please use the comment section below…

Comments (2) -

Sumit
India Sumit said:

Does not seem to work. I used the php code as form action. The code executes but no data is entered into my form

Addy
Syria Addy said:

Super-Duper site! I am loving it!! Will come back again - taking you feeds also, Thanks.

Add comment




  Country flag
biuquote
  • Comment
  • Preview
Loading