Data validation on Google spreadsheets

Written by
Jonatan Silva

I used to work in a company that had built up an entire ERP on top of spreadsheets. We had moments when many different personnel were inputting data—while the manager was creating reports and doing analysis at the same time. Spreadsheets offer a lot of freedom, but this can lead to messy data. After all, who has never typed the wrong information without realizing it? We all make mistakes with data entry in spreadsheets. In my company we had to implement several different solutions in order to minimize the problem of human error: from simple cell validation to advanced HTML forms.

Creating a Google Form

There is a powerful feature inside Google Sheets called Forms. It lets you gather information from coworkers without asking them to even open the spreadsheet. They just fill out the information on their email or in a browser, and their answers are automatically added to a spreadsheet that you control. Just open any spreadsheet in Google Docs and click on MENU > Insert > Form. When you create a form, you enter an introductory text and you can list the fields that you need your workers to fill. You can choose from text (short or paragraph), multiple choice, checkboxes, linear scale, lists, date and time. Plus you can set the data validation with number, text, regular expression and you can set each field as optional or mandatory. You will watch in real time as data provided by your co-workers flows effortlessly into your master spreadsheet.

You’ll need to use HTML Service and some AppScript programming, if you need a more advanced type of validation on your form. Consider a situation where the user has to register the supplier name on every invoice. This information needs to be consistent, otherwise your analysis of the invoice data will give multiple results where there should only be one. In this scenario you would need to pre-load a list of suppliers into a listbox in order to select your users. I could create a blog post on how to create such dynamic forms, but for now let’s keep it simple.

Embed the new Form into a website

Of course we do not want anybody to input data into our master spreadsheet and the easiest and fastest way to add an extra layer of security is to either configure the Form to only allow people from your company domain (in G Suite) to input data or you can embed it on your intranet site and give permissions only to certain authorized workers at the page level.

Locking the spreadsheet

As you have created the form to make your workers input data correctly, there is no reason why they would need to go to the main spreadsheet. In order to avoid users logging in and making any undesirable changes, you should lock the spreadsheet by editing the share settings and removing the users.

You may also like…