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.

  2. timwii said on May 16, 2017 at 11:45 pm

    thank you for your post
    but i have a question; how can i check required input before write it to google sheet? Pls help me

    • Tom Woodward said on May 25, 2017 at 6:58 pm

      You’d probably want to do that with some javascript . . . you can see some basics for that here.

  3. Lauren said on June 15, 2018 at 12:56 pm

    Is there a way to have the field input names to the form point to another sheet and be able to add input fields to the form?

    Use case: I want to build a super basic order form that has 100 items on it for restaurant stock orders that get placed daily for ingredients. The form user is the restaurant manager, but the warehouse manager needs to be able to add/remove items from the form. Do you think this could be accomplished with this solution? Currently I developed a stock ordering system within google sheets itself where the restaurant manager fills out the form and then hits a button to run a script that copies that data over to another sheet, export it to PDF and send it to the warehouse manager. The issues we’re having is with UI for the restaurant managers, accidentally adding rows/sheets and breaking the code. I haven’t been able to find a simple way to create a form for them, when it basically just needs to be a fillable paper order sheet. Most forms aren’t user friendly for that many items.

    • Tom Woodward said on June 18, 2018 at 9:49 am

      Yeah, you could do something like that by building the form fields/drop-down elements via the JSON created by another form. It’s more complex but no reason it shouldn’t work.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Trackbacks and Pingbacks on this post

No trackbacks.

TrackBack URL