Thursday, February 07, 2008

[TIPS] Google spreadsheet forms - lessons learned

Thanks to a post from Tim Lauer ( I learned about the new forms in Google Spreadsheets. Instead of having to share the spreadsheet with the contributors (a real problem if you're talking about students with no email accounts) you can instead create an online form which can be used by the students to enter the data. VERY cool!!

It was VERY easy to create, too. It knew my column headings and the data types I wanted. But, I could choose to make the form have multiple choice values or check boxes, etc. You can even have it so that users can see the data that was entered by others. I sent the form to myself, both to my gmail account and to my work account. It worked perfectly in the gmail account. I entered the data right into the form that appeared IN the email (that was an option to send it that way) and <Presto!> it went right into the spreadsheet.

However, it did add a field called "Date and Time Stamp" and it moved everything over one column - from that row on. So, anything that was in the spreadsheet already is no longer lined up correctly. I managed to insert a column, move some data, and repair the form, and now it works just fine. But, the moral of the story is, if you're going to use a form, START with the form!

Oh, and if you've got an iGoogle portal page, you can drop the forms gadget onto that iGoogle page to monitor your form data. That is VERY cool!!

Remember the days when your students would each do an experiment and write their data onto their own spreadsheets? Can you see how this can REALLY add new dimensions to the labs?

This is SO much fun!

Lessons learned:

1) Don't practice on live/real data.
2) No matter what you do you can fix it
3) If you want to use a form, START with the form to get the columns aligned correctly.
4) I hope the person who invented Undo is filthy rich!


walhus said...

How do you simply move columns around in the google spreadsheet? can't seem to find this in google help.

Ben Chun said...

You can cut a column (right click, choose "Cut"), insert a new column where you want it, paste, then delete the now-blank old column. Your form will still work the same way.

Juan Pablo said...

Great blog, thanks for your tips!
I'd like to put time stamps for some columns in my spreadsheet, how can i do that?

Jim Gates said...

The timestamp column was added automatically when the form was made. You don't have to do anything.

Juan Pablo said...

Hi thanks for replying; I wasn't referring to the time stamp for the form-submitted row of data. I was wondering about how I can make a new time stamp to track entries into a specific cell /column. I couldn't find any formula options from the gdocs knowledgebase. For ref, I'm basically experimenting with putting together a workflow starting with person A from a google doc forms and ending with person b acting on the submitted data by filling up a cell through the spreadsheet file; i'd like to put a time stamp on person 2's data entry...

Jim Gates said...

Oh boy... That one is beyond me, as well. I'm thinking that something like that would be better done in basecamphq or a similar project management software. I know you can get some basic free functions in basecamphq that just might do the trick for you.

Good luck. Sorry I'm not more help to you.