How to Create Data Entry Form with Google HTML Service and Submit Data to Google Sheets

Data entry is one of the important steps in the data analysis process. It is important that the data collected is correctly entered into the electronic format. One of the ways that you can maintain the data quality is creating an efficient form that can accurately enter your data. A correct data entry form is a great way to accelerate data entry and boost productivity. This can reduce entering inconsistent and inaccurate data which affects analysis negatively.

The data entry forms are even helpful when they are available online. With online forms, you can allow your data entry operators to work from home. You can implement tracking methods to monitor their performance. So, you can do the data entry work more easily and efficiently with online forms.

Google Forms is the most popular online survey tool which is available free of charge. However, when it comes to mass data entry works, it is not that supportive. Instead of using Google Forms, you can build your own data entry form with Google HTML Service. With HTML service, you can have your own design, input fields, validations and save your data to Google Sheet. And also you can embed your form in Google Sites and build your own web app.

In this post, I will show you how you can create the following simple data entry form with Google Apps Scripts and submit the data into Google Sheets.

Step 01: Create a new Google Sheet on your Google Drive

Create a new Google Sheet and add column labels as shown in the below image (these column names are not used in the programme, it is only to identify the data stored underneath)

Step 02. Creating the HTML form

Open the script editor from Tools → Script editor in your Google Sheet.

Then, replace code in the Code.gs file with the following code.

function doGet(request) {
  return HtmlService.createTemplateFromFile('Index').evaluate();
}

Next, create a new HTML file in the script editor from, File → New → HTML file. name it as “Index“. Then replace the auto-generated code with the following.

<!DOCTYPE html>
<html>
    <head>
        <base target="_top">
        <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous">
    </head>
    <body>
        <div class="container">
            <div class="row">
                <div class="col-6">
                    <form id="myForm" onsubmit="handleFormSubmit(this)">
                        <p class="h4 mb-4 text-center">Contact Details</p>

                        <div class="form-row">
                            <div class="form-group col-md-6">
                                <label for="first_name">First Name</label>
                                <input type="text" class="form-control" id="first_name" name="first_name" placeholder="First Name">
                            </div>
                            <div class="form-group col-md-6">
                                <label for="last_name">Last Name</label>
                                <input type="text" class="form-control" id="last_name" name="last_name" placeholder="Last Name">
                            </div>
                        </div>

                        <div class="form-row">
                            <div class="form-group col-md-6">
                                <p>Gender</p>
                                <div class="form-check form-check-inline">
                                    <input class="form-check-input" type="radio" name="gender" id="male" value="male">
                                    <label class="form-check-label" for="male">Male</label>
                                </div>
                                <div class="form-check form-check-inline">
                                    <input class="form-check-input" type="radio" name="gender" id="female" value="female">
                                    <label class="form-check-label" for="female">Female</label>
                                </div>
                            </div>
                            <div class="form-group col-md-6">
                                <label for="dateOfBirth">Date of Birth</label>
                                <input type="date" class="form-control" id="dateOfBirth" name="dateOfBirth">
                            </div>
                        </div>

                        <div class="form-group">
                            <label for="email">Email</label>
                            <input type="email" class="form-control" id="email" name="email" placeholder="Email">
                        </div>

                        <div class="form-group">
                            <label for="phone">Phone Number</label>
                            <input type="tel" class="form-control" id="phone" name="phone" placeholder="Phone Number">
                        </div>

                        <button type="submit" class="btn btn-primary btn-block">Submit</button>
                    </form>
                    <div id="output"></div>
                </div>
            </div>      
        </div>
    </body>
</html>

Now the HTML form is ready and you can deploy it as a web app by going to Publish → Deploy as a web app… Click the deploy button on the dialog box and copy the link in the next dialog box. Paste the link in the browser, then you can see the form we created.

In order to add styles to the form, I have loaded Bootstrap CSS via CDN (You can see it in line 5 of the above code).

