Skip to content

Instantly share code, notes, and snippets.

@dhcole
Created November 3, 2012 19:31
Show Gist options
  • Star 32 You must be signed in to star a gist
  • Fork 10 You must be signed in to fork a gist
  • Save dhcole/4008406 to your computer and use it in GitHub Desktop.
Save dhcole/4008406 to your computer and use it in GitHub Desktop.
Submit data from html form to Google Doc Spreadsheet. Uses Bootstrap components for auto-complete region list and date selection.
$(function(){
var formUrl = '/* ex: https://docs.google.com/a/developmentseed.org/spreadsheet/formResponse?formkey=... */';
// Set up map
var m = mapbox.map('map').addLayer(mapbox.layer().id(' /* mapbox-account.id */ '));
// Set up map ui features with point selector
var ui = mapbox.ui().map(m).auto().pointselector(function(d) {
// Remove all points except the most recent
for (var i = 0; i < d.length - 1; i++) {
var locs = ui['_pointselector'].locations();
ui['_pointselector'].deleteLocation(locs[i]);
}
saveLatLon(d[0]);
});
// Get region data from google spreadsheet and set up LGA typeahead
mapbox.converters.googledocs('/* spreadsheet key */', '/* sheet number */', typeAhead);
// Set up date pickers
// Uses http://www.eyecon.ro/bootstrap-datepicker
var now = new Date();
now = now.getMonth() + '/' + now.getDate() + '/' + now.getFullYear();
$('/* Selectors for date fields */').val(now).datepicker();
// Handle form submission
$('form').submit(function(e) {
var button = $('input[type=submit]', this),
data = $(this).serialize();
e.preventDefault();
if (validate($(this))) {
button.button('loading');
$.ajax({
type: 'POST',
url: formUrl,
data: data,
complete: function() {
button.button('reset');
// After submission, redirect to main page
window.location = 'index.html#new';
}
});
}
// All fields are required
function validate(form) {
$('.control-group').removeClass('error');
$('input, textarea', form).each(function() {
var tag = $(this)[0].tagName.toLowerCase(),
type = $(this).attr('type');
// Validate radio buttons
if (tag === 'input' && type === 'radio') {
var name = $(this).attr('name');
if ($('[name="' + name + '"]:checked').length < 1) {
$(this).parent().parent().parent().addClass('error');
}
}
// Validate text fields
if ((tag === 'input' && type === 'text') || tag === 'textarea') {
if ($(this).val() === '' && !$(this).parent().hasClass('radio')) {
$(this).parent().parent().addClass('error');
}
}
});
if ($('.control-group.error').length < 1) return true;
$('.control-group.error').length
$('html, body').animate({
scrollTop: $('.control-group.error').offset().top - 20
}, 500);
return false;
}
});
// Use typeahead to select region
// Uses http://twitter.github.com/bootstrap/javascript.html#typeahead
function typeAhead(features) {
var lgas = [];
// Pluck `region, state` values
for (var i = 0; i < features.length; i++) {
lgas.push(features[i].properties.lgastate);
}
$('/* Element for typeahead */').typeahead({source: lgas}).change(function() {
var position = $.inArray($(this).val(), lgas);
if (position >= 0) {
var coords = features[position].geometry.coordinates,
loc = { lon: coords[0], lat: coords[1] };
saveLatLon(loc);
m.center(loc).zoom(7);
$('#map-control').show();
}
});
}
function saveLatLon(loc) {
$('#entry_1').val(loc.lon);
$('#entry_2').val(loc.lat);
}
});
@dhcole
Copy link
Author

dhcole commented Nov 3, 2012

Background on using custom forms for Google Docs: http://mapbox.com/blog/2012-09-05-mapping-crowdsourced-locations-google-docs/

Background on mapping a Google Docs spreadsheet with MapBox.js: http://developmentseed.org/blog/2012/sept/13/mapping-dynamic-data-google-spreadsheet/

@dhcole
Copy link
Author

dhcole commented Nov 3, 2012

Form markup should match id and name with a standard Google Doc form, and there should be no validation on the Google Doc form, so submissions will not be filtered. ex:

<input type="text" class="input-xlarge" id="entry_0" name="entry.0.single">

@selasegithub
Copy link

