How to Create a Data Entry Form in Google Sheets (Step-by-step Guide)

Although Google Sheets has a default data entry form you can use for data collection, when you’re dealing with complex and large-scale data entry jobs, it’s best to develop an automated data entry form. Using an advanced automated data entry form not only enhances the task’s user-friendliness, but also improves speed and accuracy, which is super important. 

While other popular applications like MS Excel and MS Access have their own indigenous data entry forms, using Google Sheets data entry forms gives you an edge. Just so you know, Google Sheets allows you to access the most advanced spreadsheet application without needing 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 easy to access from anywhere without any type of restrictions. More so, Google Sheets makes it seamless to collaborate with friends and team members. 

While today’s Google Sheets tutorial will show you how to create a data entry form, you’ll also learn how to use Google Sheets Apps Script to add advanced features to your forms. 

If you have been looking to create a data entry form in Google Sheets but have been finding it challenging, today’s guide will bring you up to speed on everything you need to know. 

PS: The sample data entry form we will create in this Google Sheets guide will have the following features: 

• Designing the UI
• Validating the inputs
• Transferring the data
• Seamless search function
• Editing record
• Deleting record
• Resetting user form

---

Creating data entry form in Google Sheets: Step-by-step guide

Now that you know the features we want our sample data entry form in Google Sheets to have, it’s time to jump into the practical aspects of creating one. Let’s jump right in, shall we?

Step 1: Create a new sheet and name it

The first thing we want to do is create a new spreadsheet and name it “Employee Data Entry Form”

Step 2: Add two new sheets to the current spreadsheet 

After creating a new sheet and naming it appropriately, we need to add two new sheets to our current spreadsheet and rename them accordingly. Here is how to do that. 

Navigate to the extreme bottom of your current spreadsheet and click the plus icon to add a new spreadsheet. Since we are adding two extra sheets, it means you’ll repeat the process twice. 

Step 3: Rename Sheets

Once you have added the two new sheets, you must also ensure all three sheets are renamed appropriately. For the purpose of this guide, we will rename the three sheets as “User form,” “Database,” and “Support Sheet,” respectively. 

For those new to using Google Sheets, here is how to rename the sheets.

Start by selecting the sheet you want to rename and right-click. Doing this will provide you with a list of options. From the options available, select Rename and type in the appropriate name in the field. 

After renaming all sheets, here is what it should look like: 

Note: We will use the “User form” sheet to create our data entry form. As per the “Database” sheet, we will use that to store the data transferred from the data entry form. Finally, the “Support Sheet” will hold the names of the departments we want to include in our Employee Data Entry Form. 

Step 4: Modify the Support Sheet

Now that we have successfully renamed each sheet; let’s show you how to modify each sheet with the appropriate data. We will start with the support sheet, which should have a list of departments. Our list of Departments will be in Column A. 

Step 4.1: Modify Database Sheet

As we did for the Support Sheet, we also need to modify the Database Sheet. For this one, we need to create the necessary column headers in the first row. We will start from Column A all the way to Column H. Here are the data we need to include in those columns: Emp ID, Emp Name, Gender, Email ID, Department, Address, Submitted On, and Submitted by. 

Step 4.2: Design the User Form Sheet

After modifying the other sheets, we must do the same for the “User form” sheet. However, here, we actually need to design the form from scratch, which is no easy feat. For those who don’t mind, here is how to go about it. 

Step 4.2.1: Select the area you want for the form

Before we get into properly designing our form and creating a data entry form in Google Sheets, we need to select the area where we want to have the form. So head to your spreadsheet and select the area you intend to have the form. 

Step 4.2.2: Select color 

After highlighting the area where you intend to design the form, we need to choose a color to differentiate that section from the other sheet. For the purpose of this guide, we will use green. 

To select the color, head to the sub-menu and click the color fill icon. From the option available, select green. 

If you did everything exactly as we highlighted 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 together. This short video shows you how to do that: 

After merging the cells, we need to type in the header we want. Since we are creating an Employee Data Entry Form, we will input that in the merged section. Also, we need to format that appropriately by selecting font type and size. Not just that, we also need to justify text to the middle and vertically align it. 

We know this sounds confusing, so we have put together this short video guide for better clarification. 

Step 4.2.4: Add a border to the merged section

Done inputting the heading and formatting it appropriately? Great. Now, you need to add a border to the merged section. To do that, select the merged section, head to the sub-menu and select the icon for add Borders. From 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 the other parts of the sheet we are designing. And that, too, is easy. Simply select the other part of the sheet without borders. With that done, head to the sub-menu and click on the icon for Border. Here, you want to choose the option for “Outer border.”

If you did everything exactly as we showed you, you should have a form that now looks like this. 

Before we add the relevant fields to our Employee Data Form, we want our form to look clean. And to do that, we need to change the color for A3:E4. So instead of leaving it green as we had it earlier, we can choose a lighter shade of green. 

Here is how to change the color. Select cells A2:E3, head to the submenu, and click the color fill option. From the options, select a lighter shade of green. 

After doing that, you should notice some obvious changes to your spreadsheet. Here is what ours looks like: 

Step 5: Create the relevant fields 

Now that we have an outline for our form, we need to create the relevant fields we want to have in our form. For that, we will use the information in our Database sheet. So head to your User Form spreadsheet and input the various fields we have. For this guide, we will have a field for Emp ID, Employee ID, Employee Name, Gender, Email ID, Department and Address. 

Here, take a look at what our spreadsheet now looks like after entering the relevant fields. 

Step 6: Create border for fields

Even though our Employee Data Form is already taking shape, we still need to format it so it looks better. To do that, we need to add borders to the respective fields. Here is how it’s done.  Select the cells with data, head to the sub menu, click on the icon for Borders and select the option for “All borders.”

The video below shows you exactly how it’s done. 

After applying borders to the relevant fields we have in our form, you’ll notice how clean it looks. Take a look at ours. 

Step 7: Create blank spaces for response

With the borders now added to the respective fields in our form, we need to create blank spaces for the response. Surprisingly, this is easy. 

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 5: Create a drop-down list

As we mentioned earlier, we need to create a drop-down list for Departments and Gender. Here is how to achieve that. 

Select the field for Gender. Head to the Data menu and select the option for Data Validation. This action will automatically launch a Data validation editor to the extreme right of your spreadsheet. 

Step 5.1: Set data validation rules for Gender

After selecting the option for data validation. You need to add a rule. So navigate to the extreme right of your spreadsheet. There you’ll find the option for Add rule. 

When you click on the Add rule option, 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 Others. 

The video below provides better insight on how to approach this: 

After executing these steps above, you should notice that the field for Gender in your User form now has a drop-down that allows employees to select their gender. 

Here is what our form looks like after applying data validation for Gender. 

Step 5.2.2: Set data validation rules for Department 

Now that we have applied data validation rules to the Gender field, we need to also do the same for the Department field. So, start by selecting the field where you have Department. Head to the Data menu and select the Data validation option. 

Once you select the Data validation option, you should see that Data validation editor on the extreme right of your spreadsheet. 

Go there and select the option for Add rule. 

After clicking on the option for Add rule, you’ll be presented with a list of options. Click on the drop-down button for Criteria and choose the option for Dropdown (from a range). 

With that done, you need to select the data range, which is tricky. To make your job easy, we have put together a short video demo to guide you. Here, check it out for yourself. 

If you executed the steps exactly as we showed you in the above video, you should notice that the field for Department has been updated to include a drop-down list. Take a look at what ours looks like: 

Step 6: Add buttons to our form

We have come a long way, showing you how to create a data entry form in Google Sheets. But we aren’t done yet. While creating the drop-down list for Gender and Department has taken us at least halfway into this guide on creating a data entry form in Google Sheets, there is still one crucial section that is missing: adding the relevant buttons to our form. 

