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.
Data Disaster
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.
Pre-Populate
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.
Uses
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.
Steps
- 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.
=”URL” - 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.
Example: “&A5&” - 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.
25 thoughts on “Create a Unique Google Forms URL for Each Student”
Very cool. Is it possible to add yet another column that would take each custom URL and shorten it?
Sure, if you have a formula to do that. I use =hyperlink(“URL”,”CLick Here to assess “&A2)
I ran into an issue when trying to replicate. Some (including the first entry) don’t become active URLs. You can copy past into browser and it works, but if I’m giving my audience access to the spreadsheet with their name, I wouldn’t want to leave dead links for some.
Here’s an example of fake data that I published to the web for you: http://goo.gl/DrfN5o
Also, for sensitive student data, I would like to give them access to the roster spreadsheet while hiding the columns that I don’t want them to see, while keeping First/Last name and URL visible and protected. Got any ideas for distribution like that?
Using =importrange on a different spreadsheet to only display the data you want will help with your student information. Spacebars might be your problem with the non live hyperlinks.
That’s a really great idea and I can see how it could be very useful. How do you typically distribute the URL information to each person/student if you have a large list of entries? Do you email each individual their URL? That would be a lot of individual emails to send…I’m guessing you have a better idea than I do.
I share the spreadsheet with them, they find their name.
Great video! Is there any way to prevent respondents from editing the pre-populated field.
No, but you can just drag the formula down to copy over what they messed up.
Alice, So helpful! We are using this tool for iPad check in/out with a QR code. When I submit the form that was sent with the prefilled URL the prefilled fields are not being captured in the form responses sheet. I prefilled Student name and when I submit the form the student name is missing. Any ideas?
Sorry Jacquie, I can not tell without looking at your formulas. Most likely, you have something wrong with the formula you wrote.
Just tweeted at you but 140 characters is never enough!
I got your magic sauce “&&” working great for my pre-filled urls but want to find a way to place a logical expression on the confirmation page of a form that sends to another pre-filled form. Counseling staff at my school need regular access to a teacher obs forms for individual students. At the moment, I have a link on the confirmation page to a sheet that has 1300+ student names hyperlinked to their pre-filled form. Is that the best option?
@dackleychem
You can not have custom end screens for Google Forms.
Thanks!
Hey Alice, I’ve watched your video multiple times. However, when I click on the prefilled URL of the form, I get all the fields filled with ” instead of the data. Do you have some suggestion for me?
Sorry, not without looking at your formulas. You probably have one small error, keep squinting. 🙁
The only mistake I find is that I didn’t enter =” before pasting in the prefilled URL. When I enter =” and a ” after the prefilled URL, the result is not a link. I can’t click on it and get anywhere. If I remove =” though, it becomes a link to my Google form. The link however only prefills the “. Is it possible that something has changed since your video? I appreciate your help SO MUCH!!
Try using =hyperlink(“URL”,”Click Here”)
I had the same issue. What I discovered was if you have a space in between the “&&” it stops reading the URL and will only return the opening “. For Example: “&Westbrook Way&” because of the space it disrupts the URL. Try instead “&Westbrook_Way&” with an underscore where you have a space. It worked for me after many frustrating hours of fiddling. I am pretty sure it will work for you.
Actually use a PLUS for the space. So Westbrook+Way
Have they taken away “Get pre-filled URL” I can not find it anywhere!??
It is there under the 3 dots.
It worked for me once, but it wouldn’t work on other forms. Finally I added =HYPERLINK(“https://docs.google.com/a/etc”) instead of just =”https://docs.google.com/a/etc” and it worked! Thanks for making this available on your blog and in the YouTube video! It’s amazing to me that no one else has videos on this REALLY useful trick!
It´s a best solution thanks.
Hi Alice! When I generate my prefilled URL, there are no placeholders, just random letters/numbers. Is there a way I can tell Forms that I want the prefilled URL with placeholders? thanks! (this is the URL it generated https://docs.google.com/a/wascohsd.org/forms/d/1A51osCC4Tm5yqLzOgxi6NzpJWETMWJfkrEF__bzYV4I/prefill)
Katie I think this post is old. The new system still works but not quite as intuitive.