Hi Dhcole, thanks for your post. It was really helpful in getting me to integrate my custom forms with google sheets however, I have problems getting the textarea, radio button and text input validations to work. I cant figure out what i am doing wrong. I also noticed that each time my form is submitted and i click the google link to submit another entry, it reverts to the default google form instead of my custom form. Is there any work around this and kindly please take a look at my code below and offer some tips on getting the validations to work. thanks.

 <script type="text/javascript">
    // Handle form submission
    $('#ss-form').submit(function(e) {
        var button = $('input[type=submit]', this),
            data = $(this).serialize();

        e.preventDefault();
        if (validate($(this))) {
            button.button('loading');
            $.ajax({
                type: 'POST',
                url: "https://docs.google.com/forms/d/1HNUn0m70hGAiewLV2WfarDtu4kwLJoJrmtS0PjX3RAM/formResponse",
                dataType: "xml",
                data: data,
                complete: function() {
                    button.button('reset');
                    // After submission, redirect to main page
                    window.location = 'http://qwameselase.wix.com/black-n-beautiful#!contact/c1n3j';
                }
            });
        }

        // All fields are required
        function validate(#ss-form) {
            debug: true,
            errorClass:'error help-inline',
            validClass:'success',
            errorElement:'span',        
            $('.control-group').removeClass('error');
            $('input, textarea', #ss-form).each(function() {

                var tag = $(this)[0].tagName.toLowerCase(),
                    type = $(this).attr('type');

                // Validate radio buttons
                if (tag === 'input' && type === 'radio') {
                    var name = $(this).attr('name');
                    if ($('[name="' + name + '"]:checked').length < 1) {
                        $(this).parent().parent().parent().addClass('error');
                    }
                }

                // Validate text fields
                if ((tag === 'input' && type === 'text') || tag === 'textarea') {
                    if ($(this).val() === '' && !$(this).parent().hasClass('radio')) {
                        $(this).parent().parent().addClass('error');
                    }
                }

                // Validate email fields
                if (tag === 'input' && type === 'email'){
                    function validateEmail(email) {
                        var re = /^(([^<>()[\]\\.,;:\s@\"]+(\.[^<>()[\]\\.,;:\s@\"]+)*)|(\".+\"))@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\])|(([a-zA-Z\-0-9]+\.)+[a-zA-Z]{2,}))$/;
                        return re.test(email);
                    }

                }

            });

            if ($('.control-group.error').length < 1) return true;
            $('.control-group.error').length

            $('html, body').animate({
                scrollTop: $('.control-group.error').offset().top - 20
            }, 500);

            return false;
        }
    });
</script>

<form id="ss-form" class="form-inline" method="POST" onsubmit="" action="https://docs.google.com/forms/d/1HNUn0m70hGAiewLV2WfarDtu4kwLJoJrmtS0PjX3RAM/formResponse?embedded=true">
    <fieldset>
        <legend>Personal Details</legend>
        <div class="control-group">
            <label class="control-label" for="entry_244604360">First Name</label>
                <div class="controls">
                    <input id="entry_244604360" type="text" name="entry.244604360" value="" maxlength="100">
                </div>
        </div><br>
        <div class="control-group">
            <label class="control-label" for="entry_403969165">Last Name</label>
                <div class="controls">
                    <input id="entry_403969165" type="text" name="entry.403969165" value="" maxlength="100">
                </div>
        </div><br>
        <div class="control-group">
            <label class="control-label" for="entry_1520202759">E-Mail</label>
                <div class="controls">
                    <input id="entry_1520202759" type="email" name="entry.1520202759" value="">
                </div>
        </div><br>
        <div class="control-group">
            <label class="control-label" for="entry_294268486">Contact Number</label>
                <div class="controls">
                    <input id="entry_294268486" type="text" name="entry.294268486" value="" maxlength="15">
                </div>
        </div>                                          
    </fieldset><Br>
    <fieldset>
        <legend>Canvas Type</legend>
        <div class="control-group">
            <label for="entry_1505307714">Canvas Type</label><br><br>
                <input id="group_1123680937_1" type="radio" name="entry.1123680937" value="Booking Only">Book a spot, pick canvas at event RM 50<br><br><br>
                <label>
                    <input id="group_1123680937_2" type="radio" name="entry.1123680937" value="4x4single" />
                    <img src="http://static.wixstatic.com/media/253d02_dec03f3ad51c403e945281f6ee98240f.jpg_srb_p_600_556_75_22_0.50_1.20_0.00_jpg_srb" style="max-width:15%" alt="40x40x1 / Single / RM 195">
                </label>
                <label>
                    <input id="group_1123680937_3" type="radio" name="entry.1123680937" value="4x4diptych" />
                    <img src="http://static.wixstatic.com/media/253d02_805d3463f30f48d9b814791099c23a95.jpg_srb_p_746_378_75_22_0.50_1.20_0.00_jpg_srb" style="max-width:30%" alt="40x40x2 / Diptych / RM 325">
                </label>
                <label>
                    <input id="group_1123680937_4" type="radio" name="entry.1123680937" value="4x4triptych" />
                    <img src="http://static.wixstatic.com/media/253d02_133bac604e744812aaa82e8c11f6d63b.jpg_srb_p_1106_409_75_22_0.50_1.20_0.00_jpg_srb" style="max-width:40%" alt="40x40 / Triptych / RM 455">
                </label><br><br><br>
                <label>
                    <input id="group_1123680937_5" type="radio" name="entry.1123680937" value="4x9single" />
                    <img src="http://static.wixstatic.com/media/253d02_add84feb205b4ffe9d2a31bf6ff5d7a6.jpg_srb_p_600_567_75_22_0.50_1.20_0.00_jpg_srb" style="max-width:15%" alt="40x90 / Single / RM 350">
                </label>
                <label>
                    <input id="group_1123680937_6" type="radio" name="entry.1123680937" value="4x9diptych" />
                    <img src="http://static.wixstatic.com/media/253d02_22045721297f4b3c9e8f1c46d9bb0bec.jpg_srb_p_600_567_75_22_0.50_1.20_0.00_jpg_srb" style="max-width:30%" alt="40x90 / Diptych / RM 610">
                </label>
                <label>
                    <input id="group_1123680937_7" type="radio" name="entry.1123680937" value="4x9triptych" />
                    <img src="http://static.wixstatic.com/media/253d02_cffba0d8eee742f2b24c815b310240f0.jpg_srb_p_704_525_75_22_0.50_1.20_0.00_jpg_srb" style="max-width:40%" alt="40x90 / Triptych / RM 865">
                </label><br><br><br>
                <label>
                    <input id="group_1123680937_8" type="radio" name="entry.1123680937" value="6x10single" />
                    <img src="http://static.wixstatic.com/media/253d02_28767a3b1e694403846cb80f04ab1338.jpg_srb_p_600_567_75_22_0.50_1.20_0.00_jpg_srb" style="max-width:20%" alt="60x100 / Single / RM 525">
                </label><br>

        </div>
    </fieldset><Br>
    <fieldset>
        <legend>Remarks</legend>
        <div>
            <label for="entry_508497383">Comments</label><br>
            <textarea id="entry_508497383" rows="3" cols="100" name="entry.508497383" value=""></textarea>
        </div>
    </fieldset><Br>
    <fieldset>
        <legend>Get in Touch</legend>
        <div>
            <label for="entry_1014633536">Notify me of upcoming workshops?</label><br>

            <input id="group_1558721004_1" type="radio" name="entry.1558721004" value="Yes" /> Yes <Br>             
            <input id="group_1558721004_2" type="radio" name="entry.1558721004" value="No" /> No <Br>           
        </div>
    </fieldset><Br><Br>
    <div style="text-align: center;">
        <input type="submit" name="submit" value="Submit" id="ss-submit">
    </div>
</form>

@mohamednizar
Copy link

Excellent, I used it on my static contact form.Its save my time on writing server scripts.

@Steve-Pfeiffer
Copy link

Hi, dhcole! I'm new to GitHub... may I use this code anywhere? Do I need to cite you in some way? Thanks!

@ziaongit
Copy link

ziaongit commented Feb 9, 2018

I was strugling with that from last few days, nothing was working. At the end I found a very good solution.
Enjoy!

http://www.nanoblogsonline.com/2018/02/how-to-submit-html-form-to-google-sheet.html

@czimbalm
Copy link

Please can somebody help me?

I have set up an order form in php, then after posting the form I have check.php file which displays the order information.
I have set up the google sheets, with the script (working fine), but how can I get the order data to be written in the google sheets?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment