How to use Google Sheets to Manage Data Entry Works

“Garbage in, garbage out” is a common saying in the computer world to describe how important the quality of the data is. If your input nonsense data, you will also get a nonsense output. So, to get more accurate results, you should minimize the errors in your data. Error in data entry is one such point you should be very careful about. This post will show you how you can use Google Sheets to manage your data entry works. And also, this post is a roundup of previous posts on Google Sheets data entry forms.

Table of Contents

Why use Google Sheets for data entry works

Being a cloud-based spreadsheet application, Google Sheets has many advantages. You can use Google Apps Script with Google Sheets to develop your own custom applications. The following are the most common benefits of Google Sheets in terms of data entry works.

  • Google Sheets is free. You can build advanced web apps using Google Sheets and Google Apps Script with no financial cost.
  • You can develop several types of Data Entry forms with Google Apps Script and save the data in Google Sheets
  • You can share data entry forms with others
  • Protect the entered data from manipulation and accidental changes
  • You can hide the data sheet even from the data entry staff
  • Real time monitoring of data entry work progress
  • Spot the errors in real time and reduce the future cost of correcting errors
  • Start analysis even before the completion of data entry work
  • Link the data with Google Data Studio and set up a dashboard to share the progress with management

Type of data entry forms that can be created with Google Sheets and Google Apps Script

If you are a returning user of this blog, you know that we have discussed several types of data entry forms that can be created with Google Sheets and Google Apps Script. We have created forms using cells in the Google Sheets and forms using HTML. I can group these forms into the following categories.

  • Forms display inside the Google Sheets
    • Forms created with spreadsheet cells
    • HTML Forms (Sidebar, Modal Dialog forms)
  • HTML forms that can store data in Google Sheets (Web Apps)
    • HTML data entry forms created with the bound script
    • HTML data entry forms created with the unbound script

Forms displayed within the Google Sheets

You can create forms inside Google Sheets and share them with others for data entry works. However, with this type of data entry form, you have to give edit access to data to all who enter the data.

Forms created with spreadsheet cells

You can use spreadsheet cells as the form fields and design the data entry form. Then, create an Apps Script to read and submit the values in the relevant cells to the database (another sheet). You can assign this script to a button (button image).

See the following video to know how it works.

You can learn more about this type of data entry form from the following blog post.

HTML Forms (Sidebar and Modal Dialog forms)

The data entry forms explained above have several advantages compared to entering data into a mere spreadsheet. However, that method is not efficient in terms of the speed of data entry. Because, there are some issues like being unable to use the Tab key to move among form fields, and being unable to use Enter key to submit data.

You can overcome these issues using HTML forms. With Google Apps Script’s HTML Service and Spreadsheet Service, you can build HTML-based Modal Dialog and Sidebar forms inside Google Sheets.

Watch the following video to see how these Modal Dialog and Sidebar forms work.

From the following blog post, you can learn how to create the sidebar and modal dialog forms shown in the above video.

HTML forms that can store data in Google Sheets (Web Apps)

The data entry forms mentioned above display inside the Google Sheets. So, it is essential to share the Google Sheets with others with edit access to enable them to enter data. This is less secure since they can accidentally alter the data. And also if you do not want others to access your entire dataset.

In such situations, you can create a web app using Google Apps Script for your data entry works. In this method, you will get a separate URL to the data entry form to share with others. The data entered with this form is inserted into a Google Sheet, and it is not necessary to share the Google Sheet with the data entry staff.

We can create these web apps using both bound and unbound scripts. You can create a data entry form with the same functionalities using both these methods. We list a few examples of both these methods below.

The data entry form created using this method is my favorite. There are enormous benefits to your data entry work from the forms created with this method.

  • You can share the form with the data entry staff without giving access to the datasheet.
  • Embed these web apps in Google Sites to create a complete web app.

HTML data entry forms created with the bound scripts

