Questionnaires – Automatically send feedback to individuals

It’s common to use questionnaires at the end of a course to get feedback on the course and teacher. When you have a lot of teachers, it can be hard work analysing that feedback and splitting into the different teachers. Here we’re going to look at how we can automatically send the individual feedback to each teacher, as soon as it’s completed by the student. Plus, retain a master feedback sheet with all the teachers on it for the head of department’s use.

There are 4 documents we’re going to create:

• Questionnaire form
• Questionnaire master responses sheet
• Individual feedback sheet per teacher
• Questionnaire links sheet

Here’s how they link together:

Those who have been following my posts will see that this one combines various previous posts into one. Namely, the use of the QUERY and IMPORTRANGE functions, setting up forms, and creating short URLs.

---

Setting up the Form

Let’s start with the form. Create a new form and add the questions you want the student to answer. An example of one is below:

Then connect that form to a spreadsheet to store the responses. Click on “Responses”, then the green spreadsheet icon.

We want a new spreadsheet, so just click “Create”.

This populates the Sheet with our questions.

---

Setting up the master feedback sheet

We have a sheet called “Form Responses 1” where all the form responses will be stored, and now we need a sheet for each teacher, where we’re going to store the feedback of that particular teacher.

Add a new sheet and type the name of the first teacher in cell A1.

In cell A2 we’re going to add a QUERY function, which will filter the feedback on the Form Responses 1 sheet and will display only the feedback for Barrie Roberts. Here’s the formula. The QUERY function is explained in detail in my post on the QUERY function.

In summary, it looks at the Form Responses 1 sheet, gets all the responses, looks for ones where in column E it matches the name in cell A1 (i.e. Barrie Roberts), then sorts it by date (column A), with the most recent at the top. With no responses, you’ll get the error message “#N/A”, which will disappear as soon as you start receiving responses.

Here, I’ve named the sheet “Barrie”.

To just test it, I’ve filled out the form with a response for Barrie Roberts and on the sheet “Barrie” the feedback automatically appears. I normally use this as an opportunity to format the sheet a little, e.g. change the column widths, etc. You can always delete this test responses from the form responses 1 page.

Then duplicate this sheet for every other teacher you have. The only things you will need to change is the name in cell A1 and the sheet name.

---

Setting up the Individual feedback sheet

The spreadsheet above has everyone’s feedback on it and usually we won’t want to share that with all the teachers. So, let’s set up the teacher’s individual feedback sheet.

First, create a new spreadsheet. Then we’re going to copy one of the teacher’s sheets to the new file. We do this, so that the formatting is the exactly the same in the new sheet, saving us some time and ensuring they look the same. So, from the master spreadsheet, click on a teacher’s sheet and select “Copy to”.

Then, click on “Recent” and select the new spreadsheet you created. Here, I had already called it “Questionnaires – Barrie Roberts”.

The sheet will appear and by called “Copy of…”. Delete Sheet 1 as it’s not needed and rename the ‘Copy of’ sheet.

Now, we will need to delete the name and QUERY function from cells A1 and A2.

Then add an IMPORTRANGE function in cell A1. This will look at the master sheet, and import the information from the sheet and cells specified into the individual sheet. For more information on how the IMPORTRANGE function works, see my IMPORTRANGE post.

In the brackets and between quote marks, I add the URL of the master sheet, add the sheet called Barrie, and state the cell reference I want. Note, I’ve used an open-ended one, so that it will important all the data from the sheet, no matter how many rows there are.

We then need to authorize the connection between the 2 sheets. Just hover over cell A1 and click “Allow access”.

As we can see, it’s imported the same information from the master sheet and will update automatically as more form responses are received in the master sheet.

---

Setting up the Questionnaire links

The last thing to do is to set up the links to the questionnaire. To aid analysis afterwards and for the whole system to work automatically, it’s essential that the information is in a specific format.

The most important thing in this example, is that the teacher’s name is always the same, i.e. “Barrie Roberts”. As, if we left it up to the students to type in the teacher’s name, we could get all sorts of variations, like Mr Roberts, B Roberts, etc. Then the QUERY function wouldn’t work. In our academy we also want feedback on things like the rooms, so, we also make sure we control the format of those.

To do this, we just create a sheet where we add the information about each class, then use that to create a pre-filled link, which will be shared with the students. In this example, that pre-filled form will include the class name, level and the teacher’s name. The good thing is that you only need to set this sheet up once.

Going from left to right, we add columns for the class, level, and teacher. Then a column for the short link we’re going to create. A column for where the full link we be created. In column F we’ll copy and paste the pre-filled link from the form. Then in columns G to I, we will take the information in columns A to C and automatically replace any spaces with “%20”, which will make sure the URL we finally create doesn’t have any spaces in it, which can break the link.

So, in cell G2 we add this substitute function. This will replace any spaces in cell A2 with %20.

We do the same for column H and I, for the level and teacher.

So, adding the class, level, and teacher to our sheet, we can see that in columns G to I, its replacing the spaces with the %20 character.

Now we need our pre-filled link from the Form. Open the form and click on the 3 dot menu and then “Get pre-filled link”.

Then complete the fields you want to pre-fill with placeholders. Here, I’ve just used the same name as the questions, so CLASS, LEVEL, and TEACHER.

Click Submit and at the top of the page, a pre-filled link will appear. Click on that and press ctrl+C to copy it.

Then paste it in cell F2.

Finally, in cell E2, we need to replace the placeholders with the information on our sheet in that particular row. To do this, we use the SUBSTITUTE function. This is looking at the URL in F2 and replacing the word CLASS with the contents of cell G2 (the class name), replacing LEVEL with cell H2 (the level), and TEACHER with I2 (the teacher’s name).

In cell E2, we now have a link which we could share with the students, which will open the form and already have the class, level, and teacher pre-filled out.

---

Clicking on the link will open the questionnaire:

As you can see, it has the fields we wanted pre-filled out.

I wouldn’t suggest going through all these steps just for just one class, as the sheet is set up to create links for numerous classes. Just copy down the cells E to I to as many rows as you like and the program will create URLs automatically for all of them based on the information you enter in columns A to C. So, you’ll end up with something like this:

Here are the links to make copies of the files above:

End of course questionnaire spreadsheet (the form is linked so will copy automatically)

https://docs.google.com/spreadsheets/d/1F4TXCUFBz5GREd8z8ilxDaSZpU6WKyqhY9-RK1NUrmI/copy

Individual questionnaire feedback spreadsheet

https://docs.google.com/spreadsheets/d/1ZnZ8S2_9oK6NNOE3ETgp0URI5EjM2_0D8VX6K4BrtVE/copy

Questionnaire links spreadsheet

https://docs.google.com/spreadsheets/d/14bf_xG_cRfYOPj5Ku8ymLy-m3OZeXRYIZ-oZcHxhScQ/copy