Embedded Files
You've completed 2 big steps, the spreadsheet of data, and the form attached to that same spreadsheet.
Now you need unique link to each student's form. You can do it!
Under the ⋮ choose "Get pre-filled link."
A preview of the form opens for me to pre-fill:
I'm going to pre-fill with some place-holder text, in ALL CAPS. This is to prepare for the next step.
I set the form to collect the email address of the respondent--this is optional. I don't want to pre-fill that.
Once I've inserted the place-holder text for the questions I want to pre-fill-–leaving the others blank--I click "Get link" where the "Submit" button normally would be.
Copy the link.
Remember the spreadsheet you linked to your form? It has 2 sheets: Form responses and Sheet1. For demonstration purposes, I'm going to use a sheet called "Copy of Sheet1."
Go to Sheet1, where you data is. Paste the link in a new column in the first row that has data--in my sample, that's H2.
Now double-click that cell to enlarge the crazy jumble inside. Notice the words in ALL CAPS?
We're going to replace the ALL CAPS with the cell that has the data we want.
See FIRST? We want that to fill in with the correct first name. But before we can add the cell address where that information lives, we have a little code to enter.
Select FIRST and replace it with: "&&" That's quotation mark ampersand ampersand quotation mark. This will tell the system to go to a defined cell.
In my sample sheet, the first name is in B2. In between the two ampersands, enter B2.
As you've probably guessed, we're going to do that again for each of the ALL CAPS words.
Now the link looks like this:
Now the link looks like this:
Yeah, we should be finished now. But when I test the link, it brings me to a form that looks like this:
Instead of filling in the form with the contents of the cell I told it to go to, it filled it in with a quotation mark.
This step fixes it! In front of "https" enter:
=HYPERLINK("
Don't forget the quotation mark after that opening parenthesis mark.
And at the end of the whole string, add another quotation mark and close parenthesis. Yes, that means it has two quotation marks before the final parenthesis. Enter.
Now it looks like this:
And when I test it:
Now the sweet part! We're going to copy that ridiculous, long formula all the way down for each student.
Hover the mouse at the bottom right corner of the cell. The mouse changed to a plus sign.
Drag the plus sign down to the end of the list. Sheets will copy the formula. Even though your formula is written for row 2, Google sheets will adjust as it copies for row 3, 4, etc.
You'll need to make this link available to each person responding. For students, I put the spreadsheet in Google Classroom and let them click on their own link to the form--be sure that spreadsheet is set to "Students can view only." You could also embed it on a website, then share it with those you want to use the form, or even email the link to an individual.
More information about pre-filled links from my hero, Alice Keeler:
Report abuse