Skip to main content

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.

Comments

  1. Hi Tom! I love reading what you are doing with Google docs - pretty amazing. I have a real interest in being able to implement something like this and was wondering if you have written down the full process of what you did to make this happen. Or if you have a working example that I can look at and learn from. Your help is greatly appreciated. Kristy

    ReplyDelete
  2. I'm just putting the finishing touches to a few projects we've been working on, inlcuding this one... so anytime soon is the best I can do...

    ReplyDelete
  3. I'm really interested in learning more about this. Have you published the full process? I'm interested in what template you're using as well. Many thanks! Kyle

    ReplyDelete
  4. Kyle, the files are too interdependent to release a file as such. It's more of an approach; cobbled together from the bits and bobs in my other blog posts.

    I've found that the power is in being able to cobble your own solution and interface, not in having a generic "workflow process tool". If you wanted a generic tool, take a look at KissFlow, it looks great and can save its data in Google Drive.

    ReplyDelete
  5. Tom, really appreciate what you are doing. I am currently working in start up, which is into logistics. I would definitely like to try this for my use case.
    Currently the process is being handled by using Excel/Google docs. Have also been using Collatebox.com, but still in their basic plan.
    Could you help me identify the right tool?

    ReplyDelete
  6. JohnD,

    I think I'm moving away from the idea of "Spreadsheet instead of a Form"... Simply because text editing is still quite cumbersome.

    I'm looking more in the direction of Documents + Sidebars ( which talk to spreadhseets ) ... seems more natural.

    Tom

    ReplyDelete

Post a Comment

Popular posts from this blog

Inserting A Google Doc link into a Google Spreadsheet

This article looks at using Apps Script to add new features to a Google Spreadsheet.

At the University of York, various people have been using Google spreadsheets to collect together various project related information. We've found that when collecting lots of different collaborative information from lots of different people that a spreadsheet can work much better than a regular Google Form.

Spreadsheets can be better than Forms for data collection because:

The spreadsheet data saves as you are editing.If you want to fill in half the data and come back later, your data will still be there.The data in a spreadsheet is versioned, so you can see who added what and when and undo it if necessaryThe commenting features are brilliant - especially the "Resolve" button in comments.
One feature we needed was to be able to "attach" Google Docs to certain cells in a spreadsheet. It's easy to just paste in a URL into a spreadsheet cell, but they can often all look too si…

Writing a Simple QR Code Stock Control Spreadsheet

At Theatre, Film & TV they have lots of equipment they loan to students, cameras, microphone, tripod etc. Keeping track of what goes out and what comes back is a difficult job. I have seen a few other departments struggling with the similar "equipment inventory" problems.

A solution I have prototyped uses QR codes, a Google Spreadsheet and a small web application written in Apps Script. The idea is, that each piece of equipment ( or maybe collection of items ) has a QR code on it. Using a standard and free smartphone application to read QR codes, the technician swipes the item and is shown a screen that lets them either check the item out or return it.

The QR app looks like this.



The spreadsheet contains a list of cameras. It has links to images and uses Google Visualisation tools to generate its QR codes. The spreadsheet looks like this.


The Web Application The web application, which only checks items in or out and should be used on a phone in conjunction with a QR cod…

Getting CSV data into Google Spreadsheets Automatically

Today I was attempting to get CSV data from Estates' Alarm System into Google Docs as a spreadsheet. There were two ways to try and achieve this...


Create an AppScript in Google that pulled a .CSV file from a web serverWrite a (python) script on the local machine that pushed the data into Google Spreadsheet by using the API. The Google AppScript Way As you know, my JavaScript ain't great, but it initially looked like it was going to work... Some code like this below and using the Array to CSV functions from here, looked promising.



function encode_utf8( s ){
//This is the code that "I think" turns the UTF16 LE into standard stuff....
return unescape( encodeURIComponent( s ));
}

function get_csv(){
var url ='http://www-users.york.ac.uk/~admn812/alarms.csv.Active BA Alarms.csv';// Change this to the URL of your file
var response = UrlFetchApp.fetch(url);
// If there's an error in the response code, maybe tell someone
//MailApp.sendEmail("s.brown@york.ac.uk&qu…