Create a Unique Google Forms URL for Each Student

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.

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?

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.

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?

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?

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!!

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.

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!

My Referral Links

50% Off February Special

Online Workshops for Google Sheets, Classroom, Slides and more are 50% off for February. Use the discount code february at checkout. Also annual subscriptions for access to all of the Go Slow online workshops are 50% off!