GAS HtmlService Example to Submit Data to Google Sheets Using HTML5 Form in Browser Using Javascript

Welcome folks today in this blog post we will be submitting data from the html5 form to google sheets using the htmlservice in google apps script. All the full source code of the application is shown below.

Get Started

In order to get started you need to create the new google sheet and then you need to write the below column names as shown below

Now we need to go to tools and select the App script option to write the google apps script code. Now inside your code.gs file copy paste the following code

code.gs

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

function doGet(request) {

return HtmlService.createTemplateFromFile('Index').evaluate();

}

As you can see in the above code we are writing the doGet function which automatically executes whenever user loads the home page of this web app. We will be showing the Index.html file which is present inside the root directory. So now we need to create the Index.html file as shown below

Now you need to copy paste the below code inside the Index.html file as shown below

Index.html

<!DOCTYPE html>
<html>
  <head>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.5.3/dist/css/bootstrap.min.css"
 integrity="sha384-TX8t27EcRE3e/ihU7zmQxVncDAy5uIKz4rEkgIXeMed4M0jlfIDPvg6uqKI2xXr2" 
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>

<!DOCTYPE html>

<html>

<head>

integrity="sha384-TX8t27EcRE3e/ihU7zmQxVncDAy5uIKz4rEkgIXeMed4M0jlfIDPvg6uqKI2xXr2"

crossorigin="anonymous">

</head>

<body>

<p class="h4 mb-4 text-center">Contact Details</p>

<label for="first_name">First Name</label>

</div>

</div>

<p>Gender</p>

</div>

<label class="form-check-label" for="female">Female</label>

</div>

<label for="dateOfBirth">Date of Birth</label>

</div>

<label for="email">Email</label>

</div>

<label for="phone">Phone Number</label>

</div>

<button type="submit" class="btn btn-primary btn-block">Submit</button>

</form>

</div>

</body>

</html>

As you can see we have the bootsrap 5 html5 user form where we have different input fields for collecting the username,email,phone number and date of birth. Now we need to deploy this web app inside the browser to see the output as shown below

After deployment you will copy paste the url that it gives you now if you open it you will see the below html5 form as shown below

Now we need to write the javascript code when we submit the form guys we need to include the javascript code for this we need to create the JavaScript.html file inside the root directory as shown below

JavaScript.html

  <script>

  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){
  console.log(formObject)
  google.script.run.processForm(formObject);
  document.getElementById("myForm").reset();
}


</script>

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){

console.log(formObject)

google.script.run.processForm(formObject);

document.getElementById("myForm").reset();

}

</script>

As you can see we are first of all preventing the auto behaviour of all the form input elements. And then we are adding the event listener when we load the page we are executing this function which is preventSubmit() and then we are making the handleFormSubmit() function to handle what happens with the information submitted by the user. We are calling the method which is processForm() which we will write inside the code.gs file. We are using the run() method inside the htmlservice to call the external method inside the google app script. And then we are reseting the html5 form values.

First of all we need to add this javascript.html file inside the index.html file for this we need to modify the index.html file as shown below

<html>
  <head>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.5.3/dist/css/bootstrap.min.css"
 integrity="sha384-TX8t27EcRE3e/ihU7zmQxVncDAy5uIKz4rEkgIXeMed4M0jlfIDPvg6uqKI2xXr2" 
crossorigin="anonymous">
<?!= include('JavaScript'); ?>
  </head>

<html>

<head>

integrity="sha384-TX8t27EcRE3e/ihU7zmQxVncDAy5uIKz4rEkgIXeMed4M0jlfIDPvg6uqKI2xXr2"

crossorigin="anonymous">

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

</head>

Just add this include method in between the head and the body. Essentially this is the function we are calling inside the code.gs which is include and in the argument we are passing the filename. Now we need to define this function in code.gs file as shown below

code.gs

function include(filename){
  return HtmlService.createHtmlOutputFromFile(filename).getContent();
}

function include(filename){

return HtmlService.createHtmlOutputFromFile(filename).getContent();

}

Now we need to add the function which is processForm() inside the code.gs file as shown below

code.gs

function processForm(formObject){
  var url="####yourspreadsheeturl####";
  var ss= SpreadsheetApp.openByUrl(url);
  console.log(ss)
  var ws=ss.getSheetByName("Sheet1");
  console.log(ws)

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

function processForm(formObject){

var url="####yourspreadsheeturl####";

var ss= SpreadsheetApp.openByUrl(url);

console.log(ss)

var ws=ss.getSheetByName("Sheet1");

console.log(ws)

ws.appendRow([

formObject.first_name,

formObject.last_name,

formObject.gender,

formObject.dateOfBirth,

formObject.email,

formObject.phone

]);

}

As you can see we are receiving the form object which contains all the information submitted by the user inside he html5 form. And then we are selecting the google sheet and then we are opening the url using the openByUrl() method and then we are selecting the current sheet and then we are appending a new row inside the google sheet using the appendRow() method

Now again we need to deploy a new version of the app so that we can refresh the web app. It is done as shown below

Now if you submit the information inside the html5 form guys you will see the information will be submitted to the google sheets as shown below