Creating Forms in Google Sheets – Sidebar & Modal Dialog forms

There are a number of ways for creating forms in Google Sheets.

The most popular and easiest method is to use Google Forms. However, it is a general-purpose form building application. Sometimes you may find it very difficult to create a form that is in your mind with Google Forms. Especially when it comes to mass data entry works, you cannot use it.

So, what are the other methods for creating forms in Google Sheets? You can build various types of forms using Google Apps Scripts. In some of my previous posts, I explained,

  1. How to create a Data Entry form in Google Sheets
  2. How to Create Data Entry Form with Google HTML Service and Submit Data to Google Sheets
  3. How to Create an Online Data Entry Form that can Perform CRUD Operations on Google Sheets

The first method described above is very basic and uses a small piece of code. But it is a way to build a form within the Spreadsheet.

The other two are completely built with Google Apps Scripts. You can publish them as Web Apps and share it with others without giving access to your Google Sheet.

Let’s see how to create HTML forms inside Google Sheet Sidebar and in Modal Dialog.

Creating forms in Google Sheets and show in the Sidebar or Modal Dialog

For creating this form, we are going to use Google Apps Scripts;

And also, we are going to use Bootstrap HTML, CSS, and JS library to add styles to the form.

For this method also, we can use almost the same code used in the second post mentioned above.

You need to create bound script to display Modal and Sidebar using this method.

Before start, watch the below animation to see how the final form is working.

Make a copy of the Google Sheet

Go to Tools > Script editor to access the code
1 l8Um43Fq5cVcyAIMGXDpkQ 2

The Google Sheet in the above link contains all the scripts. So, you can start customizing it to match your requirements. However, I will explain the code below for easy understanding.

Code Snippets

The Apps Script include the following files.

  2. Index.html
  3. Form.html
  4. JavaScript.html

The codes inside the functions are briefly explained below.


The file includes the following functions,

onOpen: Create a custom menu (My Menu) and sub-menus (Sidebar Form, Modal Dialog Form, Modeless Dialog Form) when you open the Google Sheets.

showFormInSidebar: When you click the sub-menu item “Sidebar Form” this function display the form on the right side of your Google Sheet.

showFormInModalDialog: When you click the sub-menu item “Modal Dialog Form”, this function displays the form inside the Modal Dialog. With Modal Dialog, you can see the Google Sheet in the background. But, you cannot edit the sheet until you close the modal dialog.

showFormInModlessDialog: When you click the sub-menu item “Modeless Dialog Form”, this function displays the form inside the Modeless Dialog. With Modeless Dialog also, you can see the Google Sheets in the background. However, you can edit the sheet without closing the Modeless Dialog.

processForm: This function extract the values in the form object and appends the data to Google Sheet. If you are adding more form items, you need to include the “name” of that item inside this function.

include: As you can see in the above 4 Apps Script files, I have separated, Form and JavaScript file from the Index file. I have done this to reduce the complexity of the code (See HTML Service: Best Practices). These separated codes are included in the Index file using this include function.

