If you work with spreadsheets a lot, you've probably encountered data entry errors. Ensuring the quality and accuracy of data in spreadsheets is hard to do and this is a big problem that many organizations face. When errors creep into spreadsheets during data entry, they often go unnoticed and can result in erroneous conclusions. Garbage in, garbage out!
There are two common ways to prevent data entry errors from wreaking havoc in your spreadsheet:
For those of you that use Google Forms to enter data into a Google Sheets spreadsheet, you probably know how to open the form in a separate window and then use it to enter data.
One issue with this approach is that you need to keep referring back to the spreadsheet to ensure you did not inadvertently miss entering data. Imagine entering a list of 50 employee names into a spreadsheet from a printout. It's pretty easy to miss entering a few rows and when you end up with 46 rows when you should have 50, you'll spend a lot more time trying to identify the rows that you missed entering.
Can this be made more seamless? The answer is yes! With just a few lines of Google Apps Script code you can embed the Google Form directly into Google Sheets spreadsheet.
In this tutorial, I will show you how you can streamline data entry and minimize data entry errors by embedding the Google Form in the Google Sheets spreadsheet using Google Apps Script.
We will be working with a simple spreadsheet that has three columns: First name, Last name and Department. We will be using a Google Form to enter this data into our spreadsheet so there will also be a Timestamp column that is automatically created when you insert a form into the spreadsheet.
Instead of opening the form in a separate window, we will be building the following streamlined experience where the form is embedded directly in the spreadsheet.
Your browser does not support HTML5 video. Here is a link to the video instead.
This tutorial assumes that you're familiar with the following concepts:
In this tutorial I'm using a simple Google Form that has three fields: First name, Last name and Department. Please feel free to create a form based on your requirements.
Select Extensions —> Apps Script to open the Apps Script editor.
Your browser does not support HTML5 video. Here is a link to the video instead.
First, open the Apps Script editor and add a new HTML file called Sidebar.html.
Your browser does not support HTML5 video. Here is a link to the video instead.
Then, get the embed code for the Google Form and paste it into the section of the HTML. Change the width to 100% by setting width="100%". You can tweak the height later after seeing the embedded form.
How to get the embed code for the Google Form?
To get the embed code, click the Send button, choose <> and then copy the embed code.
Your browser does not support HTML5 video. Here is a link to the video instead.
When you are done, your Sidebar.html file should have the following code. If you copy paste the code below, please ensure that you replace with the actual id of your form.
First, we will create a custom menu called "Data entry" with a single menu item called "Add new employee". When this menu item is selected, the function showSidebar() will be run and this function will display the sidebar widget containing the embedded form.
//@OnlyCurrentDoc function onOpen() < SpreadsheetApp.getUi().createMenu("Data entry").addItem("Add new employee", "showSidebar").addToUi(); >function showSidebar()
Now that you have set everything up, it is time to test the functionality. To open the data entry form, select the Add new employee menu item from the Data entry custom menu that you created using Apps Script.
The Google Form will be displayed in a sidebar and you can use it to enter the details of a new employee. You can validate the data being entered by adding data validation rules to the Google Form.
As soon as you hit submit, a new row will appear in the spreadsheet displaying the information that you entered.
Once you add an employee, you can select Submit another response to add another employee.
That's it! You'll now be able to see the data you enter appear immediately in your spreadsheet. What is even better is that you can see all the existing data while entering a new row. Therefore, if you are entering data from a printout, you don't need to remember the last row you entered since this information is right in front of you.
Your spreadsheet will now benefit from fewer data entry errors and data inconsistency issues! All of this was made possible because of just 10 lines of Apps Script code. This is why I believe knowledge of Apps Script coding is truly a super power that can help everyone become more productive.
Tip: Automatically sort Google Form responses to ensure the latest entry is always at the top
If you're entering several rows of data, you might have to scroll to the bottom to see new rows that you add. This is because new form responses get appended to the bottom of the spreadsheet. You can use Apps Script to change the sorting so that newly added rows always show up at the top of the spreadsheet. I've written a tutorial that walks you through how to implement the functionality to automatically sort Google Form responses. If you routinely enter lots of data manually, this tip could help increase your productivity even more!
Data entry errors in spreadsheets are more common than one would expect. In this tutorial, I showed you how to streamline your data entry process by embedding a Google Form in your Google Sheets spreadsheet. This will let you see the data you're entering in real-time while leveraging data validations provided by Google Forms. In addition, you can also use spreadsheet data validation and conditional formatting to further ensure data quality.
By making data entry more robust, you can increase the quality of data in your spreadsheet and thereby increase the quality of decisions that are based on this data.
In this tutorial you learned how to embed a Google Form in a sidebar within Google Sheets and how to open the sidebar from a custom menu.
Your browser does not support HTML5 video. Here is a link to the video instead.
If you had difficulty understanding any concept in this tutorial or if you could not get the code to work, I'd love to hear from you using the form below so I can make it more useful. Thank you!
Thanks for reading!
Follow me via email to receive actionable tips and other exclusive content. I'll also send you notifications when I publish new content.
By signing up you agree to the Privacy Policy & Terms . Have feedback for me?I'd appreciate any feedback you can give me regarding this post.
Was it useful? Are there any errors or was something confusing? Would you like me to write a post about a related topic? Any other feedback is also welcome. Thank you so much!
Follow me via email to receive actionable tips and other exclusive content. I'll also send you notifications when I publish new content.
By signing up you agree to the Privacy Policy & Terms .Do you use spreadsheets a lot? Did you know that you can build simple applications using spreadsheets or even automate your work? If this sounds exciting, this tutorial is for you 🚀👉.
Learn Google Apps Script, learn how to use it to automate your tasks, and build simple user interfaces and applications in Google Sheets.
Learn how to send emails right from Google Sheets so you can create birthday reminders or automate sending those boring "Daily report" emails at work.
Learn how to build custom user interfaces like alerts, prompts, sidebars, etc. in Google Sheets using Google Apps Script.
In todays world, it can be useful to understand coding concepts even if you aren't a programmer. I'm writing a series of posts to try and teach some of these concepts using spreadsheets.