Google Forms: Send data to spreadsheet

Question

How can I send the data from a webform to a google spreadsheet? I made a form with Google Drive, but to get custom CSS running, I need to copy the form tag.

In my case, that is what google generated for the send button behavior

<form action="https://docs.google.com/forms/d/113H_71nd98TWE0bByjHYNpnC-
 oVA6OBDWtppU30rBrU/formResponse" method="POST" id="ss-form" target="_self" onsubmit="">

However, I want to post data from my own designed form to the above Google Form Response spreadsheet. Here is my form code (using Bootstrap 3):

<form class="form-horizontal" role="form" id="ftk-contact">
<h4>Get in touch with us now</h4>
<div class="form-group">
<label for="inputType" class="col-lg-2 control-label">Type of Inquiry</label>
<div class="col-lg-10">
<select class="form-control" id="inputType">
<option>Request a Quotation</option>
<option>Request a Bluebook</option>
<option>General Inquiry</option>
</select>
</div>
</div>

<div class="form-group">
<label for="inputName" class="col-lg-2 control-label">Name *</label>
<div class="col-lg-10">
<input type="text" class="form-control" id="inputName" placeholder="Your Name">
</div>
</div>

<div class="form-group">
<label for="inputEmail" class="col-lg-2 control-label">Email *</label>
<div class="col-lg-10">
<input type="email" class="form-control" id="inputEmail" placeholder="Your Email">
</div>
</div>

<div class="form-group">
<label for="inputCompany" class="col-lg-2 control-label">Company</label>
<div class="col-lg-10">
<input type="text" class="form-control" id="inputCompany" placeholder="Your Company Name">
</div>
</div>

<div class="form-group">
<label for="message" class="col-lg-2 control-label">Message *</label>
<div class="col-lg-10">
<textarea class="form-control" rows="5" placeholder="Your Message"></textarea>
</div>
</div>

<div class="form-group">
<label for="inputPhone" class="col-lg-2 control-label">Phone</label>
<div class="col-lg-10">
<input type="tel" class="form-control" id="inputPhone" placeholder="Your Phone Number">
</div>
</div>

<div class="form-group">
<label for="inputWeb" class="col-lg-2 control-label">URL</label>
<div class="col-lg-10">
<input type="url" class="form-control" id="inputWeb" placeholder="Your Website URL">
</div>
</div>

<div class="form-group">
<div class="col-lg-offset-2 col-lg-10">
<button type="submit" class="btn btn-primary btn-lg">Send your Inquiry now</button>
</div>
</div>
</form>

When using the above Google form action=... I am taken to the original Google Form when pressing send, instead of the form entries being copied to the spreadsheet.

If the above approach wont work, how else can I send the form data to email or Google Drive?

Answer 1

Complete Step-by-step Instructions

After reading Martin Hawskey's good introduction (to sending data from an HTML form to a Google Spreadsheet) and seeing a few gaps/assumptions, we decided to write a detailed/comprehensive tutorial with step-by-step instructions which a few people have found useful:

https://github.com/dwyl/html-form-send-email-via-google-script-without-server

The script saves any data sent via HTTP POST in the Google Spreadsheet, and optionally forwards the content to an email address. (useful if you want to be notified of new data)

HTML Form:

Result (row in sheet):

We commented the Google Script so hopefully it's clear, but if you have any questions, don't hesitate to ask! :-)

Answer 2

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

var sheetName = 'Sheet1'  
 var scriptProp = PropertiesService.getScriptProperties()  
 function intialSetup () {  
  var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet()  
  scriptProp.setProperty('key', activeSpreadsheet.getId())  
 }  
 function doPost (e) {  
  var lock = LockService.getScriptLock()  
  lock.tryLock(10000)  
  try {  
   var doc = SpreadsheetApp.openById(scriptProp.getProperty('key'))  
   var sheet = doc.getSheetByName(sheetName)  
   var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]  
   var nextRow = sheet.getLastRow() + 1  
   var newRow = headers.map(function(header) {  
    return header === 'timestamp' ? new Date() : e.parameter[header]  
   })  
   sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow])  
   return ContentService  
    .createTextOutput(JSON.stringify({ 'result': 'success', 'row': nextRow }))  
    .setMimeType(ContentService.MimeType.JSON)  
  }  
  catch (e) {  
   return ContentService  
    .createTextOutput(JSON.stringify({ 'result': 'error', 'error': e }))  
    .setMimeType(ContentService.MimeType.JSON)  
  }  
  finally {  
   lock.releaseLock()  
  }  

 }  