function onOpen() { 
  var ui = SpreadsheetApp.getUi();
  ui.createMenu("My Menu")
    .addItem("Sidebar Form","showFormInSidebar")
    .addItem("Modal Dialog Form","showFormInModalDialog")
    .addItem("Modeless Dialog Form","showFormInModlessDialog")

function showFormInSidebar() {      
  var form = HtmlService.createTemplateFromFile('Index').evaluate().setTitle('Contact Details');

function showFormInModalDialog() {
  var form = HtmlService.createTemplateFromFile('Index').evaluate();
  SpreadsheetApp.getUi().showModalDialog(form, "Contact Details");

function showFormInModlessDialog() {
  var form = HtmlService.createTemplateFromFile('Index').evaluate();
  SpreadsheetApp.getUi().showModelessDialog(form, "Contact Details");

function processForm(formObject){ 
  var sheet = SpreadsheetApp.getActiveSheet();
                //Add your new field names here

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

2. Index.html

The HTML form and the client-side scripts are included in this file. When you click the menu items, this file is evaluated and displayed on the Sidebar or Modal Dialog. The JavaScript and Form are included inside this file as explained above.

This file was created from Bootstrap Starter Template.

<!doctype html>
<html lang="en">
    <!-- Required meta tags -->
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
    <!-- Bootstrap CSS -->
    <link rel="stylesheet" href="" integrity="sha384-9aIt2nRpC12Uk9gS9baDl411NQApFmC26EwAOH8WgZl5MYYxFfc+NcPb1dKGj7Sk" crossorigin="anonymous">
    <?!= include('JavaScript'); ?> <!-- See JavaScript.html file -->
    <title>Contact Details</title>
  <body class="bg-secondary text-light">
    <div class="container">
      <?!= include('Form'); ?> <!-- See Form.html file -->
    <!-- Optional JavaScript -->
    <!-- jQuery first, then Popper.js, then Bootstrap JS -->
    <script src="" integrity="sha384-DfXdz2htPH0lsSSs5nCTpuj/zy4C+OGpamoFVy38MVBnE+IbbVYUew+OrCXaRkfj" crossorigin="anonymous"></script>
    <script src="[email protected]/dist/umd/popper.min.js" integrity="sha384-Q6E9RHvbIyZFJoft+2mJbHaEWldlvI9IOYy5n3zV9zzTtmI3UksdQRVvoxMfooAo" crossorigin="anonymous"></script>
    <script src="" integrity="sha384-OgVRvuATP1z7JjHLkuOU7Xw704+h835Lr+6QL9UvYjZE3Ipu6Tp75j7Bh/kR0JKI" crossorigin="anonymous"></script>

3. Form.html

This includes all the form components. You can add or remove the form items in this file. You can grab the code snippets for required form items from Bootstrap Documentation.

If you are adding new form elements, add the “name” of your new field in the processForm function in the file as explained above.

<form id="myForm" onsubmit="handleFormSubmit(this)">
  <div class="form-group">
    <label for="first_name">First Name</label>
    <input class="form-control form-control-sm" type="text" class="form-control" id="first_name" name="first_name" placeholder="First Name">
  <div class="form-group">
    <label for="last_name">Last Name</label>
    <input class="form-control form-control-sm" type="text" class="form-control" id="last_name" name="last_name" placeholder="Last Name">
  <div class="form-group">
    <label for="gender">Gender</label>
    <select class="form-control form-control-sm" id="gender" name="gender" required>
      <option value="" selected>Choose...</option>
      <option value="Male">Male</option>
      <option value="Female">Femle</option>
  <div class="form-group">
    <label for="email">Email</label>
    <input class="form-control form-control-sm" type="email" class="form-control" id="email" name="email">
  <button type="submit" class="btn btn-primary">Submit</button>

4. JavaScript.html

This file includes the client-side JavaScript functions. This file also included in the Index.html as explained above.

The preventFormSubmit function prevents the default behavior of the form. So if you click the submit button, you won’t leave the page.

When you click the Submit button, it will call the handleFormSubmit and pass the formObject as a parameter. Then this formObject passes to the processForm function in the file explained above.

  // 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) {
  window.addEventListener('load', preventFormSubmit);    
  function handleFormSubmit(formObject) {;

How to Edit & Run this Code

First, make a copy of the Google Sheet with the above code from this Link.

When you open the Google Sheets, you will see the My Menu next to the Help Menu. Click the My Menu and Select the form type you want to open. When you run the code (open a form) for the first time, it will ask your permission to run the code in your Google Account. Authorize it and click the menu again.

To edit the code,

Click Tools > Script Editor

Form Validation

In this example, I have used browser defaults for data validation. However, depending on the requirements you may need more customization. You can learn more about Bootstrap form validation from this link.

Wrapping Up

This post explained how you can create a data entry form withing the Google Sheets. I used Google Apps script and Bootstrap toolkit for building the form. In this method, you can load the form in the Sidebar, Modal Dialog, or Modeless Dialog. You can add almost any type of form elements to this form. You can learn them from the Bootstrap links provided above. You can also extend this form to make all the CRUD operations and can get some ideas from the post mentioned above on the CRUD application.


Oh hi there 👋
It’s nice to meet you.

Sign up to receive awesome content in your inbox, every month.

We don’t spam! Read our privacy policy for more info.

The Ultimate Managed Hosting Platform
5 3 votes
Article Rating
Notify of

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

Inline Feedbacks
View all comments
Dennis G D

Great write up! Thank you!

Kevin Tien

Very good article! Thanks!

Huber Adver

Hello and thank you for a great job! But i have some question –
i change this form to my needs and got a problem with part – everything what i add, is not adding to the table. Can you help me what to do, is my first project lol just working needs

Ryan P

Very informative write-up, but I can’t seem to get the code to run. I’ve both copied into scripts as well as the linked copy, and can’t seem to troubleshoot the issue. Is there anything I needed to alter once copied?


Hi Ryan,
I checked the Google Sheet again and it is working fine.
You don’t need to do any alteration to see the forms I have created.

  1. Just make a copy of the Google Sheet to your Drive using the link provided in the post.
  2. Wait a few seconds and you will see a custom menu named “My Menu” next to Help.
  3. Click this My Menu and chose a form.
  4. For the first time, it will ask permission to allow the code to run in your Google Allow. (You will need to perform few clicks in the pop-up window to allow the action)
  5. After you grant permission, click the My Menu, and select a form type again.
  6. Then you should see the form.
Rodrigo Silveira

Lovely article. I wonder whether anyone can help me with thoughts about how to initialize one of the input fields in this example, using spreadsheet data.


Hello. Thank you for sharing this script; it was very useful to me!
I state that I am a newbie in programming on apps script and if you could give me some advice I am grateful.

I bound your script to a google sheet and modified the form and everything works. But I have problems when I share the sheet with another user. Even if the other user authorizes the execution of the script, when the user clicks on the “Submit” button the data is not recorded on the google sheet but the data entered in the form is deleted only.
How could I fix this problem?

Thanks for the reply. 🙂



Is it possible to auto-populate certain fields in the form depending on other entries?


Beautiful piece of program. Just wondering if there are any other ways to replace the html service as google blocked the app due too security threat. Cheers


Thousand THANKS for the tutorial! I found few before, but no luck in success running. This article helps me to solve the problem. Thank you again!

Bob C

Thank you for this clear and helpful tutorial. I was able to customize a data entry form for my Google Sheets spreadsheet that works perfectly!