Custom Web Form to Google Sheets

Sometimes the options given in Google Forms just won’t quite work for what you want to do. Maybe you want a particular look, or an interaction, or whatever that Google Forms just won’t do.

Luckily, it’s not too hard to make a custom form that can do whatever you want and still has the ability to write the submitted data to a Google Spreadsheet and the form HTML is still served by Google. The following steps should get you up and running and comments in the scripts should provide additional details.

Make a new spreadsheet in Google Sheets.

Screen Shot 2016-07-19 at 8.22.56 AM
Go to Tools>Script Editor

Select all that stuff and replace it with the content below. Replace the string of ****** with the ID of your spreadsheet. Then save it. If you get any permissions prompts approve them.

//basic function that builds the form using index.html as the template
function doGet(e) {
     return HtmlService
     .createTemplateFromFile('index')
     .evaluate()
     .setSandboxMode(HtmlService.SandboxMode.NATIVE);
}

function writeForm(form) {
  try {  
    var distinguishedName = form.distinguishedName; //these match to the named fields in your form
    var moreDetails = form.moreDetails;

    var ss = SpreadsheetApp.openById('**********************************************'); //the ID of the spreadsheet you want to write to
    var sheet = ss.getActiveSheet();
    var newRow = sheet.getLastRow()+1;//go to the first blank row           
    
    //writes the form data to the spreadsheet
    var range = sheet.getRange(newRow, 1);    
    range.setValue(distinguishedName);
    range = sheet.getRange(newRow, 2);
    range.setValue(moreDetails);
    
   //an array of confirmation messages that will display as HTML 
    var confirmationMessage = ['<img src="http://bionicteaching.com/wp-content/uploads/2010/12/powerogskull.gif"><h2>A statement!</h2>', 
                               '<img src="http://bionicteaching.com/wp-content/uploads/2011/01/beachsomethingsomething.gif"><br><a href="https://pinboard.in">A Link</a>',
                               '<img src="https://c2.staticflickr.com/8/7486/27750752903_08b9a7daaa_s.jpg">'
                              ];
    var len = confirmationMessage.length-1;
    Logger.log('len= ' + len);
    var i = Math.floor(Math.random() * len);//randomizes from the array
    
    return confirmationMessage[i]; //displays randomized message
  } catch (error) {
    
    return error.toString();
  }
}

Make a new HTML page (File>New>HTML File) and name it index.html
Screen Shot 2016-07-19 at 8.25.10 AM

Select all and replace it with this.1 Save it.

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">

<style>div {margin: 10px;}</style>

  </head>
  <body>

<h2>A FORM</h2>

<form id="genericForm">

<div class="form-group">

<div>
    <label for="distinguishedName">A Distinguished Name</label>
    <input type="text" name="distinguishedName" class="form-control" id="distinguishedName" placeholder="A Fancy Name">
</div>


<div>
    <label for="moreDetails">Details</label>    
    <input type="text" name="moreDetails" id="moreDetails" placeholder="Additional Secrets">
</div>

</div>

 <input type="submit" onclick="this.value='Magic ...'; google.script.run.withSuccessHandler(formSubmitted) .writeForm(this.parentNode); return false;">
 
</form>



<div id="output"></div><!-- where the confirmation message goes -->

<script>
   function formSubmitted(status) {
        document.getElementById('genericForm').style.display = 'none'; //matches your form name or whatever you want to disappear post-submission
        document.getElementById('output').innerHTML = status; //displays in item with the 'output' id
    }
</script>    

  </body>
</html>

To make sure things work, let’s publish it (Publish>Deploy as Web App). Now go to that URL and submit something and see if it goes to the spreadsheet. If so, great. Now you can start customizing the form to reflect your needs.
Screen Shot 2016-07-19 at 10.52.44 AM

Screen Shot 2016-07-19 at 10.53.01 AM

This form should now write to a spreadsheet like this.

Do keep in mind that each form field you want to write to the sheet has to be mapped in the script. This is what’s needed for the moreDetails form field.

var moreDetails = form.moreDetails; //gets the form data by name
range = sheet.getRange(newRow, 2); //destination for the data
range.setValue(moreDetails); //writes the data to it

1 This is as parsed down as I could get it so it’s easier to understand (I hope).

Comments on this post

  1. brandons92 said on July 29, 2016 at 2:53 pm

    Hey,

    Firstly thank you for writing this, very helpful.

    I’ve got it working and it’s all great, but my question is whether I can take the code out of script.google & use it on my own site? I’ve tried simply copying the code to my own index.html file but the submit button doesn’t work.

    Am I misunderstanding what you can use this for or simply not using the code correctly?

    Thanks in advance.

    • Tom Woodward said on August 2, 2016 at 8:12 am

      Yeah- It won’t work as standalone HTML because of the relationship between the script and the spreadsheet. You could just iframe it into your page or you can take it up a level and use the external API.

Leave a Reply

Trackbacks and Pingbacks on this post

No trackbacks.

TrackBack URL