Step 03. Sending Form Data to Google Sheet

Using google.script.run asynchronous client-side JavaScript API, we can call server-side Apps Script functions from HTML-service pages.

The following JavaScript, calls the server-side function processForm once you click the submit button. To add this JavaScript, create a new HTML file and name it ass JavaScript. Then replace the auto-generated code with the following.

<script>
  // Prevent forms from submitting.
  function preventFormSubmit() {
    var forms = document.querySelectorAll('form');
    for (var i = 0; i < forms.length; i++) {
      forms[i].addEventListener('submit', function(event) {
      event.preventDefault();
      });
    }
  }
  window.addEventListener('load', preventFormSubmit);    
      
      
  function handleFormSubmit(formObject) {
    google.script.run.processForm(formObject);
    document.getElementById("myForm").reset();
  }
</script>

Then add the following code just before the </head> tag of the Index.html file. This code calls the server-side function include() and includes the above JavaScript in the head section of the Index.html file.

<?!= include('JavaScript'); ?>

The function preventFormSubmit() in the above JavaScript code change the default form behavior and it prevents the form redirecting to an inaccurate URL. Once you click the submit button, the above javascript calls the server-side function handleFormSubmit() with the form data.

Then add the following two server-side functions to the Code.gs file we created at the beginning and replace the URL with your Google Sheet URL.

/* @Include JavaScript and CSS Files */
function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename)
      .getContent();
}

/* @Process Form */
function processForm(formObject) {
  var url = "https://docs.google.com/spreadsheets/d/1esdBN1frPQoo-rKi3-cg7-Zk4H9ZwWkonU7glN5p-so/edit#gid=0";
  var ss = SpreadsheetApp.openByUrl(url);
  var ws = ss.getSheetByName("Data");
  
  ws.appendRow([formObject.first_name,
                formObject.last_name,
                formObject.gender,
                formObject.dateOfBirth,
                formObject.email,
                formObject.phone]);
}

** Important! Replace the URL (line 9) with the URL of your spreadsheet.

Your final code arrangement should look like bellow.

You can see the live form from this link. (Data Sheet)

You can make a copy of the above Google Sheet (with the code) from this link.

Step 04. Deploying a script as a web app

Now you can deploy the above code as a web app by going to Publish → Deploy as a web app… Then select New for the project version and click update. Copy the Current web app URL in the next dialog box and click OK. Paste the copied URL in the browser to get the form. You can read more about Deploying a script as a web app from this Google Apps Script Guide.

The following video explains how to copy the above Google Sheets to your drive and to deploy the Apps Script as a web app.

What Next?

In the above code, I have not added form validations. In order to improve the data quality, you need to add data validation for your critical fields. You can add form validation either from the server-side or client-side.

You can also embed this form on a Google Site. Read more about Embedding your web app in Google Sites from this Google Apps Script guide.

References

  1. HTML Service: Communicate with Server Functions
  2. HTML Service: Best Practices

