Simplifying data entry with a Google Form

[Michael Vaughn]

I've been able to successfully create and use a Google Form to enter data into a Google Spreadsheet for TimelineJS.

For context: I'm an instructional technologist who works with educators to develop methods of using technology to enhance teaching and learning, so I'm coming at this from a non-commercial and non-technical angle (it has to be something I can explain to any student in less than half an hour). This solution was designed with the intent of allowing multiple collaborators (e.g. students) to quickly add information to a timeline by submitting content through a Google Form. Here are my current test results:

The live form: https://docs.google.com/forms/d/1yHN4C8YlHnGPU26ItUBr5h5ZX27JFD2Ro8SC5tkGQs8/viewform

The speadsheet (results): https://docs.google.com/spreadsheet/ccc?key=0AuiLPq7NknYZdFRfcUgxWTdyTjZOeTFHZC1hYU5nYVE&usp=sharing

The preview: http://embed.verite.co/timeline/?source=0AuiLPq7NknYZdFRfcUgxWTdyTjZOeTFHZC1hYU5nYVE&font=Bevan-PotanoSans&maptype=HYBRID&lang=en&height=650

Here's the setup:

1. Create the Google Form and title each "question" based on the headings in the Google Spreadsheet template: https://drive.google.com/previewtemplate?id=0AppSVxABhnltdEhzQjQ4MlpOaldjTmZLclQxQWFTOUE&mode=public (you can also try and copy mine above, but it will still need the additional formatting I've outlined below). 
   
2. Set the Response destination to be a new spreadsheet
   
3. Set the spreadsheet to be published (http://timeline.verite.co/#fileformat)
   
4. Generate your embed code like you normally would (http://timeline.verite.co/#embed)

Here's how to format the spreadsheet:

1. Hide the Timestamp column (Column A)
   
2. Select the Start Date column (the new Column A), and choose Format > Number > Date Time
   
3. Optional: Select the Start and End Date columns and create a Data Validation rule for dates
   
4. Optional: Select the Media and Media Thumbnail columns and create a Data Validation rule for text urls
   

That's all there is to it. The only weirdness I've found is:

A) You have to format the spreadsheet each time you create a new one, even if you copy the form. On the plus side, you only have to do this once and it takes less than a minute if you're very comfortable with spreadsheets.

B) When the date is entered in the Form, Google Drive is automatically inserting an apostrophe in front of it when it goes into the spreadsheet (so it appears as '05/20/2013 instead of 5/20/2013). This prevents the date from being formatted properly, and you have to manually remove it. Once you delete the apostrophe and hit Enter (or click on another cell), the date will automatically reformat itself.

I actually don't mind that little glitch (B) because the instructors I'm working with wanted a system where they could "approve" content that had been submitted. This seems to do the trick. It's also worth noting that you can manually enter data into the spreadsheet without using the form, and you can edit spreadsheet content that was entered via the form without any issues. 

Just sharing. 

 

 

[Brad Belbas]

Michael,

This is a huge help.  As a fellow instructional technologist, my colleagues and I really appreciate you for taking the time to lay everything out in detail. 

Thank you.

[Ryan Graff]

This is great!

Thanks for sharing!

On Tuesday, March 19, 2013 3:11:53 PM UTC-5, Michael Vaughn wrote:

[Syrian Prince]

Excellent! Thank you for sharing. Now to try it!