Personalized Google Forms

PRE-FILLING GOOGLE FORMS OUT

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

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

Forms PF - 1


SETTING UP THE GOOGLE SHEET

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

Forms Pre-Fill - 2

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

Forms Pre-Fill - 25 (1)

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

Forms PF - 2


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

Forms Pre-Fill - 6

5) This opens your form. 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. At the bottom of your form, click “Submit”.

Forms Pre-Fill - 7 Forms1 - 4

6) A special link will appear at the top of the page. Click on it and press Ctrl+C (Cmd+C) to copy it.

Forms Pre-Fill - 8

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

Forms PF - 3


ADDING THE INDIVIDUAL DATA TO YOUR FORM LINK

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

https://docs.google.com/forms/d/1cCk-eDEV81i2X9uua_pI15SGI4N5ZJq0HE-MIHHswq8/viewform?entry.1422654197=GROUP&entry.148162827=LEVEL&entry.2134513978=TEACHER&entry.349432024=CLASSROOM&entry.1681636254

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

Forms PF - 4

=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(H3, “GROUP”, J3)

Here we are getting the contents of cell H3 (the last part of the form link), and substituting the word GROUP with the contents of cell J3 (“Mondays”).

Forms Pre-Fill - 16


This formula follows the same principal, but the only difference is, 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 (not always necessary to do)

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.

Forms Pre-Fill - 25 (1)

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

Forms PF - 5   =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).

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

Forms Pre-Fill - 20  =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.


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

Forms Pre-Fill - 22   Forms Pre-Fill - 23

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.

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

Forms PF - 6

19) 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.

Forms PF - 7 Forms PF - 8

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

Forms Pre-Fill - 27     Forms Pre-Fill - 28


To summarize the columns created and why they are there.

Substitute 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 full pre-filled form link.

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


Once you’ve set your Sheet up, if you reuse the form, you’ll only need to change the data on the Sheet to change the details that come out on the Forms.


eBooks now available on Drive, Forms, Sheets, Docs, and Slides:

Baz Roberts (Flipboard / Twitter / Google+)


2 thoughts on “Personalized Google Forms

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s