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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
<!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> |
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
1 2 3 4 |
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
1 2 3 4 |
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
1 2 3 4 5 6 |
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
<!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> |
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
1 2 3 4 |
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