Even though adding the buttons can be a little tricky, with us guiding you every step of the way, you shouldn’t have any issues. 

Let’s start with the Search button, which we will place close to the Emp ID search bar. 

Before creating our Search button, we first need to select where we want the button to be placed. Since we want it opposite the blank cell, just beside the Emp ID field, we will go ahead and select the cell opposite that. 

With that done, head over to the Insert menu and select the option for Drawing. 

This action will launch a new drawing window. Here you want to draw a rectangular box and fill it with your desired color. Since we have been using green since we started creating our data entry form in Google Sheets, we will stick to that. After selecting the color you want, all you need is double click within the box and input text to reflect the button’s name. For this example, we will type “Search.”

Here is what the button should look like after creating it: 

Having added the search button, we now need to repeat the same process for other buttons. The buttons we would like to add to our form include Save, Modify, Delete, and Clear. 

Let’s quickly create the Save button. 

As we did for the first button we created earlier, select the cell where you’d like to create the button. After that, navigate to the Insert menu and select the option for Drawing. With the Drawing window launched, simply select a rectangular shape, select the color you have been working with and give the caption as Save. Once you’re done, click on Save and Close. 

If you did exactly as highlighted above, you should notice that the Save button has now been added to your form. Here is what we have so far: 

If we keep repeating the process of creating each button we want to add to our data entry form, it will sound boring and repetitive. And because we don’t want to bore you with too many details, you can simply follow the process we have highlighted so far to add the remaining button to your spreadsheet. 

Here, take a look at our data entry form in Google Sheets after adding all the relevant buttons we want in our data entry form: 

If you look at the image above closely, you’ll notice that we have added gradients to our buttons. We did so because we wanted our data entry form to look clean. You can also do the same with your buttons. Here is how to add gradients to your buttons. 

Start by selecting the button you want to add the gradient to. After clicking the button, the drawing window should launch. 

When that happens, click the button you created earlier and head to the color fill option. But instead of selecting green as you did earlier, click on the option for Gradient. You should see several options for gradients. Choose the one you like. 

Now, repeat the same process for the other buttons so they all look uniform. 

Writing the Apps Script 

Now that we have successfully designed a neat data entry form, created a drop-down for fields like Gender and Department and added the necessary buttons we need to optimize our data entry form, we want to take things up a notch by showing you how to actually write the Apps Script. If you haven’t done this in the past, it might be a little challenging, but with us guiding you every step of the way, you should get the hang of it sooner than you imagine. 

Let’s dive right in, shall we? 

Accessing Google Sheets Apps Script is easy, simply navigate to the Extension menu and select the option for Apps Script. 

After clicking on Apps Script, Google Sheets will automatically launch the Apps Script editor in a new Window. It should look like this: 

A close look at the image above shows that it is currently untitled, so we need to change that. To do that, simply click “Untitled Project” and enter a name for your project. Since we are working on “Employee Data Entry Form,” we will rename our project to that.

Add function for the ‘Search’ button 

Now that we have renamed our file, we can start adding our code. We will start with adding the code for our Search button. This function aims to make it seamless to search a record based on user input in Cell C4 of our User Form sheet. 

Here is the code we will use to achieve that: 

//Function to Search the record

function searchRecord() {

  var myGooglSheet= SpreadsheetApp.getActiveSpreadsheet(); //declare a variable and set with active google sheet 

  var shUserForm= myGooglSheet.getSheetByName("User Form"); //delcare a variable and set with the User Form worksheet

  var datasheet = myGooglSheet.getSheetByName("Database"); ////delcare a variable and set with the Database worksheet

  var str       = shUserForm.getRange("C4").getValue();

  var values    = datasheet.getDataRange().getValues(); //getting the entire values from the used range and assigning it to values variable

  var valuesFound=false; //variable to store boolean value

  for (var i = 0; i < values.length; i++) 

    {

    var rowValue = values[i]; //declaraing a variable and storing the value

    //checking the first value of the record is equal to 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; //come out from the search function

      }

  }

