Use Google Scripts To Create a Sidebar Data Entry Form in Google Sheets
Entering data directly into a spreadsheet might be the easiest way to input data, but it might not be the best way. If you want to ensure that the data conforms to specific conditions and formats or if you want to perform certain data validations before entering the data, a data entry form is a much better solution.
Luckily Google has a few tools you can use for data entry. The most popular and easy to use is their Google Forms product. I recently wrote an article about how to use Google Forms as a Timesheet Tracking App.
This is exactly what we need to build a data entry form. In this guide, I'll show you how to build a simple contact form in Google Sheets like the below.
Create Sidebar Using Google Apps Script
To create a Sidebar in Google Sheets we need to use Google Apps Script. To access this, click on Tools -> Script editor.
The Script Editor will open with some default function in the Code.gs file. Clear this function and copy and paste the below code.
What does this code do?
The @OnlyCurrentDoc defines the script's authorization scope to this spreadsheet only instead of all the files in your Google account.
The onOpen() function creates a menu item in the toolbar called 'Sidebar' and adds an item called 'Contact Form' which when selected runs the function 'showSidebar'. As expected, the onOpen() function runs automatically when a Sheet is opened.
Next, we have the function showSidebar() which is called when a user clicks the Contact Form menu item above.
The showSidebar() function calls the HTML service which lets you serve web pages that can interact with server-side Apps Script functions.
Here we're creating an HTML Template object from the HTML file 'Sidebar.html'. The evaluate() function converts the template to an HTMLOutput object that can be served to the user, in this case as a sidebar in Google Sheets. And then we are naming the sidebar title 'Contact Form'.
We'll use the class SpreadsheetApp along with getUI() to return an instance of the spreadsheet's user-interface environment. This allows us to interact with the UI. Now we can display a sidebar with the method showSidebar() by passing the html object as a parameter.
Sidebar HTML File
In the above showSidebar() function, we referenced a file called 'Sidebar.html'. We need to create that file by clicking File -> New -> Html file and naming it 'Sidebar'.
Once it's created, we need to build the HTML page that will display the contact form. The form will have input fields for first name, last name, address, city, a drop-down for state, zip code, phone number, and email.