Creating Forms in Google Sheets – Sidebar & Modal Dialog forms

February 12, 2023May 21, 2020 by Admin

There are several ways to create 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 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,

The first method described above is elementary 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 them with others without giving access to your Google Sheets.

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

Table of Contents

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

Spreadsheet Service, and
HTML Service.

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

We can also use almost the same code used in the second post mentioned above for this method.

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

Before starting, 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

Google Sheets1

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 includes the following files.

Code.gs
Index.html
Form.html
JavaScript.html

The functions of these code snippets are briefly explained below.

1. Code.gs

The Code.gs 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 files 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.

2. Index.html

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

This file was created from Bootstrap Starter Template.

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 add new form elements, add the “name” of your new field in the processForm function in the Code.gs file as explained above.

4. JavaScript.html

This file includes the client-side JavaScript functions. This file is 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 Code.gs file explained above.

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 could create a data entry form within Google Sheets. I used Google Apps script and Bootstrap toolkit for building the form. You can load the form in the Sidebar, Modal Dialog, or Modeless Dialog in this method. You can add almost any type of form element 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.

Related