if(valuesFound==false){

  //to create the instance of the user-interface environment to use the messagebox features

  var ui = SpreadsheetApp.getUi();

  ui.alert("No record found!");

 }

}

Simply copy the above code and paste it into the code window. Once you do that, click on Save. 

Adding function to the ‘Save’ button

By creating a function for our save button, what we are basically doing is adding an instruction to transfer data from User Form to Database. To add this function to our Apps Script, we will use the following code: 

// Function to submit the data to Database sheet

function submitData() {

  var myGooglSheet= SpreadsheetApp.getActiveSpreadsheet(); //declare a variable and set with active google sheet 

  var shUserForm= myGooglSheet.getSheetByName("User Form"); //delcare a variable and set with the User Form worksheet

  var datasheet = myGooglSheet.getSheetByName("Database"); ////delcare a variable and set with the Database worksheet

  //to create the instance of the user-interface environment to use the messagebox features

  var ui = SpreadsheetApp.getUi();

  // Display a dialog box with a title, message, and "Yes" and "No" buttons. The user can also

  // close the dialog by clicking the close button in its title bar.

  var response = ui.alert("Submit", 'Do you want to submit the data?',ui.ButtonSet.YES_NO);

  // Checking the user response and proceed with clearing the form if user selects Yes

  if (response == ui.Button.NO) 

  {return;//exit from this function

  } 

  //Validating the entry. If validation is true then proceed with transferring the data to Database sheet

 if (validateEntry()==true) {

    var blankRow=datasheet.getLastRow()+1; //identify the next blank row

    datasheet.getRange(blankRow, 1).setValue(shUserForm.getRange("C7").getValue()); //Employee ID

    datasheet.getRange(blankRow, 2).setValue(shUserForm.getRange("C9").getValue()); //Employee Name

    datasheet.getRange(blankRow, 3).setValue(shUserForm.getRange("C11").getValue()); //Gender

    datasheet.getRange(blankRow, 4).setValue(shUserForm.getRange("C13").getValue()); // Email ID

    datasheet.getRange(blankRow, 5).setValue(shUserForm.getRange("C15").getValue()); //Department

    datasheet.getRange(blankRow, 6).setValue(shUserForm.getRange("C17").getValue());// Address

    // date function to update the current date and time as submittted on

    datasheet.getRange(blankRow, 7).setValue(new Date()).setNumberFormat('yyyy-mm-dd h:mm'); //Submitted On

    //get the email address of the person running the script and update as Submitted By

    datasheet.getRange(blankRow, 8).setValue(Session.getActiveUser().getEmail()); //Submitted By

    ui.alert(' "New Data Saved - Emp #' + shUserForm.getRange("C7").getValue() +' "');

  //Clearnign the data from the Data Entry Form

    shUserForm.getRange("C7").clear();

    shUserForm.getRange("C9").clear();

    shUserForm.getRange("C11").clear();

    shUserForm.getRange("C13").clear();

    shUserForm.getRange("C15").clear();

    shUserForm.getRange("C17").clear();

 }

}

To start with, head over to the Apps Script editor. Click on the code window where you earlier pasted the Search script function. Navigate all the way down to the last line of the initial code and paste the above code. After pasting the code, click on save. 

With that done, Google should create a function for that and save it as “submitData.” 

Add function for ‘Delete’ button

As we did for the other buttons, we want to also add a function for our Delete button. To do that, we will paste the following code to our Apps Script code window and click save, just like we did for the other buttons. 

//Function to delete the record