43 thoughts on “How to Create Data Entry Form with Google HTML Service and Submit Data to Google Sheets”

  1. This is a wonderful tutorial and has helped me immensely. I do have a couple of questions. First, my form does not clear after I hit the submit button. How can I make it clear automatically? Second, is there a way to use the same form to perform a search of the spreadsheet that the form sends information to, in order to update/modify information that already exists?

    Reply
  2. Brilliant example. I really like it and it works like a charm.
    It seems impossible to append any values to the form data transferred with this method, though. Do you know of a way to obtain a submitter’s identifier (e.g. e-mail) and carry that with the form data?

    Reply
    • Thanks, Jes, for your comment.
      I could not find a way to get the email. Apparently that is not allowed. (Even Google form you cannot do it, you have to request it from the user). You can add form validations and make it mandate to submit an email.
      If you want to add all CRUD functions you have to rewrite this with Google Sheets APIs.

      Reply
  3. Thanks for this brilliant example.
    Is it possible to add a drop down list for inputs, similar to the form example you maded in Google sheets?

    Reply
  4. Thanks for the extensive example.

    I have made a copy of your sheet and form and published it.
    After pressing “Submit” the web page gets blank. It seems that the script not is able to access the google sheet.
    I use the URL from the adress line when I have the google sheet open for editing.
    I have validated that the script have access to the sheet.
    Do you have any suggestions to what I have done wrong

    Reply
  5. Thanks again for this helpful example.

    Could you in addition show how to add the current date time value for each row.
    I have added a script to the sheet to do this, but it only work when I edit the sheet direct.

    Best regards

    Reply
    • Add new Date() after line 23 of the code.gs file.


      ws.appendRow([formObject.first_name,
      formObject.last_name,
      formObject.gender,
      formObject.dateOfBirth,
      formObject.email,
      formObject.phone,
      new Date()]);

      Reply
  6. good tutorial but would be better if had validation and response dialog box. also it requires users to login with gmail which is not a good idea.

    Reply
  7. Thanks for this super interesting tutorial! A question: how would one combine the dependent drop down list with the form? That is, how does one incorporate the dependent dropdown into the form? For example, many forms would request the user complete a city and state. If the user selects a particular state, how would the cities be filtered to just show cities in that state?

    Reply
  8. Thank you for this. But how can I make the fields required? I tried to append required at the end of every input type but it’s not working.

    Reply
  9. Something else that tripped me up was the sheet name (line 11 in processForm).

    var ws = ss.getSheetByName(“Data”);

    So if you’re submitting your form and no data is appearing in your spreadsheet, make sure you’ve updated the value in line 11 to match your tab name (or name your tab “Data”).

    Reply
  10. I see, but i need to update the dropdown list from answers of another form
    You said it was possible, do you know where can i learn how to do it ?
    Thanks for your answer !

    Reply
  11. Hi – thanks for the tutorial – it looks really interesting and just the kind of thing I want to get to grips with but I am stuck at part 2 – showing the form we have created.
    The form will not appear when I copy and paste the link to a new chrome tab, instead returning ”Script function not found: doGet ”
    but if I then click ‘Test web app for your latest code.’ – it generally works.
    Sometimes it will only work after i debug the script.

    I am really new to this and am unsure what I am doing wrong but if you can help to get me back on track that would be fantastic

    regards Nick

    Reply
    • Why don’t you make a copy of the Google Sheets and start from there? You can make a copy of the Google Sheet from this link. See the video for step by step guide. Regards.

      Reply
  12. Hi, I’ve wonder how can I make the same application but the function is for searching data from a google sheet? I am a teacher and I want to make an application using google scripts so my students can check their score test by input their ID in that application. Thanks

    Reply
    • Hi Baitullah,
      Try the following Google Sheet, which I have modified to match your requirements. You can make a copy of the Google Sheet to your Google Drive and start customizing it. Look at the comments in the script and change the script accordingly.
      Google Sheet: Link to Google Sheet
      Demo: Live Form

      Reply
  13. Hi Admin,

    I’m not a programmer but I look like one of the best scripts I’ve found on the web. It has a clean code.
    Of course, a base on which to develop, each according to their needs.
    In this regard, I wanted to ask you if you can see an example of checking an input data and sending an error message.
    Thank you

    Reply
  14. I am trying to embed this on my google site and I am getting this error “can’t embed due to provider site permissions” ,changing the permissions when I publish does not help

    Reply
  15. Hello! Sir,

    Is there a way to call back data from that Google Sheets then we can edit existing data and save/update a new information?

    Thank you very much.

    Reply
  16. Hi Sir I am a teacher and I want to make an application using google scripts so my students Result data spreadsheet with Stud Roll , Class , Birth date , stud name , and subject – wise Marks . Can i Use the Above to Create a Search For Exam Marks Based on Stud Roll , Class , & Birth date ?
    if yes further can i merge this search with an html page?

    Reply

Leave a Reply

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