Using Google Spreadsheets to Collect Form Data -

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.

Create Google Doc form with all the fields your form needs
Save the form
Email yourself a copy of the form
Pull the necessary fields out of the email to generate the form
URL for curl to post to

Names of the various input fields
Insert code below into PHP page
Test solution

//In order to post to Google Spreadsheet Form
//http://spreadsheets.google.com/formResponse?formkey=dFdYSTlzUVJsSomeReallyLongKeyGoesHereqemU2YUE6MA..
//Name "entry.0.single" 
//Email is "entry.1.single"
//Phone is "entry.2.single"
//Comment is "entry.3.single"
//IP address is "entry.4.single"
$url = 'http://spreadsheets.google.com/formResponse?formkey=dFdYSTlzUVJsSomeReallyLongKeyGoesHereqemU2YUE6MA..';
// Create post array to send results to Google Spreadsheets
$fields = array(
                        'entry.0.single'=>urlencode($name),
                        'entry.1.single'=>urlencode($email),
                        'entry.2.single'=>urlencode($phone),
                        'entry.3.single'=>urlencode($comments),
                        'entry.4.single'=>getRealIpAddr(),
                        'submit'=>'submit'
                );
  
// Begining of code for posting to Google Spreadsheet
$fields_string = '';
//url-ify the data for the POST
foreach($fields as $key=>$value) { $fields_string .= $key.'='.$value.'&'; }
//rtrim($fields_string,"& ");
$fields_string = substr($fields_string, 0, strlen($fields_string)-1); 
$result = "Fields_String: [" . $fields_string . "]<br />";
  
//set POST variables for Google Spreadsheets
//open connection
$ch = curl_init();
  
//set the url, number of POST vars, POST data
curl_setopt($ch,CURLOPT_URL,$url);
curl_setopt($ch,CURLOPT_POST,count($fields));
curl_setopt($ch,CURLOPT_POSTFIELDS,$fields_string);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
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');
  
//execute post
$result .= "Curl Results: [" . curl_exec($ch) . "]<br />";
  
//close connection
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:

// Function to get real IP address in case visitor is behind a proxy server
function getRealIpAddr()
{
    if (!empty($_SERVER['HTTP_CLIENT_IP'])) // Check IP from shared internet
    {
        $ip = $_SERVER['HTTP_CLIENT_IP'];
    }
    elseif (!empty($_SERVER['HTTP_X_FORWARDED_FOR'])) // to check if ip is passed from proxy
    {
        $ip = $_SERVER['HTTP_X_FORWARDED_FOR'];
    }
    else
    {
        $ip = $_SERVER['REMOTE_ADDR'];
    }
    return $ip;
}

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…

Author
Recent Posts

Stalk Me...

Michael Gibbs

I'm a technologist at heart, assisting companies best utilize the Internet to market themselves and sell their products and services online. This includes organizations as large as Google and as small as one or two individuals.
At Over The Top and Exhibit A Communications, I've programmed solutions for Google as well as at least one other company that was later acquired by Google.

I've been CTO of an Internet SaaS company and spent my time pretty evenly between guiding the future technical strategy of the company, architecting software solutions for my dev teams, designing and running a data center to service our clients world-wide as well as being a technical evangalist/sales engineer to our media clients large and small.

I've also been Chief Photographer of the Daily Sun/Post newspaper back when it was a 5-day a week daily newspaper.

I also spent a great many years as a beach lifeguardfor the City of San Clemente as well as Jr. Lifeguard instructor and then as it's program coordinator.
Bragging rights
Private Pilot with Instrument rating and proud husband of soon to be 25 years (and counting).

Stalk Me...

Related