In a previous blog post I explained how in 5 easy steps you can learn how to pre-populate a Google Form. I would like to show you how you can use spreadsheets to take this one step further.

In my quest to go paperless I created a form for students to turn work into.  This is one form that they will use all year.  I ask them to submit their Student ID (SID), First Name, Last Name, Email, Assignment Number, Assignment Title and a URL link to their work.

The problem is of course that is a lot of fields for the students to fill out.

Here is what you can do:

• Open the form that the students fill out.
• Copy the URL for the form (make sure you take off#gid=0 if it is part of the URL)
  • https://docs.google.com/spreadsheet/viewform?formkey=dHVxWUlrREx5V0Z5M0ZiRk5qbVVRM3c6MQ#gid=0
• Create a spreadsheet that contains each students SID, First Name, Last Name and Email address.
  • Hint: Make a different Google Form and ask the students to fill this out one time.
  • 
• This data takes up columns A – E.
• In column F write a formula for the customized URL by starting with an equals sign.
• Within “quotations” paste the URL.
• You want to join this URL with the &entry_0= that will create the pre-populated form.
• You will need to use an ampersand & to join each field together.
• Each &entry_#= will need to be in quotations.
• Sample Custom URL formula
  =”https://docs.google.com/spreadsheet/viewform?formkey=dHVxWUlrREx5V0Z5M0ZiRk5qbVVRM3c6MQ&ifq”&”&entry_0=”&B2&”&entry_1=”&C2&”&entry_2=”&D2&”&entry_3=”&E2
  • Pay close attention to where the quotations are and the ampersands are.
  • =“https://docs.google.com/spreadsheet/viewform?formkey=dHVxWUlrREx5V0Z5M0ZiRk5qbVVRM3c6MQ&ifq“&”&entry_0=“&B2&”&entry_1=“&C2&”&entry_2=“&D2&”&entry_3=“&E2
  • There is an ampersand & to append each of the cells to the end of the URL.
  • There is also an ampersand that is part of the formula that needs to be in between the quotation marks to indicated that it is part of the final text.  This causes the appearance of double ampersands in some places of the formula.
• Copy and paste the formula down the page to create a custom URL for each student.
• Have each student bookmark their customized URL and to use this customized URL to turn in their work.

Another use for this is to create custom URL’s for an event.  In the spreadsheet you might have a list of event session titles, presenters name and room number.  This same formula can be used to create a custom URL for each session so that when participants use the link to evaluate the session they do not have to worry about correctly identifying the session or spelling the presenters name.