Photo Credit: quinn.anya via Compfight cc
I like to have my students do peer evaluations on student presentations or student projects.  An issue is having students type in the name of their classmate. Frequently they will misspell or otherwise make a mistake on communicating identification data back to me.  In order for me to run a pivot table to analyze the data I need the students identifier to be consistent.

Step 1: Create a Google Form for students in the class to fill out with their name and project title (for group projects I ask for a team name).  If you do not have your Forms automatically create a spreadsheet you will need to click on the “choose response destination” button in the Form edit screen.
Click Here for a sample form.

Step 2: Create another Google Form with your peer evaluation rubric on it.  Use the question type “Grid” to create the rubric.
Click Here for a sample form

Step 3: From the Form edit screen, under the Responses menu, choose “Get pre-filled URL”

Step 4:  Fill out a pretend student who is going to be evaluated.

At the bottom click on the submit button

This will generate a pre-filled URL

Copy this URL

Step 5: Go back to the spreadsheet of the first form that just asked for student identifiers for their project.
Click Here to view the sample spreadsheet

Step 6: Create a column for evaluation, paste the pre-filled URL into the column

Step 7: You will want to change the URL into a formula that draws the information from the the form.  Double click on the cell with the hyperlink to the evaluation in it. Put your cursor at the front and type an equals sign (=) to start a formula and then type quotations  ” at the front and end of the URL

Example: https://docs.google.com/forms/d/1VEEZZwhuGCv_Ay2V14JOZj_ysnmlAtMSaV2aBxre-
1c/viewform?
entry.1286231922&entry.1709658078&entry.189485916&entry.170201175=SampleLast,+
First&entry.1665572164=PutSIDhere&entry.282996263=Project+Name+Here&entry.2134434756

Change this to =”https://docs.google.com/forms/d/1VEEZZwhuGCv_Ay2V14JOZj_ysnmlAtMSaV2aBxre-1c/viewform?
entry.1286231922&entry.1709658078&entry.189485916&entry.170201175=SampleLast,+
First&entry.1665572164=PutSIDhere&entry.282996263=Project+Name+Here&entry.213
4434756

You are going to want to look at the sample URL you created to see where you want to trade your sample data for what is in the spreadsheet.

Notice where it says “SampleLast” this is where you want to put the cell reference of where the last name is located in the spreadsheet.
Highlight the sample last name and replace it with “&&”
The quotation ends the text
The ampersand (&) says AND you want to join that text with a cell
The 2nd ampersand says AND you want to join that cell with more text
And the quotation maintains that the rest of the formula is a text field.  Each section of text has to have beginning and ending quotations.

In between the “&&” put the cell that contains last name.

=”https://docs.google.com/forms/d/1VEEZZwhuGCv_Ay2V14JOZj_ysnmlAtMSaV2aBx
re-1c/viewform?
entry.1286231922&entry.1709658078&entry.189485916&entry.170201175=“&D2&”,+Fi
rst&entry.1665572164=PutSIDhere&entry.282996263=Project+Name+Here&entry.2134
434756

Now notice in the formula where it says “First”
You will want to replace that sample data with “&&”
In between the ampersands put the cell that has the first name.

=”https://docs.google.com/forms/d/1VEEZZwhuGCv_Ay2V14JOZj_ysnmlAtMSaV2aBxre-1c/viewform?

entry.1286231922&entry.1709658078&entry.189485916&entry.170201175=
“&D2&”,+“&C2&”&entry.1665572164=PutSIDhere&entry.282996263=
Project+Name+Here&entry.2134434756

Notice I wrote “PutSIDhere” for where I wanted to insert the SID into pre-filled form.
Replace this with “&&”
Insert the cell with Student ID in between the ampersands.

=”https://docs.google.com/forms/d/1VEEZZwhuGCv_Ay2V14JOZj_ysnmlAtMSaV2aBxre-1c/viewform?entry.1286231922&entry.1709658078&entry.189485916&entry.170201175=”&D2&”,+
“&C2&”&entry.1665572164=“&B2&”&entry.282996263=Project+Name+Here&entry.213
4434756″

Notice where the formula has “Project+Name+Here”
Highlight this and replace it with “&&”
Put the cell containing the project name in between the ampersands.

=”https://docs.google.com/forms/d/1VEEZZwhuGCv_Ay2V14JOZj_ysnmlAtMSaV2aBxr
e-1c/viewform?
entry.1286231922&entry.1709658078&entry.189485916&entry.170201175=”&D2&”,+
“&C2&”&entry.1665572164=”&B2&”&entry.282996263=“&E2&”&entry.2134434756″

 

Step 8: Your formula might not be creating a hyperlink if any of your cells have spacebars in them.  On your Mac do a command F (control F on PC).

Click on the … button to go to Find and Replace


In the Find box hit the space bar.
In the Replace with box type in %20
Uncheck the “search all sheets” checkbox
Click on Replace all button

Your link should now be a live hyperlink.

Step 9: Optionally you can create another column and use the formula =hyperlink( to create a nicer looking hyperlink for students to click on.
Create another column header of “Click Here to Evaluate”
Type =hyperlink(
Type the cell that contains the hyperlink =hyperlink(F2
Type a comma and then in quotations type what you want the click here text to say.
=hyperlink(F2,”Click Here to evaluate ”
Use concatenation (&) to join the click here text with the students name.
=hyperlink(F2,”Click here to evaluate “&D2&” “&C2)
notice the &” “& which creates a space between the first and last name. 

Optionally you can hide all of the columns except the hyperlink so that classmates only see a list of “Click Here to evaluate …..”
You will want to fill down your formula so that it is applied to all of the students in the spreadsheet.