One of the presentations I did in the Google Booth at Educause this year was on using concatenate for a spreadsheet. While you do not need to know the word concatenate to do this trick, it is wickedly useful.
One of the best tools in the Google Apps suite is Google Forms. It allows any user on practically any device to fill out a Form. The user does not have to be logged in, have a Google account or even know what Google is. The data collected from the Form can be viewed in a spreadsheet and manipulated any number of ways. It is a beautiful thing.
The problem is the end user. When you let other people enter the data they can get creative or make mistakes in how they enter information. When sorting and organizing information, consistency of the data is very important. As much as possible I try to use multiple choice or choose from a list when users fill out forms to force the response options to be consistent.
Unfortunately there are fields you would not typically want to make multiple choice. Name, ID number, email address, etc… the person filling out the form has to put this information into a text field.
Google Forms allows you to pre-populate the fields of the Form. This means you can create a unique URL that when someone clicks on that link some of the boxes will be filled out already. Click Here for an example.
If you use the same Google Form repeatedly, such as for a daily warm-up, each student can have a unique URL that automatically pre-populates their SID, First Name, Last Name and Email Address.
For peer evaluation where students have to fill in the project title and name of another student, having a unique URL will allow the students to go straight to the rubric and bypass filling out the text fields.
When putting on a professional development (PD) conference event the same evaluation form is used for each session, each presenter. Rather than relying on the end user to enter in the session title, presenter, room number, session slot, etc… these can be pre-populated so the person filling out the form only needs to rate the quality of the presentation.
- Create a Google Form.
- In the edit screen under the Responses menu is an option to “Get pre-filled URL.”
- Fill in the fields you want to be pre-populated with place holder data.
Example: LASTNAME in the Last name field.
- Click submit.
- Copy the unique URL provided.
- Locate or create a spreadsheet that contains the information you want to pre-populate.
- In the blank column next to your data type an equals sign, quotation, and paste the URL between quotation marks.
- Locate the placeholder data in the URL.
- Highlight the placeholder data.
- Type “&&”
- In between the ampersands (&) place your cursor.
- Type the cell reference for where in the spreadsheet that information is located.
- Fill down in the spreadsheet by holding down the corner of the cell with the formula you just created and pulling down. This will create a unique URL for each row in the spreadsheet.