Using the default data entry form in Google Sheets for data collection can be helpful. However, when dealing with complex and large-scale data entry tasks, it is best to develop an automated data entry form. An advanced automated data entry form not only improves the user-friendliness of the task but also enhances speed and accuracy, which is crucial.
While other popular applications like MS Excel and MS Access have their own native data entry forms, using Google Sheets data entry forms gives you an advantage. Note that Google Sheets allows you to access the most advanced spreadsheet application without the need to install it on your system.
What we love most about using Google Sheets to create a data entry form is that the application is available on the cloud, making it easily accessible from anywhere without any restrictions. Additionally, Google Sheets facilitates collaboration with friends and team members.
Although today’s tutorial on Google Sheets shows you how to create a data entry form, you will also learn how to use Google Sheets Apps Script to add advanced features to your forms.
If you have been trying to create a data entry form in Google Sheets but have been facing difficulties, this guide today will update you on everything you need to know.
Creating a Data Entry Form in Google Sheets: Step-by-Step Guide
Now that you know the features we want to include in our example data entry form in Google Sheets, it’s time to dive into the practical aspects of creating a form. Shall we get started?
Step 1: Create a new sheet and name it
The first thing to do is to create a new spreadsheet and name it “Employee Data Entry Form.”
Step 2: Add two new sheets to the current table
After creating a new sheet and naming it appropriately, we need to add two new sheets to our current table and name them accordingly. Here’s how to do it.
Go to the bottom extreme of your current table and click on the plus icon to add a new worksheet. Since we are adding two additional sheets, this means you will repeat the process twice.
Step 3: Rename the sheets
Once you have added the two new sheets, you also need to make sure all three sheets are renamed appropriately. As part of this guide, we will rename the three sheets respectively as “User Form,” “Database,” and “Support Sheet.”
For those who are new to using Google Sheets, here’s how to rename the sheets.
Start by selecting the sheet you want to rename and right-click. This will provide you with a list of options. Among the available options, select “Rename” and type the appropriate name in the field.
After renaming all the sheets, it should look like this:
Step 4: Modify the support sheet
Now that we have successfully renamed each sheet, we will show you how to modify each sheet with the appropriate data. We will start with the support sheet, which should contain a list of departments. Our list of departments will be in column A.
Step 4.1: Modify the database sheet
Just as we did for the support sheet, we also need to modify the database sheet. For this, we need to create the necessary column headers in the first row. We will start from column A to column H. Here are the data we need to include in these columns: Employee ID, Employee Name, Gender, Email ID, Department, Address, Submission Date, and Submitted By.
Step 4.2: Design the user form sheet
After modifying the other sheets, we need to do the same for the “User Form” sheet. However, here, we actually need to design the form from scratch, which is not easy. For those who have no objection, here’s how to proceed.
Step 4.2.1: Select the area you want for the form
Before starting to design our form and create a data entry form in Google Sheets, we need to select the area where we want to have the form. So head over to your spreadsheet and select the area where you intend to have the form.
Step 4.2.2: Choose the color
After highlighting the area where you want to design the form, we need to choose a color to differentiate this section from other sheets. As part of this guide, we will use green.
To select the color, go to the submenu and click on the color fill icon. Among the available options, select green.
If you have done everything exactly as we have shown above, you should have something like this:
Step 4.2.3: Merge cells
Once we are done selecting the color for our form, we need to merge a few cells. This short video shows you how to do it:
After merging the cells, we need to enter the header we want. Since we are creating an employee data entry form, we will insert it in the merged area. Additionally, we need to format this appropriately by selecting the font type and its size. Not only that, but we also need to justify the text in the center and align it vertically.
We know this may seem confusing, so we have prepared this short video guide for better clarification:
Step 4.2.4: Add a border to the merged section
After entering the header and formatting it appropriately, you also need to add a border to the merged section. To do this, select the merged section, go to the submenu, and select the icon to add borders. Among the options, select “All Borders.”
Step 4.2.5: Add outer borders
After adding borders to the merged section, you also need to add an outer border to other parts of the sheet that we are designing. And this is easy too. Simply select the other part of the sheet without borders. Once done, go to the submenu and click on the borders icon. Here, you want to choose the “Outer Border” option.
If you have done everything exactly as we have shown you, you should have a form that now looks like this:
Step 4.2.6: Add empty spaces for response
Now that we have added borders to the respective fields of our form, we need to create empty spaces for the response. Surprisingly, this is easy to do.
Simply select the cell where you want to have the response, then change the color from green to white.
For better clarifications, watch the video below:
Step 4.2.7: Create a drop-down list
As mentioned earlier, we need to create a drop-down list for departments and gender. Here’s how to proceed.
Select the field for gender. Go to the Data menu and select the Data Validation option. This will automatically launch a data validation editor on the extreme right of your spreadsheet.
Step 4.2.8: Set data validation rules for gender
After selecting the data validation option, you need to add a rule. So go to the extreme right of your spreadsheet. You will find the option to add a rule there.
When you click on the option to add a rule, you should see several options. Change Option 1 to “Male” and Option 2 to “Female.” You can also add a third option and name it “Other.”
The video below gives a better overview of how to approach this:
After completing the above steps, you should notice that the field for gender in your user form now includes a drop-down list for employees to select their gender. Take a look at what we have:
Step 5: Add relevant fields
Now that we have an outline of our form, we need to create the relevant fields that we want to have in our form. For this, we will use the information from our database sheet. So go to your user form sheet and enter the different fields that we have. For this guide, we will have a field for Employee ID, Employee Name, Gender, Email ID, Department, and Address.
Take a look at how our spreadsheet looks now after entering the relevant fields:
Step 6: Add borders to the fields
Even though our data entry form is already taking shape, we still need to format it to make it more attractive. To do this, we need to add borders to the respective fields. Here’s how to do it. Select the cells with the data, go to the submenu, click on the borders icon, and select the “All Borders” option.
The video below shows you exactly how to proceed:
After applying borders to the relevant fields of our form, you will notice how neat it looks. Take a look at ours:
Step 7: Create empty spaces for responses
Now that we have added borders to the respective fields of our form, we need to create empty spaces for the responses. Surprisingly, this is easy to do.
Simply select the cell where you want to have the response, then change the color from green to white.
For better clarifications, watch the video below:
Step 8: Create a drop-down list
As mentioned earlier, we need to create a drop-down list for departments and gender. Here’s how to proceed.
Select the field for gender. Go to the Data menu and select the Data Validation option. This will automatically launch a data validation editor on the extreme right of your spreadsheet.
Step 8.1: Set data validation rules for gender
After selecting the data validation option, you need to add a rule. So go to the extreme right of your spreadsheet. You will find the option to add a rule there.
After clicking on the option to add a rule, you should see several options. Change Option 1 to “Male” and Option 2 to “Female.” You can also add a third option and name it “Other.”
The video below gives a better overview of how to proceed:
After following the above steps, you should notice that the field for gender in your user form has been updated to include a drop-down list. Take a look at what we have:
Step 9: Add buttons to our form
We have come a long way in showing you how to create a data entry form in Google Sheets. But we are not done yet. Although adding the buttons may be a bit tricky, with our step-by-step guide, you should encounter no issues.
Let’s start with the search button, which we will place next to the employee ID search bar.
Before creating our search button, we first need to select where we want to place the button. Since we want it across from the empty cell, right next to the employee ID field, we will select the opposite cell. Once done, go to the Insert menu and choose the Drawing option.
This action will launch a new drawing window. Here, you want to draw a rectangular box and fill it with the color of your choice. Since we have been using green since the start of creating our data entry form in Google Sheets, we will continue with this color. After selecting the color you want, all you have to do is double-click inside the box and enter the text to reflect the button name. For this example, we will type “Search.”
Here’s what the button should look like once created:
Now that we have added the search button, we need to repeat the same process for the other buttons. The buttons we would like to add to our form include Save, Edit, Delete, and Clear.
Let’s quickly create the Save button.
Just as we did for the first button we created earlier, select the cell where you want to create the button. After that, go to the Insert menu and select the Drawing option. With the drawing window open, simply select a rectangular shape, select the color you are working with, and use the text to name the button “Save.” Once done, click Save and Close.
If you have followed exactly as we have highlighted above, you should notice that the Save button has now been added to your form.
Here’s what our data entry form in Google Sheets looks like after adding all the relevant buttons we want to have in our form:
If you look closely at the image above, you will notice that we have added gradients to our buttons. We did this because we wanted our data entry form to look neat. You can do the same with your buttons. Here’s how to add gradients to your buttons.
Start by selecting the button where you want to add the gradient. After clicking on the button, the drawing window should appear. When this happens, click on the button you created earlier and go to the color fill option. But instead of selecting green as you did earlier, click on the Gradient option. You should see several options for gradients. Choose the one you prefer.
Now, repeat the same process for the other buttons so that they all have the same appearance.
Writing the Apps Script
Now that we have managed to design a neat data entry form, create a drop-down list for fields such as gender and department, and add the necessary buttons to optimize our data entry form, we want to take it a step further by showing you how to write the Apps Script. If you haven’t done it in the past, this can be a bit daunting, but with our help at every step of the process, you should get through it faster than you imagine.
Let’s dive in, shall we?
Accessing Google Sheets Apps Script is easy; simply go to the Extensions menu and select the Apps Script option.
After clicking on Apps Script, Google Sheets will automatically launch the Apps Script editor in a new window. It should look like this:
A careful inspection of the above image shows that it is currently untitled, so we need to change that. To do this, simply click on “Untitled Project” and enter a name for your project. Since we are working on “Employee Data Entry Form,” we will rename our project.
Now that we have renamed our file, we can start adding our code. We will start by adding the code for our Search button. This function aims to make it easy to search for a record based on the user’s input in cell C4 of our user form sheet.
Here’s the code we will use to achieve that:
// Function to search for a record
function searchRecord() {
var myGoogleSheet = SpreadsheetApp.getActiveSpreadsheet(); // declare a variable and set it to the active Google spreadsheet
var shUserForm = myGoogleSheet.getSheetByName("User Form"); // declare a variable and set it to the User Form worksheet
var datasheet = myGoogleSheet.getSheetByName("Database"); // declare a variable and set it to the Database worksheet
var str = shUserForm.getRange("C4").getValue();
var values = datasheet.getDataRange().getValues(); // Get all values from the used range and assign them to the values variable
var valuesFound = false; // variable to store a boolean value to validate if any values were found or not
for (var i = 0; i < values.length; i++) {
var rowValue = values[i]; // declare a variable and store the value
// check if the first value of the record is equal to the search item
if (rowValue[0] == str) {
shUserForm.getRange("C7").setValue(rowValue[0]);
shUserForm.getRange("C9").setValue(rowValue[1]);
shUserForm.getRange("C11").setValue(rowValue[2]);
shUserForm.getRange("C13").setValue(rowValue[3]);
shUserForm.getRange("C15").setValue(rowValue[4]);
shUserForm.getRange("C17").setValue(rowValue[5]);
return; // exit the search function
}
}
if (valuesFound == false) {
var ui = SpreadsheetApp.getUi(); // to create an instance of the user interface environment to use the dialog features
ui.alert("No record found!");
}
}Simply copy and paste the code above into the code window. Once you have done that, click Save.
Now let’s add the function to our “Save” button. To do this, open the user form sheet, click on the “Save” button, and you should see three dots. Click on them and select the “Assign Script” option. After selecting this option, a small box should appear where you need to enter a name for the Apps Script. Simply type “submitData” and click OK.
Now let’s see if the “Save” button works by testing it randomly. Start by visiting the form and enter the following details:
- Employee ID: BUS1094
- Employee Name: Kimberly Webber
- Gender: Female
- Email ID: [email protected]
- Department: Logistics
- Address: HK Groove, Lane
After entering the employee details above, simply click on the “Save” button.
If all goes well, you should see this prompt. To see if the details have been saved, go to the database sheet and check if the information is stored there. Here’s what our database sheet looks like:
If you look at the above image, you will see that our “Save” button works just fine. Now repeat the process for the other buttons exactly as we have highlighted above and test them to see if they work.
Add all the other functions
In addition to the “submitData” function for the “Save” button, you will need to add the other functions for the other buttons. You can simply copy and paste the other function scripts for the “Edit,” “Delete,” and “Clear” buttons from the original content. Don’t forget to save them and associate them with the corresponding buttons.
Validate the inputs
Even though we have created a function for all the buttons in our form, if we don’t add a function to validate the data inputs, we will get an error message when attempting to perform a function using the buttons in our data entry form in Google Sheets. To this end, we need to validate our data entry form to prevent our form from returning error messages.
To do this, simply go to the Apps Script editor you used for this project, then copy and paste the code below into the code window.
// Declare a function to validate user input
function validateInput() {
// Add your validation logic here
}Congratulations! You have successfully created a data entry form in Google Sheets, added buttons, and written the Apps Script to make it functional. With this guide, you can now easily create data entry forms in Google Sheets for your various projects.
Remember, practice makes perfect! Keep experimenting and exploring different features in Google Sheets to enhance your skills and optimize your workflows.
For more tips and tutorials on Google Sheets and other productivity hacks, visit Crawlan.com.
Now it’s your turn to give it a try! Start creating your own data entry form in Google Sheets and enjoy the benefits of automation and accuracy. Happy form building!