For more detail information click here

Answer 3

It's very simple.

1. Get your form id from FORM tag "action" property, you can get form tag at source of the Google Form web page

2. Get your field ids Ex. "entry.1472837636"

Please refer below example now.

<!DOCTYPE html>
<html lang="en">
    <head>
        <meta charset="utf-8">
        <title>CUSTOM GOOGLE FORM</title>
    </head>

    <body>
        <script src="http://code.jquery.com/jquery-latest.min.js" type="text/javascript"></script>

        <form id="input-form" action="" method="POST" target="no-target">
            <table>
                <tr>        
                    <td>Rate this help note</td>
                    <td><input type="radio" value="1" name="rating" class="rating" checked="">1</td>
                    <td><input type="radio" value="2" name="rating" class="rating">2</td>
                    <td><input type="radio" value="3" name="rating" class="rating">3</td>
                    <td><input type="radio" value="4" name="rating" class="rating">4</td>
                    <td><input type="radio" value="5" name="rating" class="rating">5</td>
                    <td><input type="radio" value="6" name="rating" class="rating">6</td>
                    <td><input type="radio" value="7" name="rating" class="rating">7</td>
                    <td><input type="radio" value="8" name="rating" class="rating">8</td>
                    <td><input type="radio" value="9" name="rating" class="rating">9</td>
                    <td><input type="radio" value="10" name="rating" class="rating">10</td>
                </tr>    
                <tr>        
                    <td>Are you satisfied from this help module ?</td>
                    <td><input type="radio" value="Yes" name="sat" class="sat" checked="">Yes</td>
                    <td><input type="radio" value="No" name="sat" class="sat">No</td>
                </tr>
                <tr><td>comments</td><td><input id="comments" name="comments"><td></tr>    
                <tr><td><button id="form-submit" type="submit">SUBMIT</button></td></tr>
            </table>
        </form>

        <p id="input-feedback"></p>

        <iframe src="#" id="no-target" name="no-target" style="visibility:hidden"></iframe>

        <script>
            jQuery('#input-form').one('submit', function() {
                var rating = encodeURIComponent(jQuery('input[name=rating]:checked').val());
                var sat = encodeURIComponent(jQuery('input[name=sat]:checked').val());
                var comments = encodeURIComponent(jQuery('#comments').val());
                var q1ID = "your-field-id";
                var q2ID = "your-field-id";
                var q3ID = "your-field-id";
                var baseURL = 'https://docs.google.com/forms/d/e/your-form-id/formResponse?';
                var submitRef = '&submit=Submit';
                var submitURL = (baseURL + q1ID + "=" + sat + "&" + q2ID + "=" + rating + "&" + q3ID + "=" + comments + submitRef);
                console.log(submitURL);
                jQuery(this)[0].action = submitURL;
                jQuery('#input-feedback').text('Thank You!');
            });
        </script>

    </body>
</html>

Answer 4

I made utility called Magic Forms to add some extra features beyond what Google's endpoints offer. Create a magic form project and it will give you an HTTP endpoint where you can POST forms to (regular HTTP or Ajax), set custom redirects on success/error, and also auto-create columns based on whatever data gets posted, which allows for some pretty flexible use cases.

Answer 5

Here's what worked for me:

1. Create your custom form
2. Create your Google form and view the source after clicking view live form
3. Copy the html code starting from <form> till </form>
4. Each of the input fields in the Google form have name and id attributes which need to be copied to your personal form
5. Use the URL in the form action of Google forms in your form.
6. Make sure that even the submit button has the ID and name attributes matching with the Google form source.
7. If you make a submit now, it will take you to the Google form response page. You can avoid this by making an ajax form submit.