function deleteRow() {

  var myGooglSheet= SpreadsheetApp.getActiveSpreadsheet(); //declare a variable and set with active google sheet 

  var shUserForm= myGooglSheet.getSheetByName("User Form"); //delcare a variable and set with the User Form worksheet

  var datasheet = myGooglSheet.getSheetByName("Database"); ////delcare a variable and set with the Database worksheet

  //to create the instance of the user-interface environment to use the messagebox features

  var ui = SpreadsheetApp.getUi();

  // Display a dialog box with a title, message, and "Yes" and "No" buttons. The user can also

  // close the dialog by clicking the close button in its title bar.

  var response = ui.alert("Submit", 'Do you want to delete the record?',ui.ButtonSet.YES_NO);

 // Checking the user response and proceed with clearing the form if user selects Yes

 if (response == ui.Button.NO) 

 {return;//exit from this function

 } 

  var str       = shUserForm.getRange("C4").getValue();

  var values    = datasheet.getDataRange().getValues(); //getting the entire values from the used range and assigning it to values variable

  var valuesFound=false; //variable to store boolean value to validate whether values found or not

  for (var i = 0; i < values.length; i++) 

    {

    var rowValue = values[i]; //declaraing a variable and storing the value

    //checking the first value of the record is equal to search item

    if (rowValue[0] == str) {

      var  iRow = i+1; //identify the row number

      datasheet.deleteRow(iRow) ; //deleting the row

      //message to confirm the action

      ui.alert(' "Record deleted for Emp #' + shUserForm.getRange("C4").getValue() +' "');

      //Clearing the user form

      shUserForm.getRange("C4").clear() ;     

      shUserForm.getRange("C7").clear() ;

      shUserForm.getRange("C9").clear() ;

      shUserForm.getRange("C11").clear() ;

      shUserForm.getRange("C13").clear() ;

      shUserForm.getRange("C15").clear() ;

      shUserForm.getRange("C17").clear() ;

      valuesFound=true;

      return; //come out from the search function

      }

  }

if(valuesFound==false){

  //to create the instance of the user-interface environment to use the messagebox features

  var ui = SpreadsheetApp.getUi();

  ui.alert("No record found!");

 }

}

Add function for ‘Modify’ button

Adding a function for our Modify button is super easy. All you need to do is paste the below code to the Apps Script code window we have been working with and hit save. 

//Function to edit the record

function editRecord() {

  var myGooglSheet= SpreadsheetApp.getActiveSpreadsheet(); //declare a variable and set with active google sheet 

  var shUserForm= myGooglSheet.getSheetByName("User Form"); //delcare a variable and set with the User Form worksheet

  var datasheet = myGooglSheet.getSheetByName("Database"); ////delcare a variable and set with the Database worksheet

  //to create the instance of the user-interface environment to use the messagebox features

  var ui = SpreadsheetApp.getUi();

  // Display a dialog box with a title, message, and "Yes" and "No" buttons. The user can also

  // close the dialog by clicking the close button in its title bar.

  var response = ui.alert("Submit", 'Do you want to edit the data?',ui.ButtonSet.YES_NO);

 // Checking the user response and proceed with clearing the form if user selects Yes

 if (response == ui.Button.NO) 

 {return;//exit from this function

 } 

  var str       = shUserForm.getRange("C4").getValue();

  var values    = datasheet.getDataRange().getValues(); //getting the entire values from the used range and assigning it to values variable

  var valuesFound=false; //variable to store boolean value to validate whether values found or not

  for (var i = 0; i < values.length; i++) 

    {

    var rowValue = values[i]; //declaraing a variable and storing the value

    //checking the first value of the record is equal to search item

    if (rowValue[0] == str) {

      var  iRow = i+1; //identify the row number

      datasheet.getRange(iRow, 1).setValue(shUserForm.getRange("C7").getValue()); //Employee ID

      datasheet.getRange(iRow, 2).setValue(shUserForm.getRange("C9").getValue()); //Employee Name

      datasheet.getRange(iRow, 3).setValue(shUserForm.getRange("C11").getValue()); //Gender

      datasheet.getRange(iRow, 4).setValue(shUserForm.getRange("C13").getValue()); // Email ID

      datasheet.getRange(iRow, 5).setValue(shUserForm.getRange("C15").getValue()); //Department

      datasheet.getRange(iRow, 6).setValue(shUserForm.getRange("C17").getValue());// Address

      // date function to update the current date and time as submittted on

      datasheet.getRange(iRow, 7).setValue(new Date()).setNumberFormat('yyyy-mm-dd h:mm'); //Submitted On

      //get the email address of the person running the script and update as Submitted By

      datasheet.getRange(iRow, 8).setValue(Session.getActiveUser().getEmail()); //Submitted By

      ui.alert(' "Data updated for - Emp #' + shUserForm.getRange("C7").getValue() +' "');

    //Clearnign the data from the Data Entry Form

      shUserForm.getRange("C4").clear();

      shUserForm.getRange("C7").clear();

      shUserForm.getRange("C9").clear();

      shUserForm.getRange("C11").clear();

      shUserForm.getRange("C13").clear();

      shUserForm.getRange("C15").clear();

      shUserForm.getRange("C17").clear();

Add function for ‘Clear’ button

If you look at the data entry form we are creating, you’ll see that it has a dedicated button for Clear. We need to add a function for that, which is super easy. Simply copy and paste the below code to the Apps Script window and hit save. 

// Function to Clear the User Form

function clearForm() 

{

  var myGoogleSheet= SpreadsheetApp.getActiveSpreadsheet(); //declare a variable and set with active google sheet 

  var shUserForm    = myGoogleSheet.getSheetByName("User Form"); //declare a variable and set with the User Form worksheet

  //to create the instance of the user-interface environment to use the alert features

  var ui = SpreadsheetApp.getUi();

  // Display a dialog box with a title, message, and "Yes" and "No" buttons. The user can also

  // close the dialog by clicking the close button in its title bar.

  var response = ui.alert("Reset Confirmation", 'Do you want to reset this form?',ui.ButtonSet.YES_NO);

 // Checking the user response and proceed with clearing the form if user selects Yes

 if (response == ui.Button.YES) 

  {

  shUserForm.getRange("C4").clear(); //Search Field

  shUserForm.getRange("C7").clear();// Employeey ID

  shUserForm.getRange("C9").clear(); // Employee Name

  shUserForm.getRange("C11").clear(); // Gender

  shUserForm.getRange("C13").clear(); // Email ID

  shUserForm.getRange("C15").clear(); //Department

  shUserForm.getRange("C17").clear();//Address

 //Assigning white as default background color

 shUserForm.getRange("C4").setBackground('#FFFFFF');

 shUserForm.getRange("C7").setBackground('#FFFFFF');

 shUserForm.getRange("C9").setBackground('#FFFFFF');

 shUserForm.getRange("C11").setBackground('#FFFFFF');

 shUserForm.getRange("C13").setBackground('#FFFFFF');

 shUserForm.getRange("C15").setBackground('#FFFFFF');

 shUserForm.getRange("C17").setBackground('#FFFFFF');

  return true ;

  }

}

Function to validate data entry 

Even though we have created a function for all the buttons in our form, If we don’t add a function to validate data entry, we will end up getting an error message when attempting to perform any 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 that, simply head over to the Apps Script editor you have been working with for this project, then copy and paste the code below into the code window. 

//Declare a function to validate the entry made by user in UserForm

function validateEntry(){

  var myGooglSheet= SpreadsheetApp.getActiveSpreadsheet(); //declare a variable and set with active google sheet 

  var shUserForm    = myGooglSheet.getSheetByName("User Form"); //delcare a variable and set with the User Form worksheet

  //to create the instance of the user-interface environment to use the messagebox features

  var ui = SpreadsheetApp.getUi();

    //Assigning white as default background color

  shUserForm.getRange("C7").setBackground('#FFFFFF');

  shUserForm.getRange("C9").setBackground('#FFFFFF');

  shUserForm.getRange("C11").setBackground('#FFFFFF');

  shUserForm.getRange("C13").setBackground('#FFFFFF');

  shUserForm.getRange("C15").setBackground('#FFFFFF');

  shUserForm.getRange("C17").setBackground('#FFFFFF');

//Validating Employee ID

  if(shUserForm.getRange("C7").isBlank()==true){

    ui.alert("Please enter Employee ID.");

    shUserForm.getRange("C7").activate();

    shUserForm.getRange("C7").setBackground('#FF0000');

    return false;

  }

 //Validating Employee Name

  else if(shUserForm.getRange("C9").isBlank()==true){

    ui.alert("Please enter Employee Name.");

    shUserForm.getRange("C9").activate();

    shUserForm.getRange("C9").setBackground('#FF0000');

    return false;

  }

  //Validating Gender

  else if(shUserForm.getRange("C11").isBlank()==true){

    ui.alert("Please select Gender from the drop-down.");

    shUserForm.getRange("C11").activate();

    shUserForm.getRange("C11").setBackground('#FF0000');

    return false;

  }

  //Validating Email ID

  else if(shUserForm.getRange("C13").isBlank()==true){

    ui.alert("Please enter a valid Email ID.");

    shUserForm.getRange("C13").activate();

    shUserForm.getRange("C13").setBackground('#FF0000');

    return false;

  }

  //Validating Department

  else if(shUserForm.getRange("C15").isBlank()==true){

    ui.alert("Please select Department from the drop-down.");

    shUserForm.getRange("C15").activate();

    shUserForm.getRange("C15").setBackground('#FF0000');

    return false;

  }

  //Validating Address

  else if(shUserForm.getRange("C17").isBlank()==true){

    ui.alert("Please enter address.");

    shUserForm.getRange("C17").activate();

    shUserForm.getRange("C17").setBackground('#FF0000');

    return false;

  }

  return true;

}

Assigning Apps Script to our buttons

Now that we have successfully added the necessary function to Google Sheets’ Apps Script, we want to quickly show you how to assign Apps Script to our buttons. 

We will start off with the Save button for our form. 

Simply open the User Form Sheet and click on the Save button. You should see three ellipses. Click on that and select the option to “Assign script.” After selecting that option, a small box should pop up where you’ll need to enter a name for the Apps Script. Simply type in “submitData” and click OK. 

Test the ‘Save’ Button

After assigning an Apps Script to our button, let’s randomly test it to see if it works. Start by visiting the form and typing in the following details: 

• Employee ID: BUS1094
• Employee Name: Kimberly Webber
• Gender: Female
• Email ID: Kim@hotmail.com
• Department: Logistics 
• Address: HK Groove, Alleyway 

After entering the employee details above, simply click the Save button. 

If everything is working fine, you should see this prompt. 

To see whether the details were saved, head over to the Database Sheet and see if the information was stored there. Here is what our Database Sheet looks like: 

From the image above, you can see that our Save button works just fine. Now, repeat the process for the other buttons exactly as we highlighted above and test it to see if it’s working. 

Final thoughts

Creating a data entry form in Google Sheets can be challenging, especially because of the many steps involved. Sure, designing the form was a bit stressful, but then we tried to simplify the process by showing you how it’s done.

If you have always wanted to create a data entry form in Google Sheets that is both responsive and automated, you won’t go wrong following our comprehensive tutorial. From designing the form to applying the Apps Script code, today’s guide has everything you need to create this form.

If you have any questions or need clarifications about a step you don’t understand, feel free to comment, and we will be happy to respond. 

And don’t forget; we have added all the code you need to add the relevant functions to your buttons. Simply copy and paste to the Apps Script editor and save. 

Other Google Sheets Resources You May Find Useful

• How to Make a Bell Curve in Google Sheets
• How to Create a Combo Chart in Google Sheets
• How to Create a Timeline Chart in Google Sheets
• How to Create Dynamic Chart Range in Google Sheets
• How to Create an Area Chart in Google Sheets
• How to Pivot Table in Google Sheets
• Google Sheets Conditional Formatting Custom Formula (with Examples)
• How To Create Drop-Down List In Google Sheets (With Examples)