Google Sheets HTMLService Tutorial to Show Sidebar Modal Dialog Form in Google Sheets Using App Script

Welcome folks today in this blog post we will be using the htmlservice in google apps script to show the sidebar modal dialog form in google sheets. All the full source code of the application is shown below.

Get Started

In order to get started you need to create a brand new google sheet and add some columns as you can see below

Now you need to go to the tools and then you need to select the Apps script editor as shown below

And now make the index.html file inside the app script as shown below and copy paste the html code

index.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <form>
      <input type="text" placeholder="Enter Name" required/>
      <input type="number" placeholder="Enter Age" required/>
      <input type="text" placeholder="Enter Country" required/>
      <input type="submit" value="Register"/>
    </form>
  </body>
</html>

<!DOCTYPE html>

<html>

<head>

</head>

<body>

<form>

</form>

</body>

</html>

As you can see we have the simple html5 form in which we have three input fields such as the name,age and country. Now we need to show it inside the google sheets using the app script as shown below. Now we need to copy paste the javascript code inside the code.gs file

code.gs

function showForm(){
  let userForm = HtmlService.createTemplateFromFile('index').evaluate()
  SpreadsheetApp.getUi().showSidebar(userForm)
}

function showForm(){

let userForm = HtmlService.createTemplateFromFile('index').evaluate()

SpreadsheetApp.getUi().showSidebar(userForm)

}

And as you can see inside the code we are using the htmlservice class to create the html5 template from the file called index.html and then we are calling the evaluate() method and then we are showing this form inside the google sheets by using the showSidebar() method as shown below

As you can see the html5 form is added in the sidebar section in google sheets. And you can even change the title of the form to custom string as shown below

code.gs

function showForm(){
  let userForm = HtmlService.createTemplateFromFile('index').evaluate().setTitle("User Registration Form")
  SpreadsheetApp.getUi().showSidebar(userForm)
}

function showForm(){

let userForm = HtmlService.createTemplateFromFile('index').evaluate().setTitle("User Registration Form")

SpreadsheetApp.getUi().showSidebar(userForm)

}

As you can see we are using the setTitle() method to change the title of the html5 user form and if you open the google sheets you will see the title of the form is changed

And now if you run the app you also need to grant the permission to allow the app to modify,read and delete spreadsheet as shown below

Now we will be showing the modal dialog user html5 form using the below functions as shown below

function showForm(){
  let userForm = HtmlService.createTemplateFromFile('index').evaluate().setTitle("User Registration Form")
  SpreadsheetApp.getUi().showSidebar(userForm)
  SpreadsheetApp.getUi().showModalDialog(userForm,"User Registration Form")
  SpreadsheetApp.getUi().showModelessDialog(userForm,"User Registration Form")
}

function showForm(){

let userForm = HtmlService.createTemplateFromFile('index').evaluate().setTitle("User Registration Form")

SpreadsheetApp.getUi().showSidebar(userForm)

SpreadsheetApp.getUi().showModalDialog(userForm,"User Registration Form")

SpreadsheetApp.getUi().showModelessDialog(userForm,"User Registration Form")

}

And now if you open the google sheets you will see the below html5 user modal dialog form

Adding Menu in Google Sheets

Now we will be adding the custom menu inside the google sheets so that we will be having two sub-menu items as shown below

function onOpen(){
  let ui = SpreadsheetApp.getUi()
  ui.createMenu('User Menu')
  .addItem('sidebar form',"showForm")
  .addItem('modalform',"modalForm")
  .addToUi()
}

  let userForm = HtmlService.createTemplateFromFile('index').evaluate().setTitle("User Registration Form")

function showForm(){
  SpreadsheetApp.getUi().showSidebar(userForm)
}

function modalForm(){
  SpreadsheetApp.getUi().showModalDialog(userForm,"User Registration Form")
}

function onOpen(){

let ui = SpreadsheetApp.getUi()

ui.createMenu('User Menu')

.addItem('sidebar form',"showForm")

.addItem('modalform',"modalForm")

.addToUi()

}

let userForm = HtmlService.createTemplateFromFile('index').evaluate().setTitle("User Registration Form")

function showForm(){

SpreadsheetApp.getUi().showSidebar(userForm)

}

function modalForm(){

SpreadsheetApp.getUi().showModalDialog(userForm,"User Registration Form")

}

As you can see we have defined the new method called onOpen() and inside it we have defined the menu by using the createMenu() method and inside that we are adding the items inside the menu using the addItem() method. And then we have defining the methods to be executed whenever you click the items. Now if you open the google sheets you will see the menu added as shown below

And now we need to add the below javascript code guys inside the index.html file so that we can process the submission of the form as shown below

index.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <form>
      <input type="text" id="name" placeholder="Enter Name" required/>
      <input type="number" id="age" placeholder="Enter Age" required/>
      <input type="text" id="country" placeholder="Enter Country" required/>
      <input type="button" id="button" value="Register"/>
    </form>
  </body>
  <script>
    let nameBox = document.getElementById("name")
    let ageBox = document.getElementById('age')
    let countryBox = document.getElementById('country')

    document.getElementById('button').addEventListener('click',addRecord)

    function addRecord(){
      let data = {
        name:nameBox.value,
        age:ageBox.value,
        country:countryBox.value
      }

      google.script.run.processForm(data)
    }
    </script>
</html>

<!DOCTYPE html>

<html>

<head>

</head>

<body>

<form>

</form>

</body>

let nameBox = document.getElementById("name")

let ageBox = document.getElementById('age')

let countryBox = document.getElementById('country')

document.getElementById('button').addEventListener('click',addRecord)

function addRecord(){

let data = {

name:nameBox.value,

age:ageBox.value,

country:countryBox.value

}

google.script.run.processForm(data)

}

</script>

</html>

As you can see we have given the id to all the input fields and also the button also. So that we can target them dynamically using javascript. And in javascript we are using the getElementById() method passing the id in the argument and then we are adding the click event listener to the button. When we click the button we are taking the values that the user has submitted and then we are calling the external app script function using the run() method this function is called processForm() and now we need to define this function inside the code.gs file as shown below

code.gs

function processForm(data){
  var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1")
  ws.appendRow([data.name,data.age,data.country])
}

function processForm(data){

var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1")

ws.appendRow([data.name,data.age,data.country])

}

As you can see in the above code we are getting the data passed as an argument to the processForm() method and inside it we are first of all getting the reference of the current Active Spreadsheet using the sheet name. And then we are using the appendRow() method to insert the details of the user such as name,age and country.

Now if you run the code.gs file and go to google sheets an submit the html5 sidebar or modal form data will be inserted successfully as shown below