In brief, bound Apps Script opens from the Google Sheets menu (Extensions > Apps). You cannot detach the bound script from the Google Sheet it created. Even though the script is bound to the Google Sheets, you can still get a separate URL to the web app.

In the following video, you can see a demonstration of the data entry form created using this method.

If you would like to create a form as shown in the above video, you can find more details about it in the following blog post.

HTML data entry forms created with unbound scripts

Unlike bound script, you can create an unbound (stand-alone) script directly from Google Drive or by going to script.google.com. When you use this method you have to create Google Sheet and Apps Script files separately.

The following video demonstrates a complete CRUD (Create, Read, Update and Delete) application created with Google Apps Script.

The following video demonstrates a data entry form created with Google Apps Script.

The application demonstrated above allows you to edit and delete previously entered data. You can read the complete blog post from the following link.

Other helpful tips for your data entry work

Besides the examples shown above, you may also require some other features like file uploading, search fields, dashboards, etc… for the data entry application you want to build.

I will list such helpful blog posts below.

Upload files to Google Drive with Google Apps Script Web Apps

Sometimes, your data may contain images, PDFs, or some other types of files. In such cases, you need to have a file upload feature. You can learn how to implement file uploading functions to your data entry form from the following blog post.

How to Embed Google Apps Script Web Apps in Websites

You can embed your forms (web apps) on websites. So, you can easily share these forms with website readers.

You can easily embed these forms/ web apps in Google Sites. And also you can create a complete web app on Google Sites using the other tips we discussed in this post.

You can learn more about embedding Apps Script web apps on websites from the following blog post.

Pull and display Google Sheets data on an HTML page

You can display the data in Google Sheets on an HTML page using Google Apps Script. With this method, you can show the entered data to data entry operators without giving access to Google Sheets.

The following blog post explains how you can pull Google Sheets data and display them on an HTML table using Google Apps Script.

By using the method described above, you display all the data available on the Google Sheet in the HTML table. However, there may be situations where you need to display only the user-requested data. In such cases, you can create a search field on the HTML page and ask users to submit their requests. Then, display only the requested data if they are found. The following video demonstrates this type of a form.

The following blog post explains how you can create a web form/ search field to search data in Google Sheets.

Create an online dashboard to view your data

It is essential to check the progress as well as the correctness during the data entry. It will help you correct any mistakes and avoid repeating the data entry. And also this will help you reduce the cost of data entry work.

You can use the dashboard to view a summary of data entry progress. And also you can create graphs/ charts and calculated results to check the errors and unusual entries in the entered data.

You can use it to connect the Google Sheets with Google Data Studio and create a stunning online dashboard for free. You can learn more about creating a dashboard in Google Data Studio from the following blog post.

Wrapping Up

You can use Google Sheets and related products to manage data entry works efficiently. You can use Google Sheets as the database and Google Apps Script to create data entry forms. On our blog, bpwebs.com, we have been discussing several types of applications related to data entry forms. So, this is a blog roundup that will help you to decide which type of data entry form is suitable for you to manage data entry works.

Was this helpful?

If you find our articles helpful, you could subscribe to our newsletter and have it delivered to your inbox.

2 thoughts on “How to use Google Sheets to Manage Data Entry Works”

  1. Good to see you after a while,
    I have a fan of you,
    all of your projects benefits a lot
    Thanks a lot.
    in your ( ..Data Entry Form that can Perform CRUD… ),
    i have still one issue, please guid me.
    Once we submit the data, it’s saving as string( with prefit ‘ ) however we want the actual data type to be submit,
    Ie, if we enter Date it should be as date, Not with preifx ( ‘ ),
    Ie, if we enter Number it should be as number, Not with preifx ( ‘ ).

    I’ll be very thankful of you for your kind help.
    Thanks
    Mohammed

    Reply
    • Hi Mohammed,
      I’m glad to hear that it helped you!

      For your issue,
      In lines 77 and 93 of the Code.gs file, change the valueInputOption from “RAW” to “USER_ENTERED”.

      You can read the documentation here.

      Reply

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

%d bloggers like this: