Using Spreadsheets INSTEAD of Forms

Google Forms are a great way to quickly collect data into a spreadsheet but what if the data you are collecting is a bit too complex for a simple form to handle?

We've been experimenting with using a Google Spreadsheet, instead of a Form to gather information and finding that this approach has many advantages. We still use a Form to "initiate" the process, and the data gathered from the form is saved in a "central" spreadsheet.

When the form is submitted, the central spreadsheet makes a copy of a "template" spreadsheet. This spreadsheet is more "human readable" than a regular spreadsheet. When the form is submitted, the data is saved as normal, but it also fills in certain values in the copied template sheet, sets the right permissions and mails all the people who need to know about it a link to edit the template copy.

This template sheet has a "form-like" layout, including help ( shown at the right hand side ) and additional tools added with Apps Script. Tools include the ability to add a link to another Google Drive file. There are times when the data you want already exists ( as a document ) and you don't really want people to copy and paste that data into the form.

The copied template looks like this. Each of the fields has been sized ready to fit the data we expect. Some are even "colour bordered" to help different departments find their bits.

The "Central" Spreadsheet

The central spreadsheet, or the one that receives the input from the initial Form keeps a track of which files have been created, saving links to them in each row. It also has a "status" column that the copied spreadsheets know how to update as they get completed. This makes it really easy to add a simple "Status listing" web app to a Google Site ( shown below with dummy data ).

This means you can give nice "live" summaries to certain groups of people without scaring them with the prospect of looking a huge and hairy spreadsheet.

When Might I Use A Spreadsheet Instead of a Google Form?

If Your Data Will Be Very Textual and Complex

If you want to gather lots and lots of long bits of text, a Google Form might not be the best way for people to enter that data, mainly because a Form expects you add your data in one sitting. ( You can allow people to be able to edit their responses, but somehow this still doesn't feel right ).

We've found that when data is long and textual, even viewing it in a standard spreadsheet if difficult, and so have created lots of scripts to render someone's form data into a Google Document in a more readable format.

If Your Data Needs To Be Filled In Collaboratively

It's much easier to work on a shared spreadsheet that looks like a form than in a per-row spreadsheet.
Additional benefits of using spreadsheets, as opposed to Forms, include:

• all the changes are saved in the file's "Revision History". You can see who added or deleted what and when.
• All the edits are saved on-the-fly.
• You can easily lock down parts of the spreadsheet so that people don't accidentally change it.

The "Insert Comment" feature, with the ability to "+" add someone is fantastic. If a part of the input isn't clear to you, you can ask for help from someone and they get a link to come and comment on that part of the document.

If Your Data Is Modular

In our spreadsheet, we even have a template sheet that, using custom made menus can be used to make "more sheets like this one". This is great in those circumstances when you might want people to submit something like recipes, each with the same items ( such as ingredients, method and picture ) and you want to be able to allow them to add one, two or twenty recipes.

Conclusions

So far, we haven't used this in anger yet, but as an approach I really like it. It uses a combination of Google Apps and doesn't get too fancy. There is a huge temptation to make this sort of system do things that maybe don't need doing.

Keeping it simple has been the main design ethic.

Most of the things we thought we'd need in terms of functionalities have turned out to already exist ( such as "Named and Protected Ranges" ) in the tools themselves, OR they can be achieved by just agreeing to work a certain way ( no software creation required ).

One of the things I like about this approach is that at the end of the day, they are all just first class Google files that one could, if need be, keep updated by hand, but it makes the whole process easier by gently easing all the things you'd need to do by hand, sending emails, adding permissions, creating files, adding calendar entries etc.

I'll let you know how we get on.