Google Forms – Creating personalized forms automatically

Pre-filling Google forms

Why would you want to pre-fill parts of a Google Form?

• To ensure the information is in the format you want
• The data format is consistent
• Consistent data means it’s easier to analyze afterwards
• It’s quicker for the person to fill out your form
• It adds the personal touch, if you add their name on it
• One form can collect data from many sources, and be sorted with ease later on in the same spreadsheet

Automatically pre-filling Google Forms out

Fortunately, there’s a way to do it, which requires a little bit of Google Sheets knowledge but if you follow the steps below, it’s pretty easy to do.

---

Summary of the main steps

---

Setting up the Google Sheet

1) Create a Google Sheet by going to New and clicking Google Sheets.

2) Add the information you want to add to your form. E.g. Columns A to D below.

In this example, I’m going to create an end of course questionnaire and want to send a personalized form to each class using the same form, but with 4 fields already filled out (Group, Level, Teacher, Classroom).

So, later on the data will all arrive in the same place, but I can sort the data with ease into these areas. This allows me to look at the data not just for that particular class, but also more globally across all them.

3) Next to this, I need to add some columns, which will create the personalized link (e.g. columns E to I).

You can name however you want, but I’ve given them descriptive names so you can follow where the information is going. Don’t worry, what they do for now, I’ll explain later:

• Substitute link with class details
• Full pre-filled form link
• Group %20
• Level %20
• Teacher %20

---

Getting the pre-filled form link

4) Now we need the link to the form. Go back to the form and on the top-right click on the three dots next to the Send button. Click on “Get pre-filled link”.

5) This opens your form ready to pre-fill. Type in words into the fields you want to pre-fill. Normally, I give them the same names as what is on the Google Sheet, so it’s easier to remember which is which. Also, only type one word per field.

6) At the bottom of your form, click “Get link”.

7) A black bar will appear, then click COPY LINK to the clipboard.

8) Go back to your sheet and in column F, in F2, press Ctrl+V (Cmd+V) to paste it into the cell and press Enter.

---

Adding the individual data to your form link

In the pre-filled form link below, we need to replace the words, GROUP, LEVEL, TEACHER, and CLASSROOM with the data that’s in the spreadsheet.

9) In column E (Substitute pre-filled link with class details), type in the SUBSTITUTE formula below in cell E2.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(F2, “GROUP”, G2), “LEVEL”, H2), “TEACHER”, I2), “CLASSROOM”, D2)

---

Look scary? It’s not as complicated as it seems. Let’s just look at a simpler example:

=SUBSTITUTE(F2,”GROUP”,G2)

Here we are getting the contents of cell F2 (the form link), and substituting the word GROUP in that link with the contents of cell G2 (“Monday”).

You can see at the end of the link the word GROUP:

With the formula above it replaces GROUP with the word Monday from the cell:

---

This formula follows the same principal, but the only difference is that we are substituting 4 different things at the same time (Group, Level, Teacher, and Classroom).

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(F2, “GROUP”, G2), “LEVEL”, H2), “TEACHER”, I2), “CLASSROOM”, D2)

So here, it gets the pre-filled link in F2, replaces the word GROUP with the contents of G2, replaces LEVEL with the contents of H2, replaces TEACHER with the contents of I2, and replaces CLASSROOM with the contents of D2.

---

Dealing with spaces in your data

This is not always necessary to do but if your data has spaces in it like mine here, that’s going to cause you problems with the links, as spaces cause links to break, but there’s a way to get round this.

10) In column G (Group %20), type in the SUBSTITUTE formula below:

=SUBSTITUTE(A2,” “,”%20”)

Here you’re telling Sheets to look at cell A2 (where the Group info is), to find any spaces, and to replace any spaces found with %20 (which tells the computer to add a space without breaking the link).

11) Do the same for the level and teacher columns (changing the cell reference A2 to B2 for the level, and C2 for the teacher).

=SUBSTITUTE(B2,” “,”%20”)

=SUBSTITUTE(C2,” “,”%20”)

Note: In this example, I didn’t need to do this step for the CLASSROOM, as there are no spaces in that data, e.g. A01. So, it takes the Classroom name straight from column D.

---

12) Hover over cell E2 and the direct link will appear. To try it out, just click the blue link.

Hooray! You can see that all the fields are pre-filled out from the information on the Sheet.

---

Copy the cells from row 2 down your table

Now that’s done for one row, you can simply copy it down to the other rows, and it will pick up the information on each row automatically.

13) Select all the row from “full link” to the end “teacher %20”.

14) Grab the little blue square on the end of the select row and drag it down to copy the first row to the other rows.

Clicking on the links in column E like before, shows the form with each row’s data added.

---

Summary

To summarise the columns created and why they are there.

Substitute pre-filled link with class details

This substitutes the field names you add in the pre-filled form with the information from your sheet.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(F2, “GROUP”, G2), “LEVEL”, H2), “TEACHER”, I2), “CLASSROOM”, D2)

Full pre-filled form link

Where you paste the pre-filled form link, which includes GROUP, LEVEL, etc.

Group %20

This replaces any spaces in your text with “%20”, so that the URL doesn’t have any spaces in it.

=SUBSTITUTE(A2,” “,”%20”)

Level %20, Teacher %20

As per Group %20, just change the cell reference

---

It may seem a lot of work, but believe me it’s easier than the steps may seem and once it’s set up you can use it over and over again with different sets of data, or in this example, with different groups of students, by simply pasting in the new data.

---