If your custom form does not validate a Google form mandatory element, then you will again be redirected to the Google form page.

Answer 6

It seems that recently Google has updated its way to create forms, so now the names of the fields are in hidden inputs below the normal ones (https://github.com/heaversm/google-custom-form).

I have also tried the @nelsonic approach, and it emails the answers in JSON format properly, but it doesn't load them into the Google Spreadsheet, at least for me. That's why I wanted to combine two methods to always save the users data in case of more obfuscation changes are taken in the future by Google.

So I recommend you to have a webpage with a iframe inside of it. This iframe would contain your own custom form, from another webpage or –as in my example for convenience reasons– from itself using the srcdoc attribute.

Then, you copy the names of those hidden inputs as well as the action form url from your Google Form Preview Page and paste them into the custom form.

And finally, with Ajax we can easily send one copy of the form data to the normal Google Spreadsheet, and the other to our mail with the @nelsonic script solution.

This way, with the iframe and the Ajax, we are avoiding the url redirection to the 'Response registered' page when the form is submitted to Google, but we can hide the iframe from the parent view to be 100% sure.

I post here all my code for you to test it:

Custom Form:

<!doctype html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <title>Custom Form to Google SpreadSheets</title>
</head>

<body>
  <script type="text/javascript">
    function hideIframeAndShowThankYouMessage(){
      document.getElementById('iframe').style.display = "none";
      document.getElementById('thankyou').style.display = "block";
    }
  </script>
   <iframe id="iframe" width="760" height="500" frameborder="0" marginheight="0" marginwidth="0" srcdoc="<html><head></head><body>
   <script src='https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js'></script>
   <script type='text/javascript'>

    $(document).ready(function(){

      $('#form').submit(function(e) {

        // You could also here, for example, valid an email address input

        // It shouldn't appear, but just in case the iframe loads the Google Forms 'Answer registered' page, we hide the iframe from parent js:

        window.top.hideIframeAndShowThankYouMessage();

        // Now we send the same form to two different locations: the first is the ordinary Google Spreadsheet, the second is the Google Apps Script which allows us to receive an email with the form info in JSON format

        var url_sheet = 'https://docs.google.com/forms/d/e/1FAIpQLSdxucfxPO2TgTh4DOKTty6VCykJ6v4RX0nbKjsz1Pc5fLR9gA/formResponse';

        var url_script = 'https://script.google.com/macros/s/AKfycby2xOphkRsr8Uf3mD44-H68yC0U3bUqvKV0bxXrTISTQ7QKDxw/exec';

        $.ajax({
          type: 'POST',
          url: url_sheet,
          data: $('#form').serialize(),
          success: function(data){}
        });

        $.ajax({
          type: 'POST',
          url: url_script,
          data: $('#form').serialize(),
          success: function(data){}
        });

        e.preventDefault();

      });
    });


    </script>

    <!--
        *** TO-DO!! ***        
        1. Copy your form ACTION url from your Google Form Preview Page

        2. Replace value of var url_sheet with that form ACTION url in line 31

        3. Copy your Spreadsheet WEB APP url from Google Script Editor

        4. Replace value of url_script with that WEB APP url in line 33

        3. Look into the source of your Google Form Preview Page and search for the names of the HIDDEN fields which are each one close to the normal input type (... <input type='hidden' name='entry.314619096' jsname='L9xHkb'> ...). We don't need the jsname, only the name

        4. Replace the NAMES fields of every field of the custom form below
     --> 

   <form id='form' method='POST'>

      <label for='name'>Name: </label>
      <input type='text' name='entry.314619096' placeholder='This is easy!' />
      <br/>
      <label for='message'>Message:</label>
      <input type='text' name='entry.2039301116' placeholder='Tell me something! ;)'/>
      <br/>
      <input type='submit' value='Submit'>
  </form></body></html>">Loading...</iframe>
  <h1 id="thankyou" style="display: none">Thank you!</h1>
</body>
</html>

Hope it could help someone!