Teacher Tech blog with Alice Keeler

Paperless Is Not a Pedagogy

Alice Keeler

Create a Unique Google Forms URL for Each Student

Create a Unique Google Forms URL for Each Student

alicepresent

 

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

  1. Create a Google Form.
  2. In the edit screen under the Responses menu is an option to “Get pre-filled URL.”
  3. Fill in the fields you want to be pre-populated with place holder data.
    Example: LASTNAME in the Last name field.
  4. Click submit.
  5. Copy the unique URL provided.
  6. Locate or create a spreadsheet that contains the information you want to pre-populate.
  7. In the blank column next to your data type an equals sign, quotation, and paste the URL between quotation marks.
    =”URL”
  8. Locate the placeholder data in the URL.
  9. Highlight the placeholder data.
  10. Type “&&”
  11. In between the ampersands (&) place your cursor.
  12. Type the cell reference for where in the spreadsheet that information is located.
    Example: “&A5&”
  13. 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.

Educause Slides

YouTube video

25 thoughts on “Create a Unique Google Forms URL for Each Student

  1. 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?

    1. 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.

  2. 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.

  3. 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?

  4. 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

  5. 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?

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

    1. 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.

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

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

© 2024 All Rights Reserved.

💥 FREE OTIS WORKSHOP

Join Alice Keeler, Thursday Mar 21st or register to gain access to the recording.
Create a free OTIS account.

Join Alice Keeler for this session as we learn how to revolutionize your assessment strategies. We will dive into the essentials of crafting high-quality rubrics that go beyond traditional scoring methods to offer rich, meaningful feedback. We’ll learn about the components of an effective rubric and how to mathematically ensure the accuracy of your assessments.

Exit this pop up by pressing escape or clicking anywhere